主页 / dba / 问题 / 330027
Accepted
Fajela Tajkiya
Asked: 2023-08-08 00:50:19 +0800 CST

在 DATETIME 列上连接表时优化 SQL Server 查询

  • 772

我正在使用 SQL Server，我有两个表，table1并且table2. 两个表都有一个DATETIME表示为 的列dt。我不仅需要根据某些条件连接这些表，还要确保匹配的行具有列中相同的日期（此处不考虑时间）dt。

这是我当前使用的查询：

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

该查询可以获取我需要的结果，但我担心它的性能，尤其是当表大小增加时。

我想知道是否有更高效的方法可以达到相同的结果？是否有技术或 SQL Server 功能可以帮助我优化此查询，尤其是日期比较部分？

任何意见或建议将不胜感激。先感谢您。

sql-server

5 个回答

  1. Best Answer

    计算

    如果我是您并且必须重复执行此操作，我可能会添加计算列来为您提供所需的数据类型，并相应地为它们建立索引。

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

    当然，在对列进行索引以支持联接之前，这本身并不会给您带来任何真正的好处。

    这种方法的优点在于，将计算列添加为非持久化是一种快速、非阻塞的操作，对数据库的写入几乎为零。您可以将其推迟到添加索引时（无论如何您都需要）。

    通过查询优化的表达式匹配部分，您甚至不需要更改 SQL Server 的原始查询即可使用新列。

    • 7

  2. 您可以在一个或另一个表上使用日期范围

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

    无论您选择在哪个表上使​​用这些函数，都不能使用索引，因此请明智地选择。测试两种方法并检查执行计划。

    在 SQL Server 2022 和 Azure SQl 中，您可以使用DATETRUNC(day, b.dt).

    • 4

  3. 这取决于...

    如果您想要一个具有相关索引查找的嵌套循环，并且您至少在一个具有前导列的表上有一个合适的索引，id,dt那么转换为范围查找将是理想的选择，如查理的答案所示。

    如果你想要一个散列连接（也许你没有有用的索引）那么

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

    或者

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

    至少为您提供了两个条件的相等谓词，这意味着两者都可以在哈希键中使用。

    DATETRUNC更能够利用这样一个事实，即索引排序依据datetime也是排序依据dateid, DATETRUNC(day, dt)，并且如果两个表都有索引，还允许在没有任何排序运算符的情况下使用谓词进行合并连接id, dt- 尽管这将是“多对多” “用工作台打字。

    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)

    该convert(date, a.dt)选项还支持使用嵌套循环以及等式id和范围查找的索引查找dt，尽管不如自己构建范围那么有效，因为动态查找会额外读取一天。

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

    动态查找逻辑无法扩展，DATETRUNC因此嵌套循环所能做的最好的事情就是id零件上的相等和日期上的残差。

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

  4. 根据基础表的写入量与使用此查询读取它们的频率，您可以考虑的另一个选项是索引视图。

    索引视图将保留查询结果，就像它本身是一个表一样。这种额外磁盘空间使用和写入开销的权衡有利于提高读取性能 - 本质上就像查询直接从表中读取一样。

    一般来说，索引视图有一些限制，但您的查询似乎符合使其可用的标准。您可以像这样创建一个：

    首先使用选项创建视图SCHEMABINDING（根据要求显式列出列并调用其架构名称）：

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

    然后在新创建的视图上创建唯一的聚集索引，将其转换为持久保存在磁盘上的索引视图：

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

    最后，使用提示从新创建的索引视图中进行选择NOEXPAND，以确保它使用数据的持久副本：

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

    如果您无法控制修改基础表本身的索引，则索引视图也很有用。

    • 3

  5. 最快的解决方案是向两个表添加一个额外的日期列，并确保它由写入表的任何程序填充。更新现有数据：

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

    那么现有的查询将是：

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

相关问题