But I Need To Compare Two Date Columns In Different Tables

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 talk.

Pinky Blue


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.

BIG DATA!

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.

Hm.

Switch Hitter


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);
Chock full of Jacques

Opternatives


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);
Mexican Ham Radio

But, you know. That might be overkill.

Depending.

Thanks for reading!



Leave a Reply

Your email address will not be published. Required fields are marked *