Optimizing SQL Server Query When Joining Tables on DATETIME column

Question

I am working with SQL Server and I have two tables, table1 and table2. Both tables have a DATETIME column denoted as dt. I need to join these tables based not only on certain conditions but also to ensure the matching rows have the same date (time is not considered here) from the dt column.

Here's the query I'm currently using:

select *
From table1 a inner join table2 b
    on a.id = b.a_id
    and convert(date, a.dt) = convert(date, b.dt)

This query works to get the results I need, but I'm concerned about its performance, especially as the tables grow in size.

I'm wondering if there are more performant ways to achieve the same result? Are there techniques or SQL Server features that could help me optimize this query, especially the date comparison part?

Any advice or suggestions would be greatly appreciated. Thank you in advance.

score 7 · Accepted Answer · answered Aug 08 '23 at 13:45

computin'

If I were you and I had to do this repeatedly, I'd probably add computed columns that give you the data types you want, and index them accordingly.

ALTER TABLE table1 ADD dt_c AS CONVERT(date, dt);
ALTER TABLE table2 ADD dt_c AS CONVERT(date, dt);

Of course, this doesn't give you any real benefit on its own until the columns are indexed to support the join.

The nice thing about this approach is that adding the computed columns as non-persisted is a quick, non-blocking operation with near-zero writes to the database. You can defer that to when you add indexes (which you need anyway).

And via the expression matching portion of query optimization, you shouldn't even need to change the original query for SQL Server to use the new columns.

score 4 · Answer 2 · answered Aug 07 '23 at 16:54

You can use a date range on one or the other table

select *
From table1 a
inner join table2 b
    on a.id = b.a_id
    and a.dt >= convert(datetime, convert(date, b.dt))
    and a.dt < dateadd(day, 1, convert(datetime, convert(date, b.dt)))

Whichever table you choose to use the functions on cannot use indexes, so pick wisely. Test both ways around and examine the execution plan.

In SQL Server 2022 and Azure SQl, you can use DATETRUNC(day, b.dt).

Martin Smith · Answer 3 · 2023-08-07T17:49:11.643

It depends...

If you want a nested loops with a correlated index seek and you have a suitable index on at least one of the tables with leading columns id,dt then converting into a range seek will be the ideal as in Charlie's answer.

If you want a hash join (perhaps you have no useful indexes) then

a.id = b.a_id  and convert(date, a.dt) = convert(date, b.dt)

or

a.id = b.a_id and DATETRUNC(day, b.dt) = DATETRUNC(day, a.dt)

Does at least give you an equality predicate on both conditions meaning that both can be used in the hash key.

DATETRUNC is more able to take advantage of the fact that an index ordered by datetime is also ordered by date and would also allow a merge join with a predicate on id, DATETRUNC(day, dt) without any sort operators if both tables have an index on id, dt - though this will be a "many to many" type with a worktable.

StmtText
  |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([a].[id], [Expr1003])=([b].[a_id], [Expr1002]), RESIDUAL:([dbo].[table1].[id] as [a].[id]=[dbo].[table2].[a_id] as [b].[a_id] AND [Expr1002]=[Expr1003]))
       |--Compute Scalar(DEFINE:([Expr1003]=datetrunc(day,[dbo].[table1].[dt] as [a].[dt])))
       |    |--Clustered Index Scan(OBJECT:([dbo].[table1].[PK__table1__D132DEDA9CF69990] AS [a]), ORDERED FORWARD)
       |--Compute Scalar(DEFINE:([Expr1002]=datetrunc(day,[dbo].[table2].[dt] as [b].[dt])))
            |--Clustered Index Scan(OBJECT:([dbo].[table2].[PK__table2__B54BCC7FCA4E9E10] AS [b]), ORDERED FORWARD)

The convert(date, a.dt) option would also support an indexed lookup with nested loops and an equality on id and range seek on dt though not as efficiently as constructing the range yourself would as the dynamic seek reads an additional day.

  |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[id], [Expr1002]))
       |--Compute Scalar(DEFINE:([Expr1002]=CONVERT(date,[dbo].[table1].[dt] as [a].[dt],0)))
       |    |--Clustered Index Scan(OBJECT:([dbo].[table1].[PK__table1__D132DEDA9CF69990] AS [a]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
            |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([Expr1002],[Expr1002],(62))))
            |    |--Constant Scan
            |--Clustered Index Seek(OBJECT:([dbo].[table2].[PK__table2__B54BCC7FCA4E9E10] AS [b]), SEEK:([b].[a_id]=[dbo].[table1].[id] as [a].[id] AND [b].[dt] > [Expr1005] AND [b].[dt] < [Expr1006]),  WHERE:([Expr1002]=CONVERT(date,[dbo].[table2].[dt] as [b].[dt],0)) ORDERED FORWARD)

The dynamic seek logic does not extend to DATETRUNC so the best that one can do with a nested loops is an equality on the id part and residual on the date.

   |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[id], [Expr1003]))
       |--Compute Scalar(DEFINE:([Expr1003]=datetrunc(day,[dbo].[table1].[dt] as [a].[dt])))
       |    |--Clustered Index Scan(OBJECT:([dbo].[table1].[PK__table1__D132DEDA9CF69990] AS [a]))
       |--Clustered Index Seek(OBJECT:([dbo].[table2].[PK__table2__B54BCC7FCA4E9E10] AS [b]), SEEK:([b].[a_id]=[dbo].[table1].[id] as [a].[id]),  WHERE:(datetrunc(day,[dbo].[table2].[dt] as [b].[dt])=[Expr1003]) ORDERED FORWARD)

score 3 · Answer 4 · answered Aug 07 '23 at 19:51

Depending on how heavily the underlying tables are written to vs how often they're read from with this query, another option you may consider is an Indexed View.

An Indexed View will persist the results of the query as if it was a table itself. This tradeoff of additional disk space usage and write overhead is at the benefit of improved read performance - essentially as if the query was reading directly from a table.

Generally there are some limitations of Indexed Views but your query appears to fit the criteria that makes one usable for it. You can create one like so:

First ceate the view with the SCHEMABINDING option (explicitly listing out the columns and calling out their schema names, as per the requirements):

CREATE VIEW dbo.SomeIndexedView
WITH SCHEMABINDING
AS
select a.id, a.dt, b.Column1, b.Column2 -- etc
From dbo.table1 a inner join dbo.table2 b
    on a.id = b.a_id
    and convert(date, a.dt) = convert(date, b.dt);

Then create a unique clustered index on the newly created view to convert it into an Indexed View that is persisted on disk:

CREATE UNIQUE CLUSTERED INDEX IX_SomeIndexedView_Key ON dbo.SomeIndexedView (id, dt);

Finally, select from the newly created Indexed View with the NOEXPAND hint to ensure it uses the persisted copy of the data:

SELECT * -- In general, you shouldn't really use SELECT *, instead explicitly list out your columns
FROM dbo.SomeIndexedView WITH (NOEXPAND);

Indexed Views are also useful if you don't have control to modify the indexes of the underlying tables themselves.

score -3 · Answer 5 · answered Aug 08 '23 at 11:49

The fastest solution would be to add an extra date column to both tables and make sure it's populated by whatever program is writing to the tables. Update the existing data with:

update table1 
set [date] = convert(date, a.dt)

Then the existing query would be:

select *
From table1 a inner join table2 b
    on a.id = b.a_id
    and a.[date] = b.[date]

Optimizing SQL Server Query When Joining Tables on DATETIME column

5 Answers5

computin'