作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我遇到了一个查询,它在查询的 HAVING 部分中有一个条件,我通常会将其放置在 WHERE 部分中。我之前将条件移动到 WHERE 部分,令我惊讶的是代码使用 WHERE 而不是 HAVING 的运行时间延长了大约 200%+。
这对我来说很奇怪,我还没有能够在网上找到任何描述这一点的东西。
以下是查询布局方式的示例:
此查询始终在 50 到 55 秒内运行。
SELECT TB1.COL1
,TB1.COL2
,TB2.COL3
,TB3.COL4
,TB1.SOME_ID
FROM TABLE1 TB1
JOIN TABLE2 TB2
JOIN TABLE3 TB3
ON TB1.SOME_ID = TB2.SOME_ID
ON TB1.SOME_ID = TB3.SOME_ID
GROUP BY TB1.COL1, TB1.COL2, TB2.COL3, TB3.COL4, TB1.SOME_ID
HAVING TB1.SOME_ID = 9999999
SELECT TB1.COL1
,TB1.COL2
,TB2.COL3
,TB3.COL4
,TB1.SOME_ID
FROM TABLE1 TB1
JOIN TABLE2 TB2
JOIN TABLE3 TB3
ON TB1.SOME_ID = TB2.SOME_ID
ON TB1.SOME_ID = TB3.SOME_ID
WHERE TB1.SOME_ID = 9999999
GROUP BY TB1.COL1, TB1.COL2, TB2.COL3, TB3.COL4, TB1.SOME_ID
SELECT TB1.COL1
,TABLE302.COL2
,TABLE314.COL3
,TABLE314.COL4
,TABLE312.COL5
,TABLE314.COL6
,TABLE302.COL7
,TABLE320.COL8
,TABLE320.COL9
,TABLE302.COL10
,TABLE302.COL11
,TABLE230.COL12
,TABLE100.COL13
,TABLE100.COL14
,TABLE104.COL15
,TABLE110.COL16
,TABLE230.COL17
,TABLE230.COL18
,TB1.COL19
,IIf([TABLE230.CD]>' '
And format(cast(TABLE230.DT as date),'yyyy-MM-dd') = format(cast('12/31/9999' as date),'yyyy-MM-dd'), TABLE230.AMT,TB1.O) AS [FA]
,TABLE230.COL20
,TABLE230.COL21
,format(cast(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) as date),'yyyy-MM-dd') AS [TODAY]
,TABLE104.COL22
,TABLE320.COL23
,TABLE180.COL24
,Count(TABLE100.COL14) AS [COUNT_COL14]
,IIf(format(cast(TABLE230.FDT as date),'yyyy-MM-dd') = format(cast('1/1/1901' as date),'yyyy-MM-dd')
And format(cast(TABLE230.DDT as date),'yyyy-MM-dd') = format(cast('1/1/1901' as date),'yyyy-MM-dd'),'NOT ACTIVE','ACTIVE') AS [ST]
,TABLE230.COL25
FROM TABLE1 TB1
RIGHT JOIN TABLE302
INNER JOIN TABLE114
INNER JOIN TABLE130
INNER JOIN TABLE110
INNER JOIN TABLE126
INNER JOIN TABLE104
INNER JOIN TABLE124
INNER JOIN TABLE400
INNER JOIN TABLE120
ON TABLE400.CYP = TABLE120.CYP
INNER JOIN TABLE100
ON TABLE120.PID = TABLE100.COL13
ON TABLE124.PID = TABLE100.COL13
INNER JOIN TABLE230
ON TABLE120.PID = TABLE230.PID
AND TABLE120.CYP = TABLE230.CYP
ON TABLE104.PID = TABLE230.PID
AND TABLE104.PID = TABLE124.PID
ON TABLE126.PID = TABLE104.PID
ON TABLE110.EID = TABLE230.EID
AND TABLE110.EID = TABLE126.EID
ON TABLE130.GCD = TABLE230.GCD
AND TABLE130.EID = TABLE110.TID
ON TABLE114.GCD = TABLE130.GCD
ON TABLE302.COL7 = TABLE230.LD
AND TABLE302.COL10 = TABLE400.HP
AND TABLE302.COL11 = TABLE400.SP
INNER JOIN TABLE180
INNER JOIN TABLE320
ON TABLE180.RN = TABLE320.COL23
ON TABLE302.COL7 = TABLE320.CID
INNER JOIN TABLE314
ON TABLE302.ZID = TABLE314.COL4
ON TB1.COL1 = TABLE230.GCD
LEFT JOIN TABLE312
ON TABLE314.COL4 = TABLE312.TID
GROUP BY TB1.COL1
,TABLE302.COL2
,TABLE314.COL3
,TABLE314.COL4
,TABLE312.COL5
,TABLE314.COL6
,TABLE302.COL7
,TABLE320.COL8
,TABLE320.COL9
,TABLE302.COL10
,TABLE302.COL11
,TABLE230.COL12
,TABLE100.COL13
,TABLE100.COL14
,TABLE104.COL15
,TABLE110.COL16
,TABLE230.COL17
,TABLE230.COL18
,TB1.COL19
,IIf([TABLE230.CD]>' '
And format(cast(TABLE230.DT as date),'yyyy-MM-dd') = format(cast('12/31/9999' as date),'yyyy-MM-dd'), TABLE230.AMT,TB1.O)
,TABLE230.COL20
,TABLE230.COL21
,format(cast(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) as date),'yyyy-MM-dd')
,TABLE104.COL22
,TABLE320.COL23
,TABLE180.COL24
,IIf(format(cast(TABLE230.FDT as date),'yyyy-MM-dd') = format(cast('1/1/1901' as date),'yyyy-MM-dd')
And format(cast(TABLE230.DDT as date),'yyyy-MM-dd') = format(cast('1/1/1901' as date),'yyyy-MM-dd'),'NOT ACTIVE','ACTIVE')
,TABLE230.COL25
,TABLE230.FDT
,TABLE230.MDT
,TABLE230.NCD
,TABLE114.GCD
,TABLE230.MDT
HAVING TABLE314.COL4 = 99999999 -- If I move this line to WHERE it runs 2x longer
and format(cast(TABLE230.MDT as date),'yyyy-MM-dd') > format(cast('12/31/2019' as date),'yyyy-MM-dd')
最佳答案
区别在于执行计划。您必须查看两个两个查询的执行计划才能发现差异。
根据我的经验,差异通常是由于能够为 GROUP BY
使用索引。 . WHERE
中的过滤阻止使用索引。但是,您的查询并非如此,因为它是按多个表中的列聚合的。
另一种可能是过滤器移除的记录比较少,但是影响了JOIN
的执行计划。 s。我怀疑这就是你所看到的原因。您需要查看执行计划以查看连接是否相同。
关于sql - HAVING 的条件比 WHERE 的相同条件更快?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59399216/
我是一名优秀的程序员,十分优秀!