Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RabBell
Starting Member
8 Posts |
 Posted - 2009-09-21 : 07:06:33
|
HiI have a small problem with some paging SQL code I'm using and was looking for some advice. First here is the code...quote:
WITH OrderedOrders AS ( SELECT EdArt.Section, EdArt.Status,ROW_NUMBER() OVER(ORDER BY Ed.CreatedDate DESC) AS 'RowNumber' FROM Table1 Ed, Table2 EdArt WHERE (EdArt.Section_I = "LIVE") AND (Ed.ID = EdArt.ID) AND (EdArt.Status_I = 1) ) SELECT * FROM OrderedOrders WHERE (RowNumber BETWEEN @start and @stop)
This code works fine, but in the OrderedOrders section you might notice I'm searching through the whole table to page the contents. In the example above @start and @stop have the value of 1 and 20 which is fine if the table only has a small number of records, but this search brings back over 150, 000 records. This is running really slow when searching the whole table and I know it's the OrderedOrders part doing it.Now you're probably thinking, why not use the CreatedDate to filter some results, well that is what the query will do most of the time but I do want to be able to page all results. Anyone know an easier way to page or a better way to utilise OrderedOrders than this? |
|
|
Andreas
Starting Member
11 Posts |
Posted - 2009-09-21 : 08:53:57
|
| 1) Do you have an index on Ed.CreatedDate?2) If Ed.ID is an identity column and a clustered index and you are never updating the CreatedDate column, then order over the identity column instead, it's probably faster.3) If the EdArt table is large then check that you have an index on EdArt.Section_I and EdArt.Status_I (assuming EdArt.ID is the primary key thus already have an index).Edit:Also make sure you add top(@stop) to the query in the CTE, no point in fetching more records than you need... |
 |
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-09-21 : 08:59:25
|
| Try paging with rowcount, in my experience it works much faster, the 1st link i found on google with examples http://blogs.x2line.com/al/archive/2004/05/29/325.aspx |
|
|
|
|
|
|
|
|
Paging Problems