T-SQL query performance puzzle: Why does using a variable make a difference?

Question

I'm trying to optimize a complex SQL query and getting wildly different results when I make seemingly inconsequential changes.

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;  

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;  

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?

Answer 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.

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.