Shulou(Shulou.com)05/31 Report--

SQLSERVER in how to achieve paging query, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

The first scheme, the simplest and most common method: copy the code as follows: SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN (SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC,ID DESC) ORDER BY YEAR DESC,ID DESC

Average time for 100 queries: 45s

The second scheme: copy the code as follows: SELECT * FROM (SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

Average time required for 100 queries: 138s

The third scheme: copy the code as follows: SELECT * FROM ARTICLE W1, (SELECT TOP 30 ID FROM (SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC) w ORDER BY w.YEAR ASC, w.ID ASC) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

Average time required for 100 queries: 21s

The fourth scheme: copy the code as follows: SELECT * FROM ARTICLE W1 WHERE ID in (SELECT top 30 ID FROM (SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC) w ORDER BY w.YEAR ASC, w.ID ASC) ORDER BY w1.YEAR DESC, w1.ID DESC

Average time required for 100 queries: 20s

The fifth scheme: copy the code as follows: SELECT w2.n, w1.* FROM ARTICLE w1, (SELECT TOP 50030 row_number () OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE) w2 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC

Average time required for 100 queries: 15s

Query the records of articles 1000-1030

The first scheme: copy the code as follows: SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN (SELECT TOP 1000 ID FROM ARTICLE ORDER BY YEAR DESC,ID DESC) ORDER BY YEAR DESC,ID DESC

Average time for 100 queries: 80s

The second scheme: copy the code as follows: SELECT * FROM (SELECT TOP 30 * FROM (SELECT TOP 1030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

Average time required for 100 queries: 30s

The third scheme: copy the code as follows: SELECT * FROM ARTICLE W1, (SELECT TOP 30 ID FROM (SELECT TOP 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC) w ORDER BY w.YEAR ASC, w.ID ASC) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

Average time required for 100 queries: 12s

The fourth scheme: copy the code as follows: SELECT * FROM ARTICLE W1 WHERE ID in (SELECT top 30 ID FROM (SELECT top 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC) w ORDER BY w.YEAR ASC, w.ID ASC) ORDER BY w1.YEAR DESC, w1.ID DESC

Average time required for 100 queries: 13s

The fifth scheme: copy the code as follows: SELECT w2.n, w1.* FROM ARTICLE w1, (SELECT TOP 1030 row_number () OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE) w2 WHERE w1.ID = w2.ID AND w2.n > 1000 ORDER BY w2.n ASC

The average query time: 14s shows that the efficiency is 3 > 4 > 5 > 2 > 1 when the query page number is at the front, and the page number is 5 > 4 > 3 > 1 > 2. According to the user's habit, the general user's search only looks at the first few pages, so you can choose 345 scheme. If the comprehensive consideration of scheme 5 is the best choice, but note that SQL2000 does not support the row_number () function, due to time and conditions constraints do not do more in-depth and extensive testing. If you are interested, you can study it carefully. The following is a paging stored procedure written according to the fourth scheme: copy the code as follows: if exists (select * from dbo.sysobjects where id = object_id (N' [dbo]. [sys_Page_v2]') and OBJECTPROPERTY (id, NumbIsProcedure') = 1) drop procedure [dbo]. [sys_Page_v2] GO CREATE PROCEDURE [dbo]. [sys_Page_v2] @ PCount int output,-- Total page output @ RCount int output -- output of total records @ sys_Table nvarchar,-- query table name @ sys_Key varchar (50),-- primary key @ sys_Fields nvarchar (500),-- query field @ sys_Where nvarchar (3000),-- query condition @ sys_Order nvarchar (100),-- sort field @ sys_Begin int,-- start position @ sys_PageIndex int -- current number of pages @ sys_PageSize int-- Page size AS SET NOCOUNT ON SET ANSI_WARNINGS ON IF @ sys_PageSize < 0 OR @ sys_PageIndex < 0 BEGIN RETURN END DECLARE @ new_where1 NVARCHAR (3000) DECLARE @ new_order1 NVARCHAR (4000) DECLARE @ new_order2 NVARCHAR (4000) DECLARE @ SqlCount NVARCHAR (4000) DECLARE @ Top int if (@ sys_Begin CEILING ((@ RCount+0.0) / @ sys_PageSize)-- if the current number of pages entered is greater than the actual total number of pages Then assign the actual total number of pages to the current number of pages BEGIN SET @ sys_PageIndex = CEILING ((@ RCount+0.0) / @ sys_PageSize) END set @ sql = 'select' + @ sys_fields + 'from' + @ sys_Table +'W1'+ 'where' + @ sys_Key +'in ('+ 'select top' + ltrim (str (@ sys_PageSize)) +'+ @ sys_Key + 'from' +'('+ 'select top' + ltrim (STR (@ sys)) _ PageSize * @ sys_PageIndex + @ sys_Begin) +''+ @ sys_Key + 'FROM' + @ sys_Table + @ new_where1 + @ new_order2 +') w'+ @ new_order1 +')'+ @ new_order2 print (@ sql) Exec (@ sql) GO

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.