T-SQL 查询性能难题:为什么使用变量会有所作为? [英] T-SQL query performance puzzle: Why does using a variable make a difference?

问题描述

我正在尝试优化复杂的 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 查询性能难题:为什么使用变量会有所作为?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！