12

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?

Herb Caudill
  • 50,043
  • 39
  • 124
  • 173
  • You realize that using `TOP 20` and moving the ORDER BY from the ROW_NUMBER means you don't need the CTE? – OMG Ponies Feb 19 '10 at 02:59
  • @OMG: only if those numbers never change - if he wants to get rows 800 - 820, the CTE method is much faster – Gabriel McAdams Feb 19 '10 at 03:05
  • @OMG: @Gabriel is right, this is used to deliver paged results, so it could be `Row between 20 and 40` etc. – Herb Caudill Feb 19 '10 at 13:26
  • I ran into a similar issue where I have a variable for the TOP(n) and a variable for passing in dateTime in a Delete statement. During testing, if I don't use the variables, and hardcode the value, it ran pretty fast. Once I use the variable, the query become extremely, and I suspect it is because SQL can't determine the estimated effective rows, thus it will perform a page-lock, while a row-lock is really what it needs. – dsum Apr 10 '13 at 19:32

2 Answers2

2

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
0

With hardcoded values the optimizer knows what to base on when building execution plan. When you use variables it tries to "guess" the value and in many cases it gets not the best one.

You can help it to pick a value for optimization in 2 ways:

  1. "I know better", this will force it to use the value you provide.

    OPTION (OPTIMIZE FOR(@InstanceID=1))

  2. "See what I do", this will instruct it to sniff the values you pass and use average (or most popular for some data types) value of those supplied over time.

    OPTION (OPTIMIZE FOR UNKNOWN)

root
  • 2,327
  • 1
  • 22
  • 18
  • `OPTION (OPTIMIZE FOR UNKNOWN)` will lead to exactly the same "guessing" behaviour as using a variable. `option (recompile)` will cause SQL Server to recompile the statement taking into account the actual variable value. – Martin Smith Jul 04 '12 at 20:37
  • OPTION (OPTIMIZE FOR UNKNOWN) will enable SQL to base guessing process on actual values you pass over time so it's not exactly the same although you can enable such behaviour by default for all SPs – root Aug 02 '12 at 16:56