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.
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.
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)
By Dan Holmes 725 asked Aug 04, 2015 at 07:38 PM
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).
By Aaron Bertrand ♦ 1.7k answered Aug 04, 2015 at 07:48 PM
of course. feeling kinda Homer Simpson now.
By Dan Holmes 725 answered Aug 04, 2015 at 07:58 PM