Wednesday, March 26, 2008

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.

Tuesday, March 18, 2008

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