• TOP: CPU time = 1281 ms, elapsed time = 7777 ms, logical reads 223933 (ORDER_ITEM_DIM)

    ROW_NUMBER: CPU time = 2000 ms, elapsed time = 15948 ms, logical reads 303216 (ORDER_ITEM_DIM)

    TOP is better by every measure. The TOP plan looks better too: there's a (pointless) RID lookup in the ROW_NUMBER plan.

    Better? Well OK, a little, but not by an order of magnitude or anything significant, really. Anyway, the original join beats both by miles in I/O. Which surprises me. OTOH It points out the values of digging down into the measures like this.

    Good comment about the RID lookup. Something to watch out for...