Because of the paging method of the original system, false paging is used, and the efficiency is very low, and the database frequently reports Yellow Pages. Therefore, we are looking for appropriate paging Optimization Methods recently.
Original SQL model:
With records As (select row_number () over (order by xxx desc) as recordnumber, Recordcount = count (1) over (), ...... ...... ...... From Table1 Where condition)
Select * From records with (nolock) Where recordnumber between 1 and 15
Currently, we have found two effective optimization methods: one is to use this false paging method, but the select field in with is taken out of with, as shown below: False paging optimization method: With records As (select row_number () over (order by xxx desc) as recordnumber, Recordcount = count (1) over (), ID From Table1 Where condition)
Select......
...... ......
, Records .* From records with (nolock) Join Table1With (nolock) onTable1. Partid = records. partid
Where recordnumber between 1 and 15 In this way, because with is only a select index field, and wiht only selects all the fields for the 15 pieces of data required on one page, this can improve the database processing efficiency.
The other optimization method is thorough. The top method is used for real paging queries, which greatly improves the efficiency, as shown below: True paging optimization method: Select top page size *
From Table1
Where ID>
(
Select isnull (max (ID), 0)
From
(
Select top page size * (page-1) ID from Table1Where conditionOrder by ID
)
)
Order by ID But because this method cannot calculate the total number, Therefore, I made some improvements as follows:
Declare @ recordcount int;
Set @ recordcount = (select count (1) From Table1 where condition );
Select top page size *
From Table1
Where ID>
(
Select isnull (max (ID), 0)
From
(
Select top page size * (page-1) ID from Table1Where conditionOrder by ID
)
)
Order by ID
The advantages and disadvantages of the two optimization methods are as follows: False paging Optimization Advantage: When Select fields are large and many, especially those of the varchar (max) type, the performance is improved significantly and it is easy to compile in a standardized manner.
Disadvantage: when the data volume is too large, for example, when the data set is too large, there is still a timeout risk.
True paging Optimization Advantage: because it is a real paging method, the database processing efficiency is very fast and the efficiency is currently the highest. Disadvantages: When the logic is complex, the statement is too cumbersome, which is not conducive to standardized writing; and when the table association structure is too complex and there is no unified keyword segment, it is more difficult to write. In order to implement the multi-Keyword problem, improvements are made as follows:
Declare @ recordcount int;
Set @ recordcount = (select count (1) From Table1 JoinTable2 on 1.id= 2. tb_id Where condition );
Select top page size * , @ Recordcoun as recordcoun
From Table1 JoinTable2 on1. ID =2.Tb_id
WhereNot exists (select 1
From
(
Select top page size * (page number-1) Table1. ID as tb1, Table 2. ID as tb2
From Table1 JoinTable2 on 1.id= 2. tb_id Where condition Order by 1.id, 2.id
) Where a. tb1 = 1.id and A. tb2 = 2.id
)
Order by 1.id, 2.id
