Otimizando a consulta do SQL Server ao unir tabelas na coluna DATETIME

computando

Se eu fosse você e tivesse que fazer isso repetidamente, provavelmente adicionaria colunas computadas que fornecem os tipos de dados desejados e as indexaria de acordo.

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

Obviamente, isso não oferece nenhum benefício real por si só até que as colunas sejam indexadas para suportar a junção.

O bom dessa abordagem é que adicionar as colunas computadas como não persistentes é uma operação rápida e sem bloqueio com quase zero de gravações no banco de dados. Você pode adiar isso para quando adicionar índices (que você precisa de qualquer maneira).

E por meio da parte de correspondência de expressão da otimização de consulta, você nem precisa alterar a consulta original para o SQL Server usar as novas colunas.

Você pode usar um intervalo de datas em uma ou outra tabela

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)))

Qualquer tabela que você escolher para usar as funções não pode usar índices, então escolha sabiamente. Teste os dois lados e examine o plano de execução.

No SQL Server 2022 e no Azure SQL, você pode usar DATETRUNC(day, b.dt).

Depende...

Se você deseja um loop aninhado com uma busca de índice correlacionada e possui um índice adequado em pelo menos uma das tabelas com colunas iniciais, a id,dtconversão em uma busca de intervalo será o ideal, como na resposta de Charlie .

Se você deseja uma junção de hash (talvez não tenha índices úteis), então

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

ou

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

Pelo menos fornece um predicado de igualdade em ambas as condições, o que significa que ambas podem ser usadas na chave de hash.

DATETRUNCé mais capaz de tirar proveito do fato de que um índice ordenado por datetimetambém é ordenado pordate e também permite uma junção de mesclagem com um predicado id, DATETRUNC(day, dt)sem nenhum operador de classificação se ambas as tabelas tiverem um índice ativado id, dt- embora isso seja um "muitos para muitos " digite com uma mesa de trabalho.

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)

A convert(date, a.dt)opção também ofereceria suporte a uma pesquisa indexada com loops aninhados e uma igualdade ide busca de intervalo dt, embora não tão eficiente quanto construir o intervalo por conta própria, pois a busca dinâmica lê um dia adicional.

  |--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)

A lógica de busca dinâmica não se estende a DATETRUNCisso, então o melhor que se pode fazer com loops aninhados é uma igualdade na idparte e residual na data.

   |--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)

Dependendo de quanto as tabelas subjacentes são gravadas versus com que frequência elas são lidas com essa consulta, outra opção que você pode considerar é uma exibição indexada .

Uma exibição indexada persistirá os resultados da consulta como se fosse uma tabela em si. Essa compensação de uso de espaço em disco adicional e sobrecarga de gravação beneficia o desempenho de leitura aprimorado - essencialmente como se a consulta estivesse lendo diretamente de uma tabela.

Geralmente, existem algumas limitações de exibições indexadas , mas sua consulta parece se adequar aos critérios que a tornam utilizável para ela. Você pode criar um assim:

Primeiro ceite a visão com a SCHEMABINDINGopção (listando explicitamente as colunas e chamando seus nomes de esquema, conforme os requisitos):

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);

Em seguida, crie um índice clusterizado exclusivo na exibição recém-criada para convertê-lo em uma exibição indexada que persiste no disco:

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

Por fim, selecione na exibição indexada recém-criada com a NOEXPANDdica para garantir que ela use a cópia persistente dos dados:

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

As exibições indexadas também são úteis se você não tiver controle para modificar os índices das próprias tabelas subjacentes.

A solução mais rápida seria adicionar uma coluna de data extra a ambas as tabelas e garantir que ela seja preenchida por qualquer programa que esteja gravando nas tabelas. Atualize os dados existentes com:

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

Então a consulta existente seria:

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