• I'm going to recommend incredible caution here. Even Itzik said it wasn't optimized in his original post.

    Internally, this as bad as a double cross join because it generates 1005 rows from the tblReceivedItems (15 items extracted 67 times), sorts those items 67 times, spools out 2010 items into an aggregate that's executed 67 times, and spits out the original 15 rows to yet another sort that get's executed 67 time, and... you get the idea.

    I've not done a deep dive on the required indexing for this method (been too busy) but it would appear that they'd need to be relatively wide perhaps fully covering indexes.

    Just for this relatively small bit of data, the code uses 686 logical reads and we're talking about a total of less than 100 rows combined in both tables.

    I believe that some "Divide'n'Conquer" is in order here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)