Paging storage process of millions of data in milliseconds [reprinted]

Data paging has always been a persistent topic. I have seen more than N masters in the garden, which are better than me,

Today, I used pagination.CodeContinue to share; please show your mercy and make little bricks... haha;

Thank you for reprinting !!!!

First, refer to the master method in the garden to generate a million data table, which takes 33 seconds;

Create a paging storage process. This is based on row_number of SQL 2005, and SQL 2000 is not applicable. I have not installed SQL 2008, so I have not tested it! It is estimated that it can be run.

2 typesAlgorithmYou can select 1. Use between; 2. Top; the Code has been commented out.

I tested the two methods. The Code is as follows:

 

Use [data_temp] Go/***** object: storedprocedure [DBO]. [im531_page] script Date: 11/26/2010 10:52:35 ******/set ansi_nulls ongoset quoted_identifier ongo -- ================ =================================-- Author: im531 -- create Date: 2010-10-25 -- Description: SQL 2005 row_number paging -- modify Date: -- =================================================== ======= create procedure [DBO]. [im531_page] @ tabename as nvarchar (50), @ fields as nvarchar (1024), @ searchwhere as nvarchar (1024), @ orderfields as nvarchar (1024), @ pagenumber as int, @ page as intasbeginset nocount on; declare @ sqltype as tinyintset @ sqltype = 0 -- 0 between 1 topdeclare @ SQL as nvarchar (max) if @ searchwhere <> ''set @ searchwhere = 'where' + @ searchwhereif @ page <2 set @ page = 1if @ page = 1 beginset @ SQL = 'select top' + convert (nvarchar (20 ), @ pagenumber) + ''+ @ fields + 'from [' + @ tabename + '] [a] With (nolock) '+ @ searchwhere + 'ORDER BY' + @ orderfieldsendelsebegin -- temporary table set @ SQL ='; with [page _____ table] As ('+ 'select row_number () over (order by '+ @ orderfields +') as [rownow], '+ @ fields +' from ['+ @ tabename +'] [a] With (nolock) '+ @ searchwhere + ') '-- find the current page record if @ sqltype = 0begin -- a use betweenset @ SQL = @ SQL + 'select *' + 'from [page _____ table] With (nolock) '+ 'where [rownow] between' + convert (nvarchar (20), (@ page-1) * @ pagenumber + 1) + 'and' + convert (nvarchar (20), @ page * @ pagenumber) + 'order by [rownow] 'endelsebegin -- B use topset @ SQL = @ SQL + 'select top' + convert (nvarchar (20), @ pagenumber) + '*' + 'from [page _____ table] With (nolock)' + 'where [rownow]> '+ convert (nvarchar (20), (@ page-1) * @ pagenumber) + 'order by [rownow] 'endendexec (@ SQL) End

 

 

Then test the paging performance. Each page displays 20 records, with a maximum of 50000 million data pages. The current page uses a random number;

 

 
Declare @ d datetimeset @ d = getdate () Declare @ M as int; declare @ n as int; declare @ cpage as intset @ M = 50000; set @ n = 1 Set @ cpage = cast (RAND () * (@ M-@ n) + @ n) as INT) execute [im531_page] @ tabename = 'table _ 1', @ fields = '[a], [B]', @ searchwhere = '[a]> 0 ', @ orderfields = '[a] DESC', @ pagenumber = 20, @ page = @ cpageselect [current page] = @ cpage, [paging execution time (MS)] = datediff (MS, @ D, getdate ())
 

 

Tests on my machine are in milliseconds !!!!

If you have a better method, please share it !!!!!!!!!!!!!!!

--------------------------------------- I am a gorgeous split line ---------------------------------------

Reply to yuanyou on the 10th floor★Gold★:

After the statistical results are added, it is a little slower. It is estimated that the actual usage is negligible;