Why the time difference between the executions?
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)
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).
2015-08-04 19:58:44
of course. feeling kinda Homer Simpson now.