6

There are a number of sql server paging questions on stackoverflow and many of them talk about using ROW_NUMBER() OVER (ORDER BY ...) AND CTE. Once you get into the hundreds of thousands of rows and start adding sorting on non-primary key values and adding custom WHERE clauses, these methods become very inneficient. I have a dataset of several million rows I am trying to page through with custom sorting and filtering, but I am getting poor performance, even with indexes on all the fields that I sort by and filter by. I even went as far as to include my SELECT columns in each of the indexes, but this barely helped and severely bloated my database.

I noticed the stackoverflow paging only takes about 500 milliseconds no matter what sorting criteria or page number you click on. Anyone know how to make paging work efficiently in SQL Server 2008 with millions of rows? This would include getting the total rows as efficiently as possible.

My current query has the exact same logic as this stackoverflow question about paging: Best paging solution using SQL Server 2005?

Community
  • 1
  • 1
jjxtra
  • 20,415
  • 16
  • 100
  • 140
  • Any chance you can post the query; general principles is a nice guy but he has issues in the real world ;) – u07ch Mar 17 '11 at 07:14
  • Doesn't adding an index on every column you want to sort by (or filter on), help? It seems obvious to me, but I don't know if you tried it. – bart Mar 17 '11 at 08:05
  • I do have indexes on all the columns. After paging beyond the first few thousands, query times start going up linearly... – jjxtra Jun 14 '11 at 05:36
  • i think stackoverflow uses nosql db. maybe memcache. – heximal Mar 17 '11 at 06:55
  • Also SO is using Lucene, so I imagine these queries aren't reaching the SQL server. – goalie7960 Nov 01 '11 at 16:47

2 Answers2

5

Anyone know how to make paging work efficiently in SQL Server 2008 with millions of rows?

If you want accurate perfect paging, there is no substitute for building an index key (position row number) for each record. However, there are alternatives.

(1) total number of pages (records)

  • You can use an approximation from sysindexes.rows (almost instant) assuming the rate of change is small.
  • You can use triggers to maintain a completely accurate, to the second, table row count

(2) paging

(a)
You can show page jumps within say the next five pages to either side of a record. These need to scan at most {page size} x 5 on each side. If your underlying query lends itself to travelling along the sort order quickly, this should not be slow. So given a record X, you can go to the previous page using (assuming sort order is a asc, b desc

select top(@pagesize) t.*
from tbl x
inner join tbl t on (t.a = x.a and t.b > x.b) OR
                    (t.a < a.x)
where x.id = @X
order by t.a asc, t.b desc

(i.e. the last {page size} of records prior to X)

To go five pages back, you increase it to TOP(@pagesize*5) then further TOP(@pagesize) from that subquery.

Downside: This option requires that you cannot directly jump to a particular location, your options are only FIRST (easy), LAST (easy), NEXT/PRIOR, <5 pages either side

(b)
If the paging is always going to be quite specific and predictable, maintain an INDEXED view or trigger-updated table that does not contain gaps in the row number. This may be an option if the tables normally only see updates at one end of the spectrum, with gaps from deletes easily filled quickly by shifting not-so-many records.

This approach gives you a rowcount (last row) and also direct access to any page.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • This was very well thought out, thank you I will try some of these things – jjxtra Mar 17 '11 at 08:16
  • 1
    What I ended up doing was using my Lucene Index for paging when the total number of results was smaller (less than 100K). When it was higher I used SQL Server. Sql server sucks at paging small results sets in huge tables. Lucene is great at paging smaller result sets in huge data sets. – jjxtra Feb 13 '12 at 18:32
  • Might as well mark this as correct as it's as good as one can get with just using SQL Server. The hybrid approach I mentioned above is working very well. – jjxtra May 25 '12 at 21:14
0

try this, let say you have country table as below:

DECLARE  @pageIndex INT=0;
DECLARE  @pageSize INT= 10;
DECLARE  @sortByColumn NVARCHAR(200)='Code';
DECLARE  @sortByDesc BIT=0;

;WITH tbl AS (
    SELECT COUNT(id) OVER() [RowTotal], c.Id, c.Code, c.Name
    FROM dbo.[Country] c
    ORDER BY 
       CASE WHEN @sortByColumn='Code' AND @sortByDesc=0 THEN c.Code END ASC,
       CASE WHEN @sortByColumn='Code' AND @sortByDesc<>0 THEN c.Code END DESC,
       CASE WHEN @sortByColumn='Name' AND @sortByDesc=0 THEN c.Name END ASC,
       CASE WHEN @sortByColumn='Name' AND @sortByDesc<>0 THEN c.Name END DESC,
       ,c.Name ASC  --DEFAULT SORTING ORDER
    OFFSET @PageIndex*@pageSize ROWS
    FETCH NEXT @pageSize ROWS ONLY
    ) SELECT (@PageIndex*@pageSize)+(ROW_NUMBER() OVER(ORDER BY Id))[RowNo],* from tbl;
Haseeb
  • 746
  • 7
  • 22