sql-server – 行使带参数的DISTINCT时选择机能机能降落

赏金留意事项 – START：

参数SNIFFING(这是在赏金前题目中陈诉的独一“设法”)不是题目,由于您可以在题目末端的“更新”部门阅读.题目现实上与sql server如安在行使distinct时为参数化查询建设执行打算有关.
我上传了一个很是简朴的数据库备份(它合用于sql server 2008 R2)here(你必需在下载前守候20秒).对付此数据库,您可以实行运行以下查询：

-- PARAMETRIZED QUERY

declare @IS_ADMINISTRATOR int
declare @User_ID int
set @IS_ADMINISTRATOR = 1 -- 1 for administrator 0 for normal
set @User_ID = 50

SELECT DISTINCT -- PLEASE REMEMBER DISTINCT MAKES THE DIFFERENCE!!!
  DOC.DOCUMENT_ID
FROM
  DOCUMENTS DOC LEFT OUTER JOIN
  FOLDERS FOL ON FOL.FOLDER_ID = DOC.FOLDER_ID LEFT OUTER JOIN
  ROLES ROL ON (FOL.FOLDER_ID = ROL.FOLDER_ID)   
WHERE
  1 = @IS_ADMINISTRATOR OR  ROL.USER_ID = @USER_ID

-- NON PARAMETRIZED QUERY

SELECT DISTINCT -- PLEASE REMEMBER DISTINCT MAKES THE DIFFERENCE!!! 
  DOC.DOCUMENT_ID
FROM
  DOCUMENTS DOC LEFT OUTER JOIN
  FOLDERS FOL ON FOL.FOLDER_ID = DOC.FOLDER_ID LEFT OUTER JOIN
  ROLES ROL ON (FOL.FOLDER_ID = ROL.FOLDER_ID)   
WHERE
  1 = 1 OR  ROL.USER_ID = 50

最后留意：我留意到DSTINCT是题目,我的方针是在两个查询中到达沟通的速率(或至少险些沟通的速率).

赏金注释 – 竣事：

原始题目：

我留意到两者之间的机能差别很大

-- Case A
select distinct * from table where id > 1

较量(这是我的Delphi应用措施天生的sql)

-- Case B1
exec sp_executesql N'select distinct * from table where id > @P1',N'@P1 int',1

这相等于

-- Case B2
declare @P1 int
set @P1 = 1
select distinct * from table where id > @P1

A的执行速率比B1和B2快得多.假如我删除DISTINCT,机能会变得沟通.

你可以对此颁发评述吗？

在这里,我宣布了一个简朴的查询,我在3 INNER JOIN的查询中留意到了这一点.无论怎样不是一个伟大的查询.

留意：在A和B1 / B2环境下,我祈望具有完全沟通的机能.

那么行使DISTINCT有一些留意事项吗？

更新：

我实行行使DBCC TRACEON(4136,-1)(禁用参数嗅探的符号)禁用参数嗅探,但没有任何变革.以是在这种环境下,题目并没有链接到参数SNIFFING.任何设法？

办理要领

为什么？由于抛出全部毗连的执行打算对付除@IS_ADMINISTRATOR = 1之外的任何值都是无效的.无论您是否缓存打算,它都不会天生该打算.

这与我的2008处事器上的非参数化查询一样：

-- PARAMETRIZED QUERY

declare @IS_ADMINISTRATOR int
declare @User_ID int
set @IS_ADMINISTRATOR = 1 -- 1 for administrator 0 for normal
set @User_ID = 50

IF 1 = @IS_ADMINISTRATOR 
BEGIN
SELECT DISTINCT -- PLEASE REMEMBER DISTINCT MAKES THE DIFFERENCE!!!
  DOC.DOCUMENT_ID
FROM
  DOCUMENTS DOC LEFT OUTER JOIN
  FOLDERS FOL ON FOL.FOLDER_ID = DOC.FOLDER_ID LEFT OUTER JOIN
  ROLES ROL ON (FOL.FOLDER_ID = ROL.FOLDER_ID)   
WHERE
  1 = 1
END
ELSE 
BEGIN
SELECT DISTINCT -- PLEASE REMEMBER DISTINCT MAKES THE DIFFERENCE!!!
  DOC.DOCUMENT_ID
FROM
  DOCUMENTS DOC LEFT OUTER JOIN
  FOLDERS FOL ON FOL.FOLDER_ID = DOC.FOLDER_ID LEFT OUTER JOIN
  ROLES ROL ON (FOL.FOLDER_ID = ROL.FOLDER_ID)   
WHERE
  ROL.USER_ID = @USER_ID
END

从查询打算可以清晰地看到,运行您的示例的是@IS_ADMINISTRATOR = 1未获得优化,与1 = 1沟通.在非参数化示例中,JOINS已完全优化,它只返回DOCUMENTS表中的每个id(很是简朴).

当@IS_ADMINISTRATOR<>时,也会穷乏差异的优化. 1.譬喻,LEFT OUTER JOINS会在没有该OR子句的环境下自动变动为INNER JOIN,但它们与该子句保持原样.

另请参阅此谜底：SQL LIKE % FOR INTEGERS暗示动态SQL更换方案.

虽然,这并不能真正表明原始题目中的机能差别,由于哪里没有OR.我以为这是一个疏忽.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!