6

Table Segments has an index by DEPARTMENT (VARCHAR(10)) and BDPID(VARCHAR(10)).

Execution time of the first query is 34 seconds

SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
     ON   seg.DEPARTMENT  = 'DEP345'
     AND  seg.BDPID       = c.BDPID

When I moved the parameter for DEPARTMENT to a variable, execution time became 1 second. Execution plan #2 (fast)

DECLARE @dd VARCHAR(10)
SET @dd = 'DEP345'
SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
     ON   seg.DEPARTMENT  = @dd
     AND  seg.BDPID       = c.BDPID

But I have to use dynamic sql. And when I moved query to sp_execitesql the execution time again became 34 seconds. Execution plan #3 (slow)

EXECUTE sp_executesql
     'SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
     FROM Customers c
     LEFT JOIN Segments seg
          ON   seg.DEPARTMENT  = @dd
          AND  seg.BDPID     = c.BDPID',
     '@dd VARCHAR(10)',
     @dd = 'DEP345'

How can I get performance of the second query using dynamic sql?

  • 5
    This is the second or third TOP query I've seen today without ORDER BY. Do you not care which 10 you get? – Aaron Bertrand May 18 '17 at 0:08
  • @AaronBertrand, top 10 is for this post. without top 10 execution time becomes more than an hour. – rnofenko May 18 '17 at 0:11
  • I suspect removing the top takes longer because this is a huge table and you are pulling all that data over a slow network, waiting for it to render in an SSMS grid, etc. Try top 10 with an order by that makes use of an index and makes your results predictable and repeatable. You'll also have to deal with the parameter sniffing aspects if the department column has really skewed data. – Aaron Bertrand May 18 '17 at 0:20
5

What you're running into is the curse of local variables.

The short of it is that when you declare a variable and then use it in a query, SQL can't sniff the value.

It sometimes uses magic numbers depending on how the variable is used (there are different guesses for BETWEEN, >, >= , <, <=, <>.

For equality searches, the density vector is used, (though the column being defined as unique matters here) but the bottom line is that the cardinality estimate is usually way off. Lots more information here.

This is your plan for the fast query:

Nuts

For parameterized dynamic SQL, the value can be sniffed, and you end up with a totally different plan with totally different estimates.

Nuts

If you want a quick and dirty option, you can use OPTIMIZE FOR UNKNOWN

EXECUTE sp_executesql
    @stmt =  N'SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
     FROM Customers c
     LEFT JOIN Segments seg
          ON   seg.DEPARTMENT  = @dd
          AND  seg.BDPID     = c.BDPID
          OPTION (OPTIMIZE FOR (@dd UNKNOWN));', 
          @params N'@dd VARCHAR(10)', @dd = 'DEP345'

Which will get you the density vector estimate, but that's a hacky solution and I sort of hate it. This of course goes out the window when you remove the TOP 10 from your query, which you mentioned in a comment is just for this post.

What I'd much rather see you do is some index tuning. I'm willing to bet the Segment table is crying for a clustered index -- that many rows in a HEAP is usually a sign of trouble (forwarded fetches can ruin your day).

The nonclustered index that gets used here isn't covering, which is also dragging this query down badly.

You could get rid of the RID Lookup by adding FINAL_SEGMENT as an included column.

Nuts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.