A Certain Ratio
I have a lot to say about this demo on SQL Server 2019, which I’ll get to in a future post.
For now, I’m going to concentrate on ways to make this situation suck a little less for you wherever you are.
Let’s get a couple indexes going:
CREATE INDEX something ON dbo.Posts(PostTypeId, Id, CreationDate); CREATE INDEX stuffy ON dbo.Comments(Score, PostId, CreationDate);
And look at a maybe kinda sorta stupid query.
SELECT COUNT(*) FROM dbo.Posts AS p JOIN dbo.Comments AS c ON p.Id = c.PostId WHERE DATEDIFF(YEAR, p.CreationDate, c.CreationDate) > 1 AND p.PostTypeId = 1 AND c.Score > 0;
We wanna find questions where a comment was left a year after they were posted, and the comment was upvoted.
What We Know
From yesterday’s post, we know that even if we put our date columns first in the index, we wouldn’t have anything to seek to.
Unlike yesterday’s post, these columns are in two different tables. We can’t make a good computed column to calculate that.
The indexes that I created help us focus on the SARGable parts of the where clause and the join conditions.
That query takes about 2 seconds.
You might be tempted to try something like this, but it won’t turn out much better unless you change your indexes.
SELECT DATEDIFF(YEAR, p.CreationDate, c.CreationDate) AS Diffo INTO #t FROM dbo.Posts AS p JOIN dbo.Comments AS c ON p.Id = c.PostId WHERE p.CreationDate < DATEADD(YEAR, -1, c.CreationDate) AND c.CreationDate > DATEADD(YEAR, 1, p.CreationDate) AND p.PostTypeId = 1 AND c.Score > 0 SELECT COUNT(*) FROM #t AS t WHERE t.Diffo > 1 DROP TABLE #t;
Moving CreationDate to the second column helps the first query quite a bit.
CREATE INDEX something_alt ON dbo.Posts(PostTypeId, CreationDate, Id); CREATE INDEX stuffy_alt ON dbo.Comments(Score, CreationDate, PostId);
You could try an indexed view here, too.
CREATE VIEW dbo.Sneaky WITH SCHEMABINDING AS SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p JOIN dbo.Comments AS c ON p.Id = c.PostId WHERE DATEDIFF(YEAR, p.CreationDate, c.CreationDate) > 1 AND p.PostTypeId = 1 AND c.Score > 0 GO CREATE UNIQUE CLUSTERED INDEX whatever ON Sneaky(records);
But, you know. That might be overkill.
Thanks for reading!