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.我认为这是一个疏忽.

（编辑：PHP编程网 - 黄冈站长网）

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