@PaulWhite When you unrolled the loop, did you remove the line of code that copies the input parameters into the array that you were looping over? I still see a non-trivial difference when doing that, even in your latest code.
The vast majority of the CPU is spent allocating arrays in the latest iteration.
> A much better execution plan would scan the non-clustered index, assign row numbers, filter the records, and only then use a Key Lookup to fetch the off-index columns. Since we know we will only be returning a maximum of 50 rows, it seems likely that the cost of 50 lookups would be more than offset by scanning the much narrower non-clustered index to begin with.
Unfortunately, SQL Server does not yet include logic to spot this sort of optimisation, and always places a Key Lookup just after the Index Seek or Scan it is associated with.
I feel like having your vape pen hooked up to a major body of water to keep it's nuclear power source cool seems a bit limiting in the devices mobility
Original: CPU time = 1954 ms, elapsed time = 816 ms. After combining two CTEs: CPU time = 110 ms, elapsed time = 151 ms. After adding indexes: CPU time = 0 ms, elapsed time = 69 ms.
@ErikDarling Oh the chemo for sure. However it's easier to do because you don't really have any choice you know? Quitting smoking is harder because you can always just buy a pack and give up the misery.
This week, we have an “ask me anything” panel with SQL Server experts @johnedba, @sqL_handLe, and Joe Obbish! Get your questions in now or ask them live! Details at http://madpass.pass.org
