Paul White NZ (5/5/2010)
TheSQLGuru (5/5/2010)
Wouldn't APPLY also force a nested loop iteration over each partition and thus also be slow?Yes to the loop join - but that's exactly what is required 😉
Fetching the top or bottom one row per partition is the name of the game here.
Given a straight choice between a WHILE loop and a correlated loop join... 😉
It would be hard to show a real performance difference over a small number of iterations (like the 10 here), but my point is simply that a set-based solution (correlated loop join or no) is a better design.
Well, the BEST answer is for MS to fix the damn optimizer to work properly with partitions in ALL ASPECTS!! 🙂 But they are too busy building stuff that no one (or maybe 0.03% of installed base) will EVER use . . . sigh . . . :angry:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service