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