sql-server – 使用带参数的DISTINCT时选择性能性能下降

前端之家收集整理的这篇文章主要介绍了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.任何想法?

解决方法

问题不在于DISTINCT导致参数性能下降,而是在参数化查询中没有对查询的其余部分进行优化,因为优化器不会仅使用1 = @ IS_ADMINISTRATOR来优化所有连接喜欢它只有1 = 1.它不会在没有区别的情况下优化连接,因为它需要根据连接的结果返回重复项.

为什么?因为抛出所有连接的执行计划对于除@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.我认为这是一个疏忽.

原文链接:https://www.f2er.com/mssql/84157.html

猜你在找的MsSQL相关文章