这个简单的SQL查询可以优化吗?

因为您有两组数据,按相同的值排序。您尝试过合并联接而不是嵌套循环联接吗?

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT COUNT(*)  
FROM Address adr INNER JOIN  
     Auditable a on adr.UniqueId = a.UniqueId 
OPTION (LOOP JOIN)

SELECT COUNT(*)  
FROM Address adr INNER JOIN  
     Auditable a on adr.UniqueId = a.UniqueId 
OPTION (MERGE JOIN)

SELECT COUNT(*)  
FROM Address adr INNER JOIN  
     Auditable a on adr.UniqueId = a.UniqueId 
OPTION (HASH JOIN)

编辑:

这些解释是概念性的。SQL Server可能正在执行比我的示例所显示的更复杂的操作。这种概念上的理解,与通过设置统计命令测量时间和逻辑IO以及检查查询执行计划相匹配,构成了我的查询优化技术(四年以上)的基础。愿它能为你服务,也能为我服务。

安装程序

• 得到5副牌。
• 取1个数据组并生成父数据集。
• 取其他4个数据组,生成子数据集。
• 按卡值订购每个数据集。
• 设m为父数据集中的卡数。
• n是子数据集中的卡数。

嵌套环

• 从父数据集的顶部取出一张卡。
• 在子数据集中搜索(使用二进制搜索)以查找第一个匹配项。
• 从第一个匹配处向前搜索子数据集,直到找到不匹配处。你现在找到了所有的火柴。
• 对父数据集中的每个卡重复此操作。

嵌套循环算法迭代父数据集,然后为每个父数据集搜索子数据集一次,使其开销为:m*log(n)

合并

• 从父数据集的顶部取出一张卡。
• 从子数据集的顶部取出一张卡。
• 如果牌匹配,从每个牌组的顶部拉出牌,直到找到不匹配的牌。在父匹配项和子匹配项之间生成每个匹配项对。
• 如果卡不匹配,则查找父卡和子卡之间的较小值,并从该数据集的顶部取出一张卡。

合并联接算法将父数据集迭代一次,子数据集迭代一次,开销为:m+n。它依赖于要排序的数据。如果您请求对未排序的数据进行合并联接,则会引发一个排序操作!这会将成本增加到(m*log(m))+(n*log(n))+m+n。在某些情况下,甚至可能比嵌套循环更好。

搞砸

• 找张卡片桌。
• 从父数据集中取出每张卡,并将其放在卡表中可以找到的位置(不必与卡的值有任何关系,只需方便即可)。
• 从子数据集中取出每张卡,将其匹配的父卡放在纸板表上,生成匹配对。

哈希连接算法对父数据集和子数据集进行一次迭代,使其开销为:m+n。它依赖一个足够大的卡表来保存父数据集的全部内容。

如果您经常运行这个查询,并且它需要非常快,那么就为它创建一个物化的索引视图。在插入/更新/删除时会有一些开销,但这个查询将是即时的。聚合可以预先计算并存储在索引中,以便在查询执行期间最小化昂贵的计算。

Improving Performance with SQL Server 2005 Indexed Views

真正的问题是嵌套循环联接。对于地址表中的140万行,您要对可审核表进行索引查找。也就是说,根块、分支块和叶块总共读取4.2米的块。整个索引大概只有5千块左右…它应该进行哈希联接,这样它只读取两个索引一次,并通过它们进行哈希。

如果你认为这些桌子很大,我猜这是在一个没有很多内存的小盒子上。您需要确保分配了足够的内存,以便将整个索引放入内存中,以使哈希连接有效。

auditable.uniqueid是否是对address.uniqueid的外键引用,这意味着auditable中没有地址中不存在的值?

如果是这样,这可能有效,而且可能更快:

SELECT COUNT(DISTINCT Auditable.UniqueID)
FROM Auditable

注意:这还假定uniqueid在地址表中是唯一的(/primary key),但在可审核表中不是唯一的。

与内部联接相比,该子句的运行成本更低。

select COUNT(adr.UniqueId)
    from Addresses adr
    where EXISTS (
        select 1
            from Auditables aud
            where aud.UniqueId = adr.UniqueId
    )

这个适合你的需要吗?

N.B.guid对于数据库引擎来说非常昂贵。

不确定是否会更快,但您可以尝试以下操作

SELECT COUNT(adr.UniqueID) FROM Address adr INNER JOIN Auditable a on adr.UniqueId = a.UniqueId

它应该给您相同的计数,因为unqieid永远不会为空。

我想说的是,在外键上缺少索引。

• 140万和400万不是大桌子,而是小桌子。请在浏览5亿条条目时说“大”。

• 为了得到一个真正的答案,我们需要执行计划/查询计划,这样我们就可以看到发生了什么。

• 如果知道你的世界里“长”是什么,那就太好了(考虑到你认为400万行是很多行)。这个问题不会在一秒钟内回答-那么你期待什么,会发生什么?

• 不过,我敢打赌,你的索引不见了。除此之外,我将开始指出硬件(因为我也看到了这一点,这也是导致糟糕性能的原因)。

对于这些大型表,您可能希望对数据进行分区以提高查询性能。此外,如果还没有,请尝试运行优化顾问,查看是否还有其他索引可能对您有利。此外,您最近是否重新组织了集群索引——这是维护包的一部分吗?很多时候,这也会大大提高你的表现。