b5535084 (1/31/2012)
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.
Well I'd say half the elapsed time was pretty significant 😉
Anyway, the original join beats both by miles in I/O. Which surprises me.
There is a reason, but anyway I think we've gone as far with this as is sensible on this thread. Thanks for commenting.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi