Why the time difference between the executions?

The attached plan has two executions of the same proc. The only difference is the parameter named @onboarddeviceid. For the first one, that doesn't return any rows. The second will return one row.

the first execution is 4+ seconds while the second is subsecond. I can't find a reason in the plan for the difference.

For the value 141 which has one row returned, here is the relevant histogram.

 RANGE_HI_KEY    RANGE_ROWS    EQ_ROWS     DISTINCT_RANGE_ROWS    AVG_RANGE_ROWS
 140                    0            388147.2 0                    1
 143                    145278.9    222512.7 1                    143788.9

Here is the same for the second value that doesn't have rows. Best i can tell these histograms show the same information which is really nothing about the value returned.

 RANGE_HI_KEY    RANGE_ROWS    EQ_ROWS     DISTINCT_RANGE_ROWS    AVG_RANGE_ROWS
 192                 0            343769.7 0                    1
 195                    16177.69    295173.2 1                    16011.77

I have also forced a recompile (sp_recompile) on the proc and ran them in the reverse order. that doesn't help either.

(me and this editor rarely get along. the format is all goofed up)

Plan.pesession (12.9 kB)
avatar image By Dan Holmes 725 asked Aug 04, 2015 at 07:38 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

Seems on first glance that the one that returned a single row returned much quicker because it short circuited and bailed out as soon as it found the row. The query that didn't find a row had to read through the entire table; it couldn't short circuit - you can see this from the Table I/O tab (or even just the Reads number in the Statement/Results grid).

avatar image By Aaron Bertrand ♦ 1.7k answered Aug 04, 2015 at 07:48 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

of course. feeling kinda Homer Simpson now.

avatar image By Dan Holmes 725 answered Aug 04, 2015 at 07:58 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x166
x89
x44
x20

asked: Aug 04, 2015 at 07:38 PM

Seen: 86 times

Last Updated: Aug 05, 2015 at 12:53 PM