代码之家 › 专栏 › 技术社区 › Jose

SQL查询优化

optimization performance sql-server

Jose · 技术社区 · 15 年前

我有一个查询要尽可能快地执行。

这里是:

select d.InvoiceDetailId,a.Fee,a.FeeTax
from InvoiceDetail d
LEFT JOIN InvoiceDetail a on a.AdjustDetailId = d.InvoiceDetailId

我在adjustdetailid列上放置了一个升序索引

然后,我运行带有“显示实际执行计划”的查询,结果估计子树成本(不在最顶端的选择节点)为2.07。

然后我想,也许我可以做点什么来改进这一点,所以我向左侧联接添加了一个条件,如下所示:

select d.InvoiceDetailId,a.Fee,a.FeeTax
from InvoiceDetail d
LEFT JOIN InvoiceDetail a on a.AdjustDetailId is not null 
and a.AdjustDetailId = d.InvoiceDetailId

我重新跑了一次,得到了0.98的子树成本。所以我想,太好了,我跑得快了两倍。然后我单击了显示客户机统计信息,然后对这两个查询都单击了执行4-5次,不管你信不信,第一个查询的平均速度会更快。我不明白。查询返回120K行。

有什么见解吗?

也许我会因为缓存而被污染结果,但我不知道是否是这样,也不知道如何重置缓存。

编辑: 好的,我在谷歌上搜索了如何清除查询缓存,因此在查询之前添加了以下内容:

DBCC DROPCLEANBUFFERS  
DBCC FREEPROCCACHE

然后,我运行了5次每个查询,第一个查询速度仍然稍快(13%)。第一次查询:客户端处理时间:239.4 第二次查询:客户端处理时间:290

所以我想问题是,你为什么这么认为?当表的大小增加四倍时,第二个查询会更快吗?或者左联接导致查询命中索引两次,因此查询速度总是较慢。

请不要解雇我,我只是想受教育。

编辑第2页: 我需要得到所有的发票明细,而不仅仅是调整后的明细,因此是左联接。

编辑第3页: 我试图用查询解决的真正问题是总结所有的invoiceDetail行,但同时也调整它们。因此,最终似乎要执行的最佳查询如下。我认为只有做一个连接,然后添加被连接的表是唯一的方法,但似乎通过条件分组最优雅地解决了这个问题。

SELECT CASE WHEN AdjustDetailId IS NULL THEN InvoiceDetailId ELSE AdjustDetailId END AS InvoiceDetailId
  ,SUM(Fee + FeeTax) AS Fee
FROM dbo.InvoiceDetail d
GROUP BY CASE WHEN AdjustDetailId IS NULL THEN InvoiceDetailId ELSE AdjustDetailId END

示例:具有以下行发票金额费用费用调整金额

1 300 0空

2(100)×0(1)

3(50)×0(1)

4,250,0,零

我的愿望是: 发票费用 1 150

4×250

谢谢大家的意见。

4 回复 | 直到 14 年前

Charles Bretana 14 年前

我可能已经猜到它们是相同的(有相同的执行计划),因为像a.AdjustDetailid=d.InvoiceDetailid这样的谓词不可能是真的,如果一侧为空…因此,添加is not null条件是多余的。但也许处理器正在用其中的附加谓词执行其他不必要的步骤…

但另一个答案提到的更重要。是否确实需要输出没有匹配记录的所有行(没有调整发票的发票)??如果不改为内部连接,会加速很多。

但是,如果你真的需要他们,你可以尝试联合。

  Select d.InvoiceDetailId,a.Fee,a.FeeTax
  From InvoiceDetail d
     Join InvoiceDetail a 
         On a.AdjustDetailId = d.InvoiceDetailId
  Union
  Select InvoiceDetailId, null, null
  from InvoiceDetail 
  Where AdjustDetailId Is Null

它在不使用外部联接的情况下做同样的事情… (有一个联合的两个查询是否比单个外部联接查询运行得更快,这是个问题…)

marc_s HarisH Sharma 15 年前

如果你想让这个查询非常快,你需要

将左联接转换为内部联接

确保对invoiceDetail.AdjustDetail和invoiceDetail.invoiceDetail进行索引

SELECT 
  d.InvoiceDetailId, a.Fee, a.FeeTax
FROM 
  dbo.InvoiceDetail d
INNER JOIN 
  dbo.InvoiceDetail a ON a.AdjustDetailId = d.InvoiceDetailId

接下来,您需要确保您的统计数据是最新的,以便基于成本的查询优化器能够正常工作。

要更新统计信息,请使用 UPDATE STATISTICS (table) 命令-参见 MSDN docs on UPDATE STATISTICS here

Brad 15 年前

这个查询中只有一个表,对吗?

如果你使用

选择InvoiceDetail、Fee、Feetax 来自发票详细信息

这将得到所有的行,而不仅仅是调整后的行。

假设您正在进行自连接,并且出于一个好的原因,我将索引invoiceDetailid和adjustDetailid,并查看执行计划使用的索引。

您还可以尝试在索引中“包括”fee和feetax列-如果表真的很宽,这将有很大帮助。

erikkallen 15 年前

对于您的疑问,我可以考虑3种不同的合理执行计划:

LOOP JOIN OUTER [a.AdjustDetailId = d.InvoiceDetailId]
    TABLE SCAN InvoiceDetail d
    TABLE SCAN InvoiceDetail a

HASH JOIN OUTER [a.AdjustDetailId = d.InvoiceDetailId]
    TABLE SCAN InvoiceDetail d
    TABLE SCAN InvoiceDetail a

LOOP JOIN OUTER
    HASH JOIN OUTER [x.AdjustDetailId = d.InvoiceDetailId] AS y
        TABLE SCAN InvoiceDetail d
        INDEX SEEK [InvoiceDetail, AdjustDetailId IS NOT NULL] x
    InvoiceDetail a [a.AdjustDetailId = y.AdjustDetailId]

也许添加非空条件会使优化器选择另一个计划,这很难说。