How to optimize the pagination query of multiple table joins

Question

I use a translator to communicate with you. If anything I say is incorrect, please forgive me.

I am faced with the task of optimizing the pagination query of many views and TVFs on three database versions: 2008R2/2012/2016. The query uses ROW_NUMBER to page query.The specific SQL is as follows:

select *  from (select *,Rowno= ROW_NUMBER() OVER(order by 序号 desc) from ConcreateDetectTrust_v 				)as TempTable   				where Rowno between 0 and 2000

ROW_NUMBER.XML

These views and TVFs often query dozens or even more than two dozen tables, ranging from tens of thousands to hundreds of thousands of rows.Usually, they do not have notable where conditions, as the current where conditions cannot narrow down the data range of the tables to be searched.

I have made two operations to address this issue. First, I updated the statistics information for the involved tables and created an index to cover the large table. Second, I replaced ROW_NUMBER with offset fetch in the 2012+ version, as shown in the following SQL statement:

select  * from ConcreateDetectTrust_v  where  1=1 order by 序号 desc OFFSET 0 ROWS FETCH NEXT 2000 ROWS ONLY

offset.XML

Now we are facing two problems

1. Updating statistical information and building covering indexes alone do not effectively reduce query time (as expected), so are there any other optimization methods?
2. When the output row count is small, such as querying 0-100 rows or 0-1000 rows, the time difference between offset fetch and ROW_NUMBER is similar.However, as the output row count gradually increases, offset fetch becomes slower than ROW_NUMBER.If the increased row count is large enough to trigger parallelism, the slowdown compared to ROW_NUMBER will be significantly exacerbated.This phenomenon is contrary to the conclusion that offset fetch performs better than ROW_NUMBER on paged queries that I have heard.So does this mean that offset fetch is not necessarily better than ROW_NUMBER in terms of performance?

Thank you for reading and helping.

SQLPLAN is a test export of my local server, so the database version will be higher.

Answer 1

I did not check the plans, since you say that have you have many of these. Each is its own tuning exercise.

But some general ideas. Even if you only present, say, 20 rows at a time, read 1000 at time from SQL Server, so when the user press next, there is no need to go to SQL Server again. That would only happen if the user gets beyond row 1000.

You could also consider materialising the search result in a table and serve the user from that table. You would then have to track when a search result is no longer of interest so you can delete it.

As for row_number vs OFFSET, I think this can vary from query to query. I'm not a fan of paging, so I have done a whole of testing myself.