Tag: End data condition use HTTP datetime and image--
1. Create a test environment, (inserting 1 million data takes about 5 minutes).
Create DATABASE DBTest
Use DBTest
--Create a test table
CREATE TABLE Pagetest
(
ID int identity (a) is not NULL,
COL01 int NULL,
COL02 nvarchar () null,
COL03 datetime NULL
)
--1 Record Set
DECLARE @i int
Set @i=0
while (@i<10000)
Begin
Insert INTO Pagetest Select CAST (Floor (rand () *10000) as int), Left (newid (), ten), GETDATE ()
Set @[email protected]+1
End
2. Several typical paging SQL, the following example is 50 per page, 198*50=9900, take the 199th page of data.
--Writing 1,not In/top
Select Top * from Pagetest
where ID not in (the select top 9900 ID from the pagetest ORDER by ID)
ORDER BY ID
--Writing 2,not exists
Select Top * from Pagetest
Where NOT EXISTS
(select 1 from (select top 9900 ID from Pagetest ORDER by id) a where a.id=pagetest.id)
ORDER BY ID
--Notation 3,max/top
Select Top * from Pagetest
Where id> (select Max (ID) from (the Select top 9900 ID from the pagetest ORDER by ID) a)
ORDER BY ID
--Notation 4,row_number ()
Select Top * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where rownumber>9900
SELECT * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where rownumber>9900 and rownumber<9951
SELECT * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where RowNumber between 9901 and 9950
---5, in the csdn on a post to see, Row_number () variant, not based on the existing field generated record ordinal, first by criteria filtering and sequencing, and then on the result set to a constant column to generate the record sequence number
SELECT *
From (
Select Row_number () over (order by Tempcolumn) rownumber,*
From (select Top 9950 tempcolumn=0,* from Pagetest where 1=1 order by ID) a
) b
where rownumber>9900
2. Test under 10,000, 100,000 (take 1990 pages), 100 (take 19900 pages) record set.
Test sql:
DECLARE @begin_date datetime
DECLARE @end_date datetime
Select @begin_date = getdate ()
< YOUR code.....>
Select @end_date = getdate ()
Select DateDiff (MS, @begin_date, @end_date) as ' milliseconds '
10,000: The basic feeling is not different.
100,000:
1 million:
Conclusion:
1.max/top,row_number () is a good paging method. Compared to Row_number () only supports sql2005 and above, Max/top has better portability and can be used for sql2000,access.
2.not exists feeling is a little more efficient than not.
3.row_number () 3 different ways of writing efficiency looks similar.
The variant of 4.row_number () is based on the fact that my test efficiency is not good. The original posts here http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html
PS. The page sort above is based on the self-increment field ID. The test environment also provides a int,nvarchar,datetime type field, or you can try it. However, the efficiency of large data sequencing that is not indexed for non-primary keys should be very unsatisfactory.
Comparison of the efficiency of several common SQL paging methods