使用带参数的 DISTINCT 时 select 语句性能下降

赏金注意事项 - 开始：

参数嗅探（这是赏金前问题中报告的唯一“想法”）不是这里的问题，您可以在问题末尾的“更新”部分中阅读。这个问题实际上与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 有一些注意事项吗？

UPDATE:

我尝试使用禁用参数嗅探DBCC TRACEON (4136, -1)（禁用参数嗅探的标志）但没有任何变化。因此，在这种情况下，问题与参数嗅探无关。任何想法？

问题不在于 DISTINCT 导致参数性能下降，而在于参数化查询中查询的其余部分没有被优化掉，因为优化器不会只使用 1=@IS_ADMINISTRATOR 优化掉所有连接就像 1=1 一样。它不会优化连接without不同，因为它需要根据连接的结果返回重复项。

为什么？因为丢弃所有连接的执行计划对于 @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。在您的非参数化示例中，联接已完全优化，它仅返回 DOCUMENTS 表中的每个 id （非常简单）。

还存在不同的优化缺失@IS_ADMINISTRATOR <> 1。例如，LEFT OUTER JOINS 自动更改为INNER JOINs without that OR条款，但它们保持原样with那个或条款。

另请参阅这个答案：SQL LIKE % 对于整数动态 SQL 替代方案。

当然，这并不能真正解释你原来问题中的性能差异，因为你在那里没有 OR 。我认为这是一个疏忽。