Sql Server Paging issue

Question

Friends,

I have already implemented paging in my SP -

with MyData As (
    select ROW_NUMBER() over (order by somecolumn desc) AS [Row], 
    x,y,z,...
)
Select x,y,z,...
From MyData
Where [Row] between ((@currentPage - 1) * @pageSize + 1) and (@currentPage*@pageSize)

The problem here is that data is retried very fast if with clause return smaller number of rows but it takes long time when there are millions of records. Sometimes it times out.

Is there any other alternative?

Thanks for sharing your valuable time.

Answer 1

The reason why your query is slow is that you have sort whole table on every request. To speed it up significantly you need to avoid sorting big chuck of data, at cost of CPU, HDD/Memory or limitations on pagination logic.

As there is not much information about how you table is sorted and if you insert in the middle / delete entries very often, I'll narrow down you question by making these assumptions:

1. I would imagine you have a table storing an archive of articles. New entries are mostly at the bottom of the table, entries from the middle of the table deleted rarely.

2. You sort always by the same column somecolumn and in the same order, e.g. descending.

3. You do not have any user entered filters (like article title or author).

This makes the table static in terms of the output: each article will be on the same place, unless a new one inserted. New one come to the top of your output. Then you can store ROW_NUMBER() OVER () as a column. A more convenient solution will be an IDENTITY column. It will speed up things if you create a clustered index on this column

alter table add [Record_Number] int null IDENTITY 

This new column is added as null so you can populate values first time. Then you can make it not null.

On the other hand you can last row number very quickly by

select @Max_Row =  SELECT MAX(row_number) from MyTable

Now when you have total number of rows, page size and page number you can select rows you need in one statement without sorting the whole lot.

Select * From MyTable 
Where row_number between 
    (@Max_Row - @Page * @Page_Size) + 1 AND
     @Max_Row -(@Page - 1) * @Page_Size

If you do have a filter in your CTE, then give some more information about how your data is structured, so we can think of a way to limit the scope of CTE.

Answer 2

SQL server optimisation is a very broad subject and it is pretty much impossible to work out the issue with the limited amount of information you have posted. However since you're in a rush for a solution - Firstly I would suggest checking your actual execution plan, post it here, and making sure that the index is actually being used - if this is not the case then consider using the FASTFIRSTROW table hint to force the index to be used - check here and here on how it can improve things and here in how it can make things worse.

Next to consider is SQL parameter sniffing - it's unlikely from what you have said but possible check here for details enter link description here

For large scale performance gains you may need to look at architectural changes at the very least ensure that your transaction logs are on a different disk to your data.The reason you separate the database files from the log files is because database access is random and log access is sequential. Best practice dictates that you don't mix those two I/O types on the same disk

Also if you've got million of rows then you really need to consider splitting the data across multiple disks.

Finally I would strongly consider partioning either the table or the index see here for a start