Fastest SQL 2005 select query for ASP .NET table paging ?

Question

What is the fastest way to select a range of rows, let's say from 4.200.000 to 4.200.050, using SQL 2005? Suppose that I have 10 millions of rows.

On my own projects I use the following approach, but I'm not sure if this is the best practice

select * from
(
 select 
  Column1, Column2, Column3
  RowNumber = row_number() over (order by ID asc) 
 from 
  tblLogs
 where
  Column4 = @Column4 and Column5 = @Column5
    ) as tempTable
where tempTable.RowNumber >= @StartIndex and tempTable.RowNumber <= @EndIndex

With the code above I am tempted to say that tempTable will be a big table with one column containing all my IDs.

Is there anything faster ?

Don't think to make some workarounds using the ID column, this won't work, I delete rows from that table, so my IDs are not successive numbers.

Answer 1

if you are paging, you can pass in the first & last key for the current page, and limit your derived "tempTable" using those to make it return fewer rows and thus faster.

Answer 2

This article over at SQLServerCentral is excellent:
SQL Server 2005 Paging – The Holy Grail

Answer 3

I noticed that you have a lot of rows, adding indexes on Column4 and Column5 would increases performances dramatically if not already added.

I found the following article interesting: Ranking Functions and Performance in SQL Server 2005

I will let you figure out how to improved it according to the article if possible. I tested their solutions myself and it works.

If you're looking forward paging in ASP.NET, I also found the following article by Scott Mitchell very interesting: Custom Paging in ASP.NET 2.0 with SQL Server 2005

It used their method in my code and it works just great. Here is a sample of TSQL code:

    SELECT ROWNUM, COLUMN1, COLUMN2, COLUMN3
    FROM (
 SELECT COLUMN1, COLUMN2, COLUMN3,
 ROW_NUMBER() OVER(ORDER BY ID) AS ROWNUM
            FROM TABLE1
 WHERE COLUMN4 = @X AND COLUMN5 = @Y
    ) AS TABLE2
WHERE ROWNUM BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

I suggest reading the 4guysfromrolla's article for further information.

Good Luck

Answer 4

Ok. This is my final thought about this problem.

For big projects, with tables containing 10 millions of rows or more, I will use this approach:

  select * from
  (
        select 
     myTable.*, 
     RowNumber = row_number() over (order by myTable.ID asc) 
    from 
     myTable
        where
                myCondition
  ) as tempTable
  where tempTable.RowNumber >= @StartIndex and tempTable.RowNumber <= @EndIndex

• for ASP .NET paging I'll use the SELECT below, wich works very fast for first 100.000 rows, 10.000 pages with 10 rows / page, but from page 10.000 to Infinity the query will work slower and slower, to very slower. No one will want to browse the page 10.001 !!

• For counting the number of pages and number of rows that fulfill myCondition from the SELECT above, I'll make a special TABLE that will have only one row and one column, on this column I will store the number of rows. Every time I add, modify or delete a row from myTable I will update this colon based on myCondition by adding or decreasing it with value 1. The purpose of making this is to fast select the number of rows that fulfill myCondition and show to my users how many pages I have.