ChrisM@Work (10/21/2016)
The guesses made by the optimiser are whacked somewhat by the ranges.
Yes they are WHacked.
Try forcing a loop join reading first from the ranges table like this:
SELECT *FROM #Ranges r
INNER loop JOIN #Lines_100 l
ON l.linenumber >= Low
AND l.linenumber <= High
-- (90019 row(s) affected) / 00:00:17
THANKS.
The improvement on the specific statements is Huge.
Also thanks for the example, although it does not actually match the situation, it does illustrate the working of the statement.
(My code performs extremely bad with the example code. I did not finish the test.)
Thanks for this huge improvement and educating me,
Ben
Because of changes in the testset, I can not reproduce the previous testtimes.
But for the currect set the suggested (forum) code (with indexes) outperforms my previous code (no indexes) with about a factor 25.