BrentOzar20240222PX

BrentOzar20240222PX

/*Drop and create indexes*/
EXEC DropIndexes;
GO
CREATE INDEX CreationDate ON dbo.Users (CreationDate);
CREATE INDEX Reputation ON dbo.Users (Reputation);

/*Define end date for last 90 days*/
SELECT TOP (1)
       CreationDate
FROM dbo.Users
ORDER BY Id DESC;

/*
2018-06-03 05:18:17.217
*/

SET STATISTICS IO, TIME ON;

/*Version 1: 1.917.455 reads. Of course, the most easy one does not perform, what's new :)*/
SELECT TOP (1000)
       u.Id
FROM dbo.Users AS u
WHERE u.Reputation > 50
      AND u.CreationDate >= DATEADD(DAY, -90, '2018-06-03 05:18:17.217')
ORDER BY u.Reputation DESC;

/*
How does your query perform? Compare the number of logical reads your query does, versus what it’d take to scan the whole table once.
Well, the full table has about 141.573 reads, so we are a little bit above it, I doubt Brent will approve that one..
*/


/*Let's split the logic in 2, this gives me 10.928 reads*/
WITH CTE
AS (SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Reputation > 50
    INTERSECT
    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.CreationDate >= DATEADD(DAY, -90, '2018-06-03 05:18:17.217'))
SELECT TOP (1000)
       u.*
FROM CTE           AS c
    JOIN dbo.Users AS u ON u.Id = c.Id
ORDER BY u.Reputation DESC;