SQL Server Data Paging

1. Introduction

In the list query due to the amount of data, one-time detection will be very slow, even if once detected, and can not be displayed to the client, so to the data in batches to query out, each page shows a certain amount of data, which is the data to be paged.

2. Common Data Paging methods

We often encounter to take N to M Records, is the idea of paging, the following list of general methods.

My local table tbl_flightsdetail, there are more than 300 W records, primary key Flightsdetailid (GUID), required to sort by flightsdetailid to take 3000001 to 3000010 between 10 records, is also millions.

Method 1 Positioning method (with ID greater than how much)

Statement form:

Select Top Ten * from Tbl_flightsdetail where flightsdetailid> (       select Max (flightsdetailid) from (               select Top 30 00000 Flightsdetailid from Tbl_flightsdetail order by Flightsdetailid       ) as T) Order by Flightsdetailid

Execution Plan:

First find top 300000, then aggregate the largest Id1 in this set, and then filter the collection of IDs greater than ID1 (used to index), and then take the top 10.

Method 2 (using not in)

Statement form:

Select Top 10* from Tbl_flightsdetail where Flightsdetailid not in (       select top 3000000 flightsdetailid from Tbl_fligh Tsdetail ORDER by Flightsdetailid) Order by Flightsdetailid

Execution Plan:

Similar to method one, except that the filter where condition is not the same, the use of the not in, the index is not used, time consuming 8 seconds. If Flightsdetailid is not an index, Method 1 and the method will be similar.

Method 3 (use a top upside down)

Statement form:

Select Top 10* from (       select top 3000010* from Tbl_flightsdetail order by flightsdetailid) as-t  order by T.flights Detailid desc

Execution Plan:

Take the first 3,000,010 records, then reverse, then take the front 10 is 300001 to 300,010 Records, no index, time spent 11 seconds

Method 4 (Row_number () function)

Statement form:

SELECT * FROM (       select *,row_number () Up (ORDER by Flightsdetailid) as rank from tbl_flightsdetail) as  t where T . Rank between 3000001 and 3000010

Execution Plan:

SQL 2005 version or above support, also useless to the index, time consuming 2 seconds, the speed is good.

Method 5 (using in)

This method is returned by the Golden Ocean (Jyk) Sunshine Boy , flying often thanks to the statement form:

Select Top * from Tbl_flightsdetail  where flightsdetailid in (        select Top Ten flightsdetailid from                P 3000010 Flightsdetailid from Tbl_flightsdetail order by Flightsdetailid       ) as-t order by t.flightsdetailid Desc) Ord ER by flightsdetailid

Execution Plan:

After several executions generally maintained in about 4 seconds, using the index, very good, the plan is very long, only the part of the interception, may be a bit more around.

3.Tens Paging stored procedures

Everyone Baidu this title will appear immediately a lot of relevant information, all the same, I copy one, should the project needs, modified a sort of bug and added the total number of records returned, as follows:

SET ansi_nulls ongoset quoted_identifier ongo--Paging stored procedure CREATE PROCEDURE [dbo]. [Sp_paging]            (@Tables nvarchar (1000),--table name/view name @primarykey nvarchar (100),--primary key @sort nvarchar ($) = NULL, --sort field (without order by) @pageindex int = 1,--current page @pagesize int = 10,--Number of records per page @fie lds nvarchar (+) = N ' * ',--output field @filter nvarchar (+) = NULL,--where filter condition (without where) @Group nvarchar (1000)  = NULL,--group statement (without GROUP by) @TotalCount int OUTPUT--Total number of records) as DECLARE @SortTable nvarchar (100) DECLARE @SortName nvarchar (2) DECLARE @type DECLARE (100) DECLARE @strSortColumn nvarchar (+) nvarchar @operator Char ) DECLARE @prec INT--Sets the sort statement if @Sort is NULL OR @Sort = ' SET @Sort = @PrimaryKey IF CHARINDEX (' DESC ', @Sort) &   Gt;0 BEGIN Set @strSortColumn = REPLACE (@Sort, ' DESC ', ') set @operator = ' <= ' END ELSE BEG In SET @strSortColumn = ReplacE (@Sort, ' ASC ', ') SET @operator = ' >= ' END IF CHARINDEX ('. ', @strSortColumn) > 0 BEGIN Set @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX ('. ', @strSortColumn)) Set @SortName = SUBSTRING (@str SortColumn, CHARINDEX ('. ', @strSortColumn) + 1, LEN (@strSortColumn)) END ELSE BEGIN SET @SortTable = @T        Ables Set @SortName = @strSortColumn END-Sets the sort field type and precision SELECT @type =t.name, @prec =c.prec from sysobjects o  Join syscolumns C on o.id=c.id join systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable and C.name = @SortName IF CHARINDEX (' char ', @type) > 0 SET @type = @type + ' (' + CAST (@prec as varchar) + ') ' DECL is @strPageSize nvarchar DECLARE @strStartRow nvarchar () DECLARE @strFilter nvarchar (+) DECLARE @ Strsimplefilter nvarchar (+) DECLARE @strGroup nvarchar () IF @pageindex <1 Set @pageindex = 1 set @strP Agesize = CAST (@PageSize as nvarchar (50)--Set Start paging record number Set @strStartRow = CAST (((@pageindex-1) * @PageSize + 1) as nvarchar (50))--Filter and group statement if @Filter is not NULL and @Filter! = ' BEGIN SET @strFilter = ' WHERE ' + @Filter + ' Set @strSimpleFilter = ' and ' + @  Filter + ' End ELSE BEGIN set @strSimpleFilter = ' Set @strFilter = ' End IF @Group is Not NULL and @Group! = ' SET @strGroup = ' Group by '-Calculates the total number of records declare @TotalCountSql nvarchar SET @TotalCountSq L=n ' SELECT @TotalCount =count (*) ' +n ' from ' + @Tables + @strFilterEXEC sp_executesql @TotalCountSql, N ' @TotalCount int outp UT ', @TotalCount output--executes the query statement EXEC (' DECLARE @SortColumn ' + @type + ' SET ROWCOUNT ' + @strStartRow + ' SELECT @SortColu Mn= ' + @strSortColumn + ' from ' + @Tables + @strFilter + "+ @strGroup + ' ORDER by ' + @Sort + ' SET ROWCOUNT ' + @strP Agesize + ' SELECT ' + @Fields + ' from ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimple Filter + ' + @strGroup + ' ORDER by ' + @Sort + ') 

Now let's test it out:

DECLARE    @return_value int,        @TotalCount intexec    @return_value = [dbo].[ Sp_paging]        @Tables = n ' tbl_flightsdetail ',        @PrimaryKey = n ' flightsdetailid ',        @Sort = n ' flightsdetailid ',        @pageindex = 299999,        @PageSize = ten,        @Fields = ' * ',        @Filter = null,        @Group = null,        @ TotalCount = @TotalCount outputselect    @TotalCount as N ' @TotalCount ' SELECT    ' Return Value ' = @return_value

Execution Plan:

See the time is really fast, execution plan shows 4 queries

Query 1, is the use of system tables to get sort fields, types and precision, this quickly, all index.

Query 2, return the total number of records, the first time will be slow, and soon after.

Query 3 and Query 4 (used to index) is the data we want to page, query 3 is sort, take a maximum value assigned to the variable, query 4 is greater than the value of this variable to take the data, directly look at the SQL statement, the above EXEC dynamic statement changed to the following:

DECLARE @SortColumn varchar (40)-that is, top 3000001, remove the largest ID covering @sortcolumn SET ROWCOUNT  3000001SELECT @SortColumn = Flightsdetailid from  tbl_flightsdetail ORDER by Flightsdetailid  --Top 10SET ROWCOUNT 10SELECT *  from Tbl_flightsdetail  WHERE flightsdetailid   >= @SortColumn  ORDER by  Flightsdetailid   

You will find that it is similar to the method 1 in the data paging method used in Heading 2, the original mystery is here.

4. Summary

There are also some performance differences with cursors and table variables that are not considered. The paging stored procedure looks quite complex, the statement many, actually all in the judgment, in the left Assembly, the right assembly, finally assembles resembles in the title 2 commonly used pagination method one kind of statement, grasps the commonly used data pagination method, everybody can write oneself, certainly also has other methods, everybody may share out.

Note: This article author Qlin, is I watch after feeling this article very practical, so take to collect, convenient later consult

SQL Server Data Paging