Optimization of one paging SQL query (based on SQL Server)

 

 

 

Assume that student has the following fields:

 

ID: The clustered index is created.

Name: corresponding name

Gender: Gender

 

There is a stored procedure that requires paging to retrieve students of a certain gender and perform paging.

 

 

The paging SQL statement in it is like this. Assume that we want to retrieve the data of 100 and 20th pages per page sorted by ID.

 

 

 

Select top 20 * from student

Where where gender = 'male' and not exists (

Select top 1900 ID from student

Where gender = 'male'

Order by ID)

Order by ID

Because it is sorted by ID, it is equivalent to knowing the largest ID of the first 1900, and then the select of the outer layer is not less than the ID of the first 1900,

 

Therefore, you can write:

 

Select top 20 * from student

Where where gender = 'male' and ID> (

Select max (ID) from (

Select top 1900 ID from student

Where gender = 'male'

Order by ID ))

Order by ID

 

Performance will be greatly improved.