Also say SQL Server paging query

xiaoxiao2021-03-06  85

It is now generally used to have the following two methods:

1. Select Top @Pagesize * from table1 where id not in (select top @pagesize * (@ Page-1) ID from table1 order by id) Order by ID

2. Select * from (select top @Pagesize * from (select top @ PageSize * @ page * from table1 order by id) a order by id desc) b Order by ID

Which way is better? I tried it.

Do two tables, each 10,000 records, a table ID has Index, one is not

Figure 1, there is no INDEX Table, take the 1000-1100 records

Figure 2, there is no INDEX Table, take the 9000-9100 records

Figure 3, INDEX, take 1000-1100 records

Figure 4, INDEX, take the 9000-9100 record

It can be found that the SORT field has established index conditions, and the first method is very fast, especially when checking the number of back pages. The reason is in the second method, after the first time SELECT, do the self-inquiry When INDEX is already. Sort will very much effect.

转载请注明原文地址:https://www.9cbs.com/read-122688.html

New Post(0)