问题描述
我正在尝试优化复杂的 SQL 查询，但当我做出看似无关紧要的更改时，却得到了截然不同的结果.
I'm trying to optimize a complex SQL query and getting wildly different results when I make seemingly inconsequential changes.
例如，这需要 336 毫秒才能运行:
For example, this takes 336 ms to run:
Declare @InstanceID int set @InstanceID=1;
With myResults as (
Select
Row = Row_Number() Over (Order by sv.LastFirst),
ContactID
From DirectoryContactsByContact(1) sv
Join ContainsTable(_s_Contacts, SearchText, 'john') fulltext on (fulltext.[Key]=ContactID)
Where IsNull(sv.InstanceID,1) = @InstanceID
and len(sv.LastFirst)>1
) Select * From myResults Where Row between 1 and 20;
如果我用硬编码数字替换@InstanceID，则运行需要超过 13 秒(13890 毫秒):
If I replace the @InstanceID with a hard-coded number, it takes over 13 seconds (13890 ms) to run:
Declare @InstanceID int set @InstanceID=1;
With myResults as (
Select
Row = Row_Number() Over (Order by sv.LastFirst),
ContactID
From DirectoryContactsByContact(1) sv
Join ContainsTable(_s_Contacts, SearchText, 'john') fulltext on (fulltext.[Key]=ContactID)
Where IsNull(sv.InstanceID,1) = 1
and len(sv.LastFirst)>1
) Select * From myResults Where Row between 1 and 20;
在其他情况下，我得到了完全相反的效果:例如，使用变量 @s 而不是文字john"会使查询运行速度减慢一个数量级.
In other cases I get the exact opposite effect: For example, using a variable @s instead of the literal 'john' makes the query run more slowly by an order of magnitude.
有人可以帮我把它们联系起来吗?变量什么时候让事情变快，什么时候让事情变慢?
Can someone help me tie this together? When does a variable make things faster, and when does it make things slower?
推荐答案
原因可能是 IsNull(sv.InstanceID,1) = @InstanceID 对 的某些值非常有选择性@InstanceID，但对其他人不是很挑剔.例如，可能有数百万行 InstanceID = null，因此对于 @InstanceID = 1，扫描可能会更快.
The cause might be that IsNull(sv.InstanceID,1) = @InstanceID is very selective for some values of @InstanceID, but not very selective for others. For example, there could be millions of rows with InstanceID = null, so for @InstanceID = 1 a scan might be quicker.
但是如果你明确提供@InstanceID的值，SQL Server会根据表的统计信息知道它是否是选择性的.
But if you explicitly provide the value of @InstanceID, SQL Server knows based on the table statistics whether it's selective or not.
首先，确保您的统计数据是最新的:
First, make sure your statistics are up to date:
UPDATE STATISTICS table_or_indexed_view_name
然后，如果问题仍然存在，请比较两种方法的查询执行计划.然后，您可以使用查询提示强制执行最快的方法.
Then, if the problem still occurs, compare the query execution plan for both methods. You can then enforce the fastest method using query hints.
这篇关于T-SQL 查询性能难题:为什么使用变量会有所作为?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！