ROW_NUMBER() Sucks?

Is it just me, or does the SQL Server 2005 ROW_NUMBER() function's performance suck? I always used to complain about SQL Server's lack of a LIMIT clause like MySQL has. I was thrilled when 2005 came out with the ROW_NUMBER() function.

When we switched to SQL 2005, I dutifully recoded the main search query to use the ROW_NUMBER() function, only to find out that it hurt performance by almost 3 fold! In addition, using ROW_NUMBER() doesn't give you an easy way to get the total number of records a query would return. (Helpful to display something like, "Viewing records 51-100 of 2,120")

I should put a caveat here: My testing shows that ROW_NUMBER() is actually much faster when doing very simple queries with large numbers of rows. For example, if you need to quickly grab 10 rows in the middle of all 10,000 rows in a single table with no joins, ROW_NUMBER() is probably for you. However, if you're doing a more complex query like to search for specific records or joining with many other tables, my testing shows ROW_NUMBER() is a significant slow-down. Here are the results for my specific query:

Description	Rows	Estimated Cost	Actual Time	Slowdown
My original query	12,547	3.701	0.729	-
Using ROW_NUMBER()	12,547	6.616	2.094	287.1%!

So, if not ROW_NUMBER(), then what? My solution is to declare a temporary table and insert all of your result IDs into that table, along with the ordering. You can then join with that table to grab only the results you need. For example:

DECLARE @FirstRecord Int
SET @FirstRecord=0
DECLARE @LastRecord Int
SET @LastRecord=49

--First make a table to hold the order of the final sorted results
DECLARE @ResultOrder TABLE  
(     OrderID int identity(0,1) not null primary key clustered,
    ResultID int    
);

--The main "search" query... this can be pretty slow with lots of rows
INSERT INTO @ResultOrder (ProductID)
    SELECT p.ProductID
    FROM Products p
    WHERE p.Cost < 100 AND p.CategoryID=4 --Or whatever search you're doing

--A very fast way to get the total number of results
SELECT COUNT(*) FROM @ResultOrder; 

--A very fast way to get the details... you can even join with many more tables
SELECT p.Name, p.Cost, c.Name AS CategoryName, s.Name AS SalesPerson
FROM @ResultOrder ro, Products p, Categories c, SalesPeople s
WHERE p.ProductID=ro.ProductID
    AND p.CategoryID=c.CategoryID
    AND p.SalesPersonID=s.SalesPersonID
ORDER BY ro.OrderID

Obviously, that is a simple example, but I suspect as your query gets more complicated (especially the 2nd details part) you're way better off not using ROW_NUMBER().

As an ADDITIONAL speedup, that first result set is potentially PRIME cacheing material. For example, if a user (or a crawler) hits the first page of results, then clicks "next" a few times. You do the expensive search only once, then quickly pull up the details for each page on subsequent requests. You might have to analyze your usage to see how often and how close together different pages of the same search are requested.

How do you best cache those Result IDs you ask? And how do you get the IDs to and from SQL? Well, I'm about to implement that myself so hopefully I'll let you know in a subsequent blog post.

TWL Two Letter Words Sorted By Second Letter

I'm playing a lot of scrabulous online lately and I constantly want to see which two letter words END in a given letter. So for your enjoyment, here are the 101 words defined by the TWL sorted alphabetically by first letter and second letter.
In list format:

Sorted by the first letter AA AB AD AE AG AH AI AL AM AN AR AS AT AW AX AY BA BE BI BO BY DE DO ED EF EH EL EM EN ER ES ET EX FA FE GO HA HE HI HM HO ID IF IN IS IT JO KA KI LA LI LO MA ME MI MM MO MU MY NA NE NO NU OD OE OF OH OI OM ON OP OR OS OW OX OY PA PE PI QI RE SH SI SO TA TI TO UH UM UN UP US UT WE WO XI XU YA YE YO ZA Sorted by the second letter AA BA FA HA KA LA MA NA PA TA YA ZA AB AD ED ID OD AE BE DE FE HE ME NE OE PE RE WE YE EF IF OF AG AH EH OH SH UH AI BI HI KI LI MI OI PI QI SI TI XI AL EL AM EM HM MM OM UM AN EN IN ON UN BO DO GO HO JO LO MO NO SO TO WO YO OP UP AR ER OR AS ES IS OS US AT ET IT UT MU NU XU AW OW AX EX OX AY BY MY OY

And in table format:

Sorted by 1st letter	Sorted by 2nd letter
AA AB AD AE AG AH AI AL AM AN AR AS AT AW AX AY BA BE BI BO BY DE DO ED EF EH EL EM EN ER ES ET EX FA FE GO HA HE HI HM HO ID IF IN IS IT JO KA KI LA LI LO MA ME MI MM MO MU MY NA NE NO NU OD OE OF OH OI OM ON OP OR OS OW OX OY PA PE PI QI RE SH SI SO TA TI TO UH UM UN UP US UT WE WO XI XU YA YE YO ZA	AA BA FA HA KA LA MA NA PA TA YA ZA AB AD ED ID OD AE BE DE FE HE ME NE OE PE RE WE YE EF IF OF AG AH EH OH SH UH AI BI HI KI LI MI OI PI QI SI TI XI AL EL AM EM HM MM OM UM AN EN IN ON UN BO DO GO HO JO LO MO NO SO TO WO YO OP UP AR ER OR AS ES IS OS US AT ET IT UT MU NU XU AW OW AX EX OX AY BY MY OY