Comparison Experiment on three paging methods of SQL Server

Every system cannot avoid list pages, while list pages generally require pagination. Although all languages provide the internal paging function, the efficiency is very poor when there is a large amount of data, especially in the B/S architecture. There are two causes:
1. On the program language page, all related data in the database should be read each time, and only 10 or 20 pieces of data need to be displayed. A large amount of data is not used.
2. If the server or client provides data caching, it can be slightly optimized, but there is a very obvious phenomenon that none of the customers need to see so much data at a time, it may only need one or several pieces of data.

Therefore, programmers who have noticed performance optimization generally do not use the program language for paging. Paging tasks are transferred from the application server or client to the harsh database server, while the paging mode of the database server is generally implemented by SQL statements (stored procedures can be operated). Based on the previous experience of SQL Server, summary there are three types of paging SQL statements.

1. Use two select statements and two orders in double inverted order. For example, if you have 20 records of 10001-10020 in related records. You first sort a field, search for the first 10020 records, form a dataset, and then search for the first 20 records in reverse order. This method is relatively simple, but sometimes an inexplicable error occurs during multi-field sorting (maybe I am not experienced enough ).

2. Use two select statements and generate a temporary sequence number. For example, you have 20 records from 10001-10020 in the relevant records. First, you need to search for all the relevant data, add a new column to store the temporary sequence number of the record, form a dataset, and then add a temporary sequence number between 10001-10020 Based on the query dataset. This method must use a strange function to generate a temporary sequence number, which is only supported by SQL serrver 2005.

3. Create a temporary table and generate a temporary ID. For example, you have 20 records of-in related records. You can create a temporary table with only the auto-increment ID and the fields you need to search for the relevant records, and enter the primary keys of your search records into the temporary table. Query the temporary table and add the temporary sequence number of the condition to be in the range. Finally, delete the temporary table. This method is the most complex, but it has a large expandable space and has a strong potential for performance optimization.

The following are examples of the three methods:

Lab environment:
Intel (r) core (TM) 2 CPU 4300 @ 1.80 GHz 1.80 GHz 1.96g memory
Microsoft windwos XP Professional SP2
Microsoft SQL Server Management studio Express
There are also some messy software and services.

Database table test requires 12 fields, one auto-incrementing ID (Primary Key), one smallint type field, and 10 insignificant nchar (10) fields. There are 700000 records in total.

The SQL statement is as follows:

---------------- Two select statements plus two orders and two inverted orders (27 seconds)
Select top 20 * from (select top 349980 * from test where type> 5 order by type DESC) as temp order by type DESC

---------------- Two select statements generate a temporary sequence number (1 second)
Select * from (
Select *, row_number () over (order by type, ID) as rowrank from test where type> 5) as temp
Where rowrank between 350001 and 350020

---------------- Temporary table, create + insert + select + drop (3 seconds)
---------------- Temporary table, create + insert + select (2 seconds)
Create Table # pageindexforusers
(
Indexid int identity (0, 1) not null,
Id int
)
Insert into # pageindexforusers (ID)
Select ID
From Test
Where type> 5
Order by type, ID

Select T. * from test T, # pageindexforusers P
Where p. ID = T. ID and
P. indexid between 350000 and 350019

Drop table # pageindexforusers

The running result takes 27 seconds for the first method, 1 second for the second method, 2 seconds for the third method, and 3 seconds for the drop method. The following attachment is a database file. If you are interested, please download it.