On the list page, the programmer used the "Wuqi. Webdiyer" Paging plug-in, which was slow and could not be found. After reading the code, I thought there was too much redundancy, so I decided to optimize it.
On the list page, the programmer used the "Wuqi. Webdiyer" Paging plug-in, which was slow and could not be found. After reading the code, I thought there was too much redundancy, so I decided to optimize it.
After looking for some information from the Internet, consider directly at the bottom of the SQL, directly retrieve the data on the current page, and then bind it, which should be more efficient.
The core SQL query statement is as follows:
The Code is as follows: |
|
Select top @ size * from (@ sqlstring) Where @ key <= (select min (@ key) from (select top @ star @ key from (@ sqlstring) a order by @ key desc)) And @ key> = (select min (@ key) from (select top @ end @ key from (@ sqlstring) a order by @ key desc)) Order by @ key desc
|
Where:
@ Size: The quantity of one page;
@ Sqlstring: the original query statement;
@ Key: key field/sorting field, which should be a unique field;
@ Star: Index of the number of records starting, star = size * (p-1) + 1;
@ End: Index of the number of ending records, end = size * p + 1;
If the sorting is different, the code should be changed accordingly:
The Code is as follows: |
|
Select top @ size * from (@ sqlstring) Where @ key> = (select max (@ key) from (select top @ star @ key from (@ sqlstring) a order by @ key asc)) And @ key <= (select max (@ key) from (select top @ end @ key from (@ sqlstring) a order by @ key asc)) Order by @ key asc
|
In this way, I construct a method to retrieve the dataset, pass the relevant parameters in, splice the query, and bind the returned dataset.
No error is found in this solution, and the efficiency is also improved.