SQL Tuning Learning--sqlserver paging from inefficient to efficient

Background

MySQL and Oracle were used before, and there was not much use for SQL Server. Recently, due to project reasons, to read the database of other projects, take out a gate of the switch history, and the other side of the use of SQL Server, so the study of SQL Server paging, after several practices, slowly from the inefficient paging to the efficient paging.

Table structure

History table

Historical record Id:id (single cable)

Operating Hours: Time

Open or Close: Flag

WHO operates: user_id

Which device to belong to: device_id

Here we first introduce four methods of paging, with "one inefficient"-"two more efficient"-"an efficient" sequential introduction, at the end of the test results are attached.

Low-efficiency SQL

Ideas:

innermost : First 50,010 records from history table based on time reverse

Middle Layer : From the above query results according to the time sequence to detect the first 10 records, a positive and a reverse just took out the 10,000th to 10,010 Records.

outermost : Take out the above query results in reverse chronological time

SQL code:

Select *  from (    Select Top Ten *  from     (        Select Top 50010 *  from HistoryOrder  by Timedesc) HOrder  byH.timeASC) HHOrder  byHh.timedesc

By the following test, this query efficiency is relatively low.

The reason is because each layer of the query uses select *, that is, scan all of this paragraph, but "innermost" and "middle layer" there is no need to select *, these two layers are just to locate the last layer of the search scope between 第10000-10010条, so, in these two layers, We just have to take out the keyword ID and sort the fields in time.

more efficient SQL (1) ———— use where ... =

innermost : First 50,010 records are detected from the history table according to the reverse of the time, only take out the ID and timing

Middle Layer : From the above query results according to the sequence of time to detect the first 10 records, only take out the ID and timing, a positive and a reverse just took out the 10,000th to 10,010 Records.

outermost : According to the time reverse of the above query results,SELECT * Take out all fields, query scope with where ... = ... to match.

SQL code:

Select *  fromHistory hh, (Select Top TenID, time from      (         Select Top 50010ID, time from HistoryOrder  by Timedesc) HOrder  byH.timeASC) HHHwhereHhh.id=hh.idOrder  byHhh.timedesc

After testing, this paging method is a little bit faster than the previous one.

The main reason in the "innermost" and "middle tier" of the two queries, all just identify the ID and time, rather than select *, from this point of view to improve efficiency. In the end, the where...= syntax is used, which reduces the efficiency of the previous page paging method. But the where...= syntax is fast, so the paging method is faster in general.

More efficient paging (2) ———— using where...in

innermost : First 50,010 records are detected from the history table according to the reverse of the time, only take out the ID and timing

Middle Layer : From the above query results according to the sequence of time to detect the first 10 records, only take out the ID andtiming, a positive and a reverse just took out the 10,000th to 10,010 Records.

outermost : According to the time reverse of the above query results,SELECT * Take out all the fields, query range with where...in () to match

SQL code:

Select *  fromHistory hhwhereIdinch (     Select Top TenId from      (         Select Top 50010ID, time from HistoryOrder  by Timedesc) HOrder  byH.timeASC )Order  byHh.time des

This paging method, compared with the previous paging method, is the difference between this use of where...in (), rather than where...=, the principle is not very different, but it may be the reason for the internal optimization of SQL Server, using where...in is faster than using where...=. Detailed in the following table of calculation can be seen.

Efficient paging ———— using row_number () over

innermost : Query out the first 50,010 data, only take out the ID field, while using the row_number () over syntax, add an n field, representing the data when the first few lines .

outermost : match the ID according to Where...=, and take out the n>50000 data directly.

SQL code:

SelectHHH.N, HH.*  fromHistory hh, (Select Top 50010Row_number () Over      (         Order  by Timedesc) N,id fromHistory ) HHHwhereHhh.id=hh.id andHhh.n> 50000 Order  byHHH.Ndesc

This paging method, which is only two queries at first, increases efficiency. The innermost query out of the "virtual column"--n,sqlserver do not know if it will be indexed, personally think, but can not think of any way to verify. If there is an index, then in this place, the use of "n > A number" Query method, and a little faster than the previous queries.

The speed comparison of several paging methods

The following two Tests, the first is to query out 1000-1010 data, the second is to query out 第50000-50010条 data. The number of seconds recorded is 50 times the total time of the query, each group of tests 5 times, and finally averaged.

5.253s td>5.835s

" query 1000-1010 Data "	first time	second	third	fourth	fifth time	average
Inefficient paging	6.344s	5.687s	5.797s	5.704s	5.641s
More efficient paging (1)--where...=	4.485s	5.281s	5.094s	5.281s	5.313s	5.091s (Win)
more efficient Paging (1)--where...in	5.093s	5.328s	5.14s	5.406s	5.297s
Efficient Paging--row_number () over	5.437s	5.39s	5.156s	5.016s	5.344s	5.269

As you can see, using "Higher paging (1)-where...=" is the fastest way to page out when the number of rows in a query is small .

" query 50000-500 10 data "	first	second	third	fourth time	fifth time	average
Inefficient paging	10.844s	9.985s	10.172s	10.0s	10.297s	10.260s
More efficient paging (1)--where...=	9.625s	9.469s	9.14s	9.171s	9.219s	9.325s
More efficient paging (1)--where...in	9.156s< /td>	9.61s	9.187s	9.218s	9.219s	9.278s
High Effect Paging--row_number () over	7.844s	6.765s	6.422s	7.359s	6.875s	7.053s (WINS)

As can be seen, when the number of rows queried , the use of "efficient paging-row_number ()over" is the fastest one of a paging method, and quickly several grades !

SQL Tuning Learning--sqlserver paging from inefficient to efficient