Medians, ROW_NUMBERs, and performance

re: Medians, ROW_NUMBERs, and performance

Adam Machanic — Mon, 18 Dec 2006 21:46:16 GMT

Paul Nielsen asked me for clarification on the final paragraph and why Ben-Gan's "should" perform better. Here's my response:

Ben-Gan's -should- perform better, because the QP shouldn't have to do a table spool to get the row count at the same time it does the row number. Row numbering is essentially the same operation as row counting (a walk over the rows that satisfy a given predicate, except that row numbering has to be ordered/sequential), so I would expect a shortcut by the QP in that case -- but the SQL Server team has obviously not implemented anything along those lines. If the
QP did behave that way, Ben-Gan's query would be much better than Celko's because it wouldn't have to do an intermediate sort (assuming a supporting index existed).

Medians, Actual Query Cost, and Statistics

Peter DeBetta's SQL Programming Blog — Wed, 20 Dec 2006 11:18:57 GMT

I was going to comment on Adam's post "Medians, ROW_NUMBERs, and performance" (which&nbsp;was a follow-up&nbsp;to...

Medians, Actual Query Cost, and Statistics

Peter DeBetta's SQL Programming Blog — Tue, 02 Jan 2007 21:12:11 GMT

I was going to comment on Adam's post " Medians, ROW_NUMBERs, and performance " (which was a follow-up

re: Medians, ROW_NUMBERs, and performance

Michelle Ufford — Wed, 16 Mar 2011 15:48:19 GMT

Thank you for this post, Adam! :)

re: Medians, ROW_NUMBERs, and performance

Mike L — Tue, 23 Aug 2011 14:50:17 GMT

This is a very, very cool solution. :) Thanks for sharing it.

re: Medians, ROW_NUMBERs, and performance

Fkoffee — Mon, 14 May 2012 17:56:48 GMT

thanks a ton!!!