SQL参数导致查询变慢

Question

SQL参数导致查询变慢

sqlsql-serversql-server-2008ado.netparameterized

5

我有一个查询，通过ADO.NET与SQL Server 2008R2一起使用。当我在行内使用LIKE子句时，它可以在不到一秒钟的时间内工作，并从200万行中返回5行。如果我像在.NET中一样在SSMS中在查询开头声明参数，那么它将需要很长时间。

这是相同的查询，但使用了参数化。

第一个（正常工作）是：

;WITH Results_CTE AS (
    SELECT  ld.* , ROW_NUMBER() OVER (ORDER BY PK_ID) AS RowNum  
    FROM list..List_Data ld 
    WHERE Name IS NOT NULL  AND 
    Postcode LIKE 'SW14 1xx%' 
) SELECT * FROM Results_CTE

第二个需要永远等待的是：

declare @postcode varchar(10) = 'SW14 1xx'
;WITH Results_CTE AS (
    SELECT  ld.* , ROW_NUMBER() OVER (ORDER BY PK_ID) AS RowNum  
    FROM list..List_Data ld
    WHERE Name IS NOT NULL  AND 
    Postcode LIKE @postcode +'%' 
) SELECT * FROM Results_CTE

我认为这与SQL Server的内部工作机制有关，但我真的不知道。

- Echilon

在 like 上搜索会使优化器不太可能使用索引。在第一种情况下，它可以看到字符串开头没有通配符，因此它将使用索引。也许您可以“提示”使用索引。 - Klas Lindbäck

SQL Server 实现了参数化的 LIKE 作为一个范围查找，所以在存在非前导通配符的查询情况下不会受到惩罚（在存在前导通配符的情况下，范围是整个索引）。但是，在这种情况下，如果 Postcode 上没有覆盖索引，则它不会使用它，因为它会高估需要进行的查找次数。 - Martin Smith

@Echilon：第二个查询的执行计划是什么？你可以尝试在调用查询之前，在 postcode 上添加 '%'（以防万一 + '%' 让优化器困惑）。这应该不会有影响，但值得一试。 - Klas Lindbäck

在处理 SQL Server 性能问题时，您应该始终获取执行计划。 - Justin

4个回答

3

使用

SELECT * 
FROM Results_CTE 
OPTION (RECOMPILE)

SQL Server不会嗅探变量的值，因此它不知道它有多么具有选择性，可能会假设查询返回的行数比实际情况要多得多，并为此而生成一个优化的计划。

在您的情况下，我相信在好的计划中，它将使用非覆盖非聚集索引来评估PostCode谓词，并进行一些查找以检索缺少的列，而在坏计划中（因为它猜测查询将返回更多的行），它避免了这个方法而采取了全表扫描。

- Martin Smith

1

这个能用来加速所有参数化查询吗？我们在一个查询中遇到了性能问题，基本上是这样的：WHERE DATECOL BETWEEN @D1 AND @D2，当我们将实际日期值作为字面量插入到 SQL 中时，它的速度显著提高。我们能否在其上添加 OPTION (RECOMPILE) 来使其更快？我知道我应该测试一下，但我们重写了我们的 SQL 层以自动注入这些日期，所以现在创建一个可行的测试有点麻烦。 - Lasse V. Karlsen

@LasseV.Karlsen：重新编译查询并非免费，因此这将是一个权衡行为。在我处理的数据库上，每次查询重新编译大约需要增加30毫秒的时间。 - Andomar

@Justin - 只有在查询自动参数化时才会发生（在Lasse的情况下并没有，或者他们使用的修复程序不起作用）。当然，他们可能已经开启了针对adhoc工作负载的优化，这将减少实际缓存的计划数量，但代价是增加编译次数。 - Martin Smith

1

@Echilon - 列的数据类型是什么？参数的数据类型是什么？（注意不要在varchar列上使用nvarchar参数，因为这会导致隐式转换）请参阅此答案以获取详细信息 - Martin Smith

Results_CTE表有一个RowNum列，我正在添加整数参数@Offset和@Limit，并使用BETWEEN子句。如果我通过SSMS运行它，它可以正常工作，但是在.NET中却不行。 - Echilon

显示剩余6条评论

2

您可以使用“优化”来使参数查询使用与具有特定参数的查询相同的执行计划：

SELECT * 
FROM Results_CTE 
OPTION (OPTIMIZE FOR (@postcode = 'SW14 1xx'))

- Andomar

这对我的情况看起来很有前途。 - Lasse V. Karlsen

1

这看起来像是参数嗅探引起的问题 - 在计划编译期间，SQL Server“嗅探”当前参数值并使用它来优化查询。可能导致的最常见问题是，如果在第一次运行/编译查询时使用了“奇怪”的参数值，则查询计划将针对该参数值进行优化，但参数嗅探可能会导致所有其他问题。

在您的情况下，如果使用空/ null值运行查询@postcode，则查询将使用LIKE '%'子句，这很可能会导致表扫描，因为在过滤器的开头使用了LIKE通配符。看起来要么计划最初是使用空的@postcode参数运行/编译的，要么SQL Server在处理此参数时出现了混淆。

您可以尝试以下几件事：

标记查询以重新编译，然后再次使用非空值运行查询@postcode。
“掩盖”参数以尝试防止参数嗅探，

例如：

declare @postcode varchar(10) = 'SW14 1xx'
declare @postcode_filter varchar(10) = @postcode + '%'
-- Run the query using @postcode_filter instead of @postcode

尽管这个查询看起来应该以完全相同的方式运行，但我发现SQL Server以奇怪的方式处理参数 - 关于何时确切地使用参数嗅探的规则有时可能会有些奇怪，因此您可能需要尝试上述变体。

- Justin

发布的代码使用了变量而不是参数。变量值仅在语句重新编译时被嗅探。实际上，导致OP中出现问题的原因是该值被屏蔽了。 - Martin Smith

谢谢，但是使用ADO.NET会非常繁琐。 - Echilon

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user4099684 · Accepted Answer

我正在使用谷歌搜索关于C#中SqlCommand.Parameters.Add()潜在问题的信息，然后找到了这个页面。我知道这是一个SQL Server的帖子，但其他人可能会通过谷歌找到它，并且它可能会对他们在C#方面有所帮助。

对我来说，以上所有答案都没有起作用，因此我尝试了另一种方法。

不是：

cmd.Parameters.Add(new SqlParameter("@postcode", postcode));

我使用了这个替代方案：

// Replace SqlDbType enumeration with whatever SQL Data Type you're using.
cmd.Parameters.Add("@postcode", SqlDbType.VarChar).Value = postcode;

不要忘记命名空间:

using System.Data;

希望这能帮助到某些人！