sql-server – 搜查是否存在EXISTS优于COUNT！ ……不是吗？
|
副问题[/!--empirenews.page--]
我常常阅读当必需搜查行的存在时,应始终行使EXISTS而不是COUNT. 然而,在最近的几个场景中,我丈量了行使计数时的机能晋升. LEFT JOIN (
SELECT
someID,COUNT(*)
FROM someTable
GROUP BY someID
) AS Alias ON (
Alias.someID = mainTable.ID
)
我不认识汇报SQL Server“内部”产生了什么的要领,以是我想知道是否存在一个带有EXISTS的无法办理的缺陷,这对我已经完成的丈量很是故意义(可以说是RBAR吗？！). 你对这种征象有一些表明吗？ 编辑： 这是您可以运行的完备剧本： SET NOCOUNT ON
SET STATISTICS IO OFF
DECLARE @tmp1 TABLE (
ID INT UNIQUE
)
DECLARE @tmp2 TABLE (
ID INT,X INT IDENTITY,UNIQUE (ID,X)
)
; WITH T(n) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master.dbo.spt_values AS S
),tally(n) AS (
SELECT
T2.n * 100 + T1.n
FROM T AS T1
CROSS JOIN T AS T2
WHERE T1.n <= 100
AND T2.n <= 100
)
INSERT @tmp1
SELECT n
FROM tally AS T1
WHERE n < 10000
; WITH T(n) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master.dbo.spt_values AS S
),tally(n) AS (
SELECT
T2.n * 100 + T1.n
FROM T AS T1
CROSS JOIN T AS T2
WHERE T1.n <= 100
AND T2.n <= 100
)
INSERT @tmp2
SELECT T1.n
FROM tally AS T1
CROSS JOIN T AS T2
WHERE T1.n < 10000
AND T1.n % 3 <> 0
AND T2.n < 1 + T1.n % 15
PRINT '
COUNT Version:
'
WAITFOR DELAY '00:00:01'
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
T1.ID,CASE WHEN n > 0 THEN 1 ELSE 0 END AS DoesExist
FROM @tmp1 AS T1
LEFT JOIN (
SELECT
T2.ID,COUNT(*) AS n
FROM @tmp2 AS T2
GROUP BY T2.ID
) AS T2 ON (
T2.ID = T1.ID
)
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (RECOMPILE) -- Required since table are filled within the same scope
SET STATISTICS TIME OFF
PRINT '
EXISTS Version:'
WAITFOR DELAY '00:00:01'
SET STATISTICS TIME ON
SELECT
T1.ID,CASE WHEN EXISTS (
SELECT 1
FROM @tmp2 AS T2
WHERE T2.ID = T1.ID
) THEN 1 ELSE 0 END AS DoesExist
FROM @tmp1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (RECOMPILE) -- Required since table are filled within the same scope
SET STATISTICS TIME OFF
在SQL Server 2008R2(七个64位)上我获得了这个功效 COUNT版本：
EXISTS版本：
办理要领
任何工作都长短常有数的,出格是涉及到数据库时.在SQL中有很多表达沟通语义的要领.假若有一个有效的履历法例,也许是行使最天然的语法编写查询(而且,是的,这是主观的),而且只有在您得到的查询打算或机能不行接管时才思量重写. 对付它的代价,我本身对这个题目的观点是存在查询最天然地行使EXISTS来表达.这也是我的履历,EXISTS tends to optimize better比OUTER JOIN拒绝NULL更换.行使COUNT(*)和过滤= 0是另一种选择,刚亏得SQL Server查询优化器中有一些支持,但我小我私人发明这在更伟大的查询中是不行靠的.无论怎样,对付我来说,EXISTS好像比任何一种更换品更天然.
您的特定示例很风趣,由于它突出了优化措施处理赏罚CASE表达式(出格是EXISTS测试)中的子查询的方法. CASE表达式中的子查询 思量以下(完全正当的)查询： DECLARE @Base AS TABLE (a integer NULL);
DECLARE @When AS TABLE (b integer NULL);
DECLARE @Then AS TABLE (c integer NULL);
DECLARE @Else AS TABLE (d integer NULL);
SELECT
CASE
WHEN (SELECT W.b FROM @When AS W) = 1
THEN (SELECT T.c FROM @Then AS T)
ELSE (SELECT E.d FROM @Else AS E)
END
FROM @Base AS B;
semantics of 只有当转达谓词返回false时,才管帐算嵌套轮回毗连的内侧.总体结果是CASE表达式按次序举办测试,而且仅在没有满意先前表达式的环境下才评估子查询. 带有EXISTS子查询的CASE表达式 在CASE子查询行使EXISTS的环境下,逻辑存在测试实现为半毗连,可是在后头的子句必要时,必需保存凡是被半毗连拒绝的行.流经这种非凡范例的半毗连的行获取一个符号,以指示半毗连是否找到匹配.此符号称为探测列. 实现的细节是逻辑子查询被相干联接(‘apply’)替代为探测列.该事变由查询优化器中的简化法则执行,该法则称为RemoveSubqInPrj(在投影中删除子查询).我们可以行使跟踪符号8606查察具体信息： SELECT
T1.ID,CASE
WHEN EXISTS
(
SELECT 1
FROM #T2 AS T2
WHERE T2.ID = T1.ID
) THEN 1
ELSE 0
END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (QUERYTRACEON 3604,QUERYTRACEON 8606);
（编辑：湖南网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
