BrentOzar.com: Improving Cardinality Estimation

Improving Cardinality Estimation.sql

DROP INDEX IF EXISTS Location ON dbo.Users; 
GO

CREATE INDEX Location ON dbo.Users(Location);
GO

CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation
AS
SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate
FROM dbo.Users u
WHERE u.Location = (SELECT TOP 1 Location
	FROM dbo.Users
	WHERE Location != N''
	GROUP BY Location
	ORDER BY COUNT(*) DESC)
ORDER BY u.Reputation DESC;
GO

SET STATISTICS IO ON;
 
EXEC GetTopUsersInTopLocation;
--Worktable = 0
--Users = 49496

SET STATISTICS IO OFF;
GO

CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation
AS
DECLARE @TopLocation nvarchar(100);

SELECT TOP 1 @TopLocation = Location
FROM dbo.Users
WHERE Location != N''
GROUP BY Location
ORDER BY COUNT(*) DESC

SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate
FROM dbo.Users u
WHERE u.Location = @TopLocation
ORDER BY u.Reputation DESC;
GO

SET STATISTICS IO ON;
 
EXEC GetTopUsersInTopLocation;
--Worktable = 0
--Users = 46466 (3030 less) 6% Profit
--Bangs: ExcessiveGrant and TempDb spill
--Major overestimation

SET STATISTICS IO OFF;
GO

CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation
AS
CREATE TABLE #UsersInTopLocation
(
    Id int PRIMARY KEY CLUSTERED
);

INSERT INTO #UsersInTopLocation (Id)
SELECT Id
FROM dbo.Users
WHERE Location =
(
    SELECT TOP 1 Location
	FROM dbo.Users
	WHERE Location != N''
	GROUP BY Location
	ORDER BY COUNT(*) DESC
);

SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate
FROM dbo.Users AS u
INNER JOIN #UsersInTopLocation AS uitl ON u.Id = uitl.Id
ORDER BY u.Reputation DESC;
GO

SET STATISTICS IO ON;
 
EXEC GetTopUsersInTopLocation;
--Worktable = 0
--#UsersInTopLocation = 31063
--Users = 3080

--Worktable = 0
--#UsersInTopLocation = 27
--Users = 44529

--Total reads: 31063 + 3080 + 44529 + 27: 78699

SET STATISTICS IO OFF;
GO

CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation
AS
SELECT TOP 1 Location
INTO #TopLocation
FROM dbo.Users
WHERE Location != N''
GROUP BY Location
ORDER BY COUNT(*) DESC

DROP TABLE IF EXISTS #UsersInTopLocation;
CREATE TABLE #UsersInTopLocation
(
    Id int PRIMARY KEY CLUSTERED
);

INSERT INTO #UsersInTopLocation(Id)
SELECT Id
FROM dbo.Users AS u
WHERE EXISTS
(
    SELECT NULL
    FROM #TopLocation AS tl
    WHERE u.Location = tl.Location
);

SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate
FROM dbo.Users AS u
WHERE EXISTS
(
    SELECT NULL
    FROM #UsersInTopLocation AS uitl
    WHERE u.Id = uitl.Id
)
ORDER BY u.Reputation DESC;
GO

SET STATISTICS IO ON;
 
EXEC GetTopUsersInTopLocation;
--Users = 3030

--Users = 50

--#UsersInTopLocation = 25
--Users 44529
--Total reads: SELECT 3030 + 50 + 25 + 44529: 47641 (1872 reads less, 3% profit)

--100% correct estimations.

SET STATISTICS IO OFF;
GO