sql-server-2005 - 如何避免从 C# 构建的 Sql Server 2005 参数化查询变慢

Question

我正在构建一个复杂的查询以在 Web View 中显示一些统计结果。根据用户的选择， View 可以有几个不同的过滤器。此外，还可以使用通配符。

我正在使用 SqlParameters 在 c# 中以编程方式构建此查询。所以查询看起来像这样:

sc.CommandText = "SELECT * FROM table 
                  WHERE field1 = @filter1 
                  AND field2 LIKE @filter2"; //...and more parameters

sc.SqlParameters.Add(
   new SqlParameter("@filter1", SqlDbType.Int, 32) { Value = 1});

sc.SqlParameters.Add(
   new SqlParameter("@filter2", SqlDbType.VarChar, 446) { Value = "whatever%"});

这是一个非常简化的版本，但查询本身并不是重点。请记住，它可以有不同的可选参数(我认为这是很常见的情况)。

当我在 Sql Manager 中运行此查询时，我意识到使用参数时速度非常慢。因此，以下两个查询应该是相同的，它们使用不同的执行计划，这使得参数化的查询运行速度慢很多:

DECLARE @filter1 INT
DECLARE @filter2 VARCHAR 446
SET @filter1 = 1
SET @filter2 = "whatever%"

SELECT * FROM table WHERE field1 = @filter1 AND field2 LIKE @filter2

快速版本:

SELECT * FROM table WHERE field1 = 1 AND field2 LIKE 'whatever%'

这是另一个有同样问题的人的例子:

Why does a parameterized query produces vastly slower query plan vs non-parameterized query

好像有个东西叫 parameter sniffing ，这可能会使参数化查询运行得更慢，但它不适用于我的情况，因为这不是存储过程。

solutions 之一建议是使用 OPTION(RECOMPILE) 或 OPTION(OPTIMIZE FOR)。我不能这样做，因为我有大约 10 个可选参数，它们可能在过滤器中或不在过滤器中，并且在使用 LIKE 时此选项不起作用.

所以，我觉得我陷入了死胡同，我正在考虑摆脱参数并在代码上构建动态文字查询。但随后 Sql Injection 出现在游戏中。

那么，您对如何解决此问题还有其他建议吗？或者你知道一种安全的方法来逃避参数吗？

编辑 : 在这里您可以使用 LIKE 查看带有一个参数的查询的执行计划:

Execution Plan

编辑 :更简化的代表性查询执行计划:

Simplified execution plan

Answer 1

查看执行计划中的“估计行数”属性。对于您的慢版本(带有参数)，SQL Server 无法很好地估计您的查询将返回的行，因为它不会在编译时评估变量的实际值。它只会利用统计信息来估计您用作过滤器的那些字段的基数，并根据它创建一个执行计划。

我对此类问题的解决方案是创建一个存储过程，其参数与您想要的过滤器一样多:

CREATE PROCEDURE your_sp @filter1 INT, @filter2 VARCHAR(446) AS
 SELECT * FROM table 
 WHERE field1 = @filter1 
 AND field2 LIKE @filter2

sc.CommandText = "your_sp";
sc.CommandType = CommandType.StoredProcedure;

sc.SqlParameters.Add(new SqlParameter("@filter1", SqlDbType.Int, 32) { Value = 1});

sc.SqlParameters.Add(new SqlParameter("@filter2", SqlDbType.VarChar, 446) { Value = "whatever%"});

connection.Open();
SqlDataReader reader = command.ExecuteReader();