Top and Row_number

The problem of paging was studied in the previous period, because the database belonged to millions, and the problem of optimization was considered. One of the considerations is that the first page is definitely the highest frequency, so I want to use top N to read the first page.

The idea itself is not wrong, because usually I read n records under a certain condition I always use top n. After the top N and paged read the first article for efficiency comparison, found that the efficiency of paging is actually higher, the following is the test code:

use [D_study]; Goset STATISTICS IO on; SET NOCOUNT on; Godeclare @BeginTime datetime;declare @EndTime datetime;declare @ExecTime int;declare @ExecNum int; SET @ExecNum = 1; SET @ExecTime = 0;--Test top reads the execution time of the first page
While @ExecNum <= BEGIN SET @BeginTime = getdate ();        SELECT TOP * from the users WHERE nid>2000 and nid<50000 ORDER by NID DESC;        SET @EndTime = getdate ();        SET @ExecTime = @ExecTime + DateDiff (MS, @BeginTime, @EndTime);        SET @ExecNum = @ExecNum + 1;  CHECKPOINT; /* Write dirty buffer into disk */DBCC freeproccache with NO_INFOMSGS; /* Clear Execution Plan */DBCC dropcleanbuffers with NO_INFOMSGS; /* Clear Buffered Data */endprint ' top average execution speed: ' + Cast ((@ExecTime/30) as varchar (10)) + ' millisecond ';--Test paging read the first page execution time set @ExecNum =   1;  --Reset the number of executions set @ExecTime = 0;                --Reset recording time while @ExecNum <= BEGIN Set @BeginTime = getdate ();         SELECT * FROM (select Row_number () over (ORDER by NID ASC) as rownum,* from users Where nid>2000 and nid<50000) as D        WHERE rownum>0 and rownum<31;        Set @EndTime = getdate ();        SET @ExecTime = @ExecTime + DateDiff (MS, @BeginTime, @EndTime);        SET @ExecNum = @ExecNum + 1;  CHECKPOINT; --Write dirty buffer into disk DBCC freeproccache with no_infomsgs; --Clear execution plan DBCC dropcleanbuffers with NO_INFOMSGS; --Clear buffered data endprint ' pagination is similar to the top effect: ' + Cast ((@ExecTime/30) as varchar (10)) + ' millisecond '; Goset NOCOUNT OFFSET STATISTICS IO OFF

Modifying the number of records read N and modifying the range values of read conditions is still more efficient paging.

See a lot of resources and do a variety of tracking, have not found a satisfactory answer.

Finally I analyzed the two SQL, the difference is that "extract N records" of this operation, Top N and WHERE rownum>0 and rownum<31.

I don't have a theory about how they work, but I can make an analogy:

Sports teacher let us run 30 step distance, Top N is the practice is to run 30 steps, their own side of the number of runs; where rownum>0 and rownum<31 equivalent to the teacher in 30 steps of the location of a mark, you just die, to the mark is equivalent to run 30 steps. I want to make a mark dead run this should be quick,:)

Category: SQL

Top and Row_number