Why the time difference between the executions?

Dan Holmes 2015-08-04 19:38:44

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)

Aaron Bertrand 2015-08-04 19:48:55
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).
Dan Holmes 2015-08-04 19:58:44
of course. feeling kinda Homer Simpson now.