ROW_NUMBER OVER (ORDER BY date_column)

Question

I am wondering. I have a complex query which runs in a SQL Server 2005 Express edition in around 3 seconds.

The main table has around 300k rows.

When I add

ROW_NUMBER() OVER (ORDER BY date_column)

it takes 123 seconds while date_column is a datetime column.

If I do

ROW_NUMBER() OVER (ORDER BY string_title)

it runs in 3 seconds again.

I added an index on the datetime column. No change. Still 123 seconds.

Then I tried:

ROW_NUMBER() OVER (ORDER BY CAST(date_column AS int))

and the query runs in 3 seconds again.

Since casting needs time, why does SQL Server behave like this???

UPDATE: It seems like ROW_NUMBER ignore my WHERE statements at all and build a row column list for all available entries? Can anyone confirm that ?

Here I copied a better read able (still tonz of logic :)) in the SQL Management Studio:

SELECT ROW_NUMBER() OVER (ORDER BY xinfobase.lid) AS row_num, *
FROM xinfobase
LEFT OUTER JOIN [xinfobasetree] ON [xinfobasetree].[lid] = [xinfobase].[xlngfolder] 
LEFT OUTER JOIN [xapptqadr] ON [xapptqadr].[lid] = [xinfobase].[xlngcontact] 
LEFT OUTER JOIN [xinfobasepvaluesdyn] ON [xinfobasepvaluesdyn].[lparentid] = [xinfobase].[lid] 
WHERE (xinfobase.xlngisdeleted=2 
AND xinfobase.xlinvalid=2) 
AND (xinfobase.xlngcurrent=1) 
AND ( (xinfobase.lownerid = 1  
       OR (SELECT COUNT(lid) 
           FROM xinfobaseacl 
           WHERE xinfobaseacl.lparentid = xinfobase.lid 
             AND xlactor IN(1,-3,-4,-230,-243,-254,-255,-256,-257,-268,-589,-5,-6,-7,-8,-675,-676,-677,-9,-10,-864,-661,-671,-913))>0 
               OR xinfobasetree.xlresponsible = 1) 
AND (xinfobase.lid IN (SELECT lparentid 
                       FROM xinfobasealt a, xinfobasetree t 
                       WHERE a.xlfolder IN(1369) 
                         AND a.xlfolder = t.lid 
                         AND dbo.sf_MatchRights(1, t.xtxtrights,'|')=1 )) ) 
AND ((SELECT COUNT(*) FROM dbo.fn_Split(cf_17,',') 
      WHERE [value] = 39)>0)

This query need 2-3 seconds on 300k records. Now I changed the ORDER BY to xinfobase.xstrtitle then it runs in around 2-3 seconds again. If I switch to xinfobase.dtedit (datetime column with an additional index I just added) it needs hte time I mentioned above already.

I also tried to "cheat" and made my statement as a SUB SELECT to force him to retriev the records first and do a ROW_NUMBER() outside in another SQL statement, same performance result.

Not that much time; other things (like a table scan when one shouldn't be needed) can far exceed the overhead of a cast. — Robert Harvey, Jan 16 '13 at 16:57
I checked my indexes and they are fine regarding my where statements. — YvesR, Jan 16 '13 at 16:58
Show your entire query, and the results of your estimated query plan from SSMS. — Robert Harvey, Jan 16 '13 at 16:59
Btw, the sql statement is created by Ruby on Rails in the ORM mapping, so I checked my log files and fetched the created SQL statement. The gem "will paginate" is used here. — YvesR, Jan 16 '13 at 16:59
@RobertHarvey I can post the SQL but since it is created by RoR it is not really nice to read :) — YvesR, Jan 16 '13 at 17:00
You may need to run a custom query or stored procedure, instead of the RoR generated one. — Robert Harvey, Jan 16 '13 at 17:00
Take a look at [this](http://stackoverflow.com/questions/1716798/sql-server-2008-ordering-by-datetime-is-too-slow). Same question, several good answers. — jpw, Jan 16 '13 at 17:02
@jpw I checked the indexes so far. and as you can see in my post, I tested with string_column which is not even in the index and my query runs in 3 seconds. — YvesR, Jan 16 '13 at 17:04
You may also want to have a look here if you want to page the result set: http://www.4guysfromrolla.com/webtech/042606-1.shtml (`SET ROWCOUNT`) — Tim Schmelter, Jan 16 '13 at 17:08
@TimSchmelter Interesting article but in my case rails created the stuff using active record, so not much of a choice. My workaround is to cast it as an int and put the datetime column in another column, but I wonder why I have todo this... — YvesR, Jan 16 '13 at 17:21
Compare the plans with date column and integer column side by side and see what is the main diff. Did you update the stats on the tables involved? — Gulli Meel, Jan 16 '13 at 18:18

YvesR · Accepted Answer · 2013-01-17T13:48:47.253

UPDATE

After I was still frustrated about doing a workaround I was investigating more. I removed all my existing indexes and run several SQL statements against the tables. It turns out, that building new indexes with a new sortorder of columns and include different columns I fixed my issue and the query is fast with dtedit (datetime) column as well.

So lessons learned: Take more care of your indexes and execution plans and recheck them with every update (new version) of the software you produce...

But still wonderung why CAST(datetime_column AS int) makes it fast before...

ROW_NUMBER OVER (ORDER BY date_column)

1 Answers1