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.

Answer 1

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...