SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Paging 10m+ records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LiamW
Starting Member

5 Posts

Posted - 02/19/2008 :  09:29:54  Show Profile  Reply with Quote
Are there any suitable methods for paging the rows in a table of this size.

Any of the pagination techniques I have tried to far take too long to return the results.

Any advice, pointers, where to start??

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/19/2008 :  09:31:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Should end-users be able to sort/paginate for ALL columns?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

LiamW
Starting Member

5 Posts

Posted - 02/19/2008 :  09:55:49  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Should end-users be able to sort/paginate for ALL columns?



E 12°55'05.25"
N 56°04'39.16"




Yeah, they have the choice to filter the results by the following:

Points [ASC/DESC]
Date Added[(ASC/DESC]
Date Added Range [ASC/DESC]
Tag (varchar field)
Source (varchar field)

The easiest solution would be to limit the number of rows returned using SET ROWCOUNT. I've tried this and it is reasonable quick. Lets face it, no-one is going to trawl through a list of 10m records to find something. However it would be nice to give the user those capabilities.

Are there dangers associated with using ROWCOUNT (loss of information returned etc?)

Cheers,
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/19/2008 :  10:08:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try something similar to this
CREATE PROCEDURE dbo.uspPaginate
(
	@PageNumber INT,
	@RecordsPerPage TINYINT = 50
)
AS

SET NOCOUNT ON

DECLARE @MaxRows INT

SET @MaxRows = @PageNumber * @RecordsPerPage

SELECT		SomeColumns
FROM		(
			SELECT		TOP (@RecordsPerPage)
					SomeColumns
			FROM		(
						SELECT		TOP (@MaxRows)
								SomeColumns
						FROM		YourTable
						ORDER BY	SomeCase ASC/DESC
					)
			ORDER BY	SomeCase DESC/ASC
		)
ORDER BY	SomeCase ASC/DESC



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

LiamW
Starting Member

5 Posts

Posted - 02/19/2008 :  10:39:03  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Try something similar to this
CREATE PROCEDURE dbo.uspPaginate
(
	@PageNumber INT,
	@RecordsPerPage TINYINT = 50
)
AS

SET NOCOUNT ON

DECLARE @MaxRows INT

SET @MaxRows = @PageNumber * @RecordsPerPage

SELECT		SomeColumns
FROM		(
			SELECT		TOP (@RecordsPerPage)
					SomeColumns
			FROM		(
						SELECT		TOP (@MaxRows)
								SomeColumns
						FROM		YourTable
						ORDER BY	SomeCase ASC/DESC
					)
			ORDER BY	SomeCase DESC/ASC
		)
ORDER BY	SomeCase ASC/DESC



E 12°55'05.25"
N 56°04'39.16"




I'll give it a go and let you know how it performs.

Thanks
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 02/19/2008 :  13:22:28  Show Profile  Reply with Quote
Use a CTE!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/19/2008 :  15:02:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Please enlighten us of the superiority of a CTE in this case.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 02/19/2008 :  15:50:17  Show Profile  Reply with Quote
Peso, I'm not expert, but it seems to be a cleaner approach.

I've seen people use temp tables and CTE's for paging, this sub query method is not very legible.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/20/2008 :  03:55:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Create a table with 10 million records.
I am interested to see a test with different approaches.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/20/2008 :  05:10:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Some initial testing have shown me that it takes about 2 seconds on my laptop with SQL Server Express to get page 20000 (pagesize 50) from a 3012154 record table with the suggestion posted 02/19/2008 : 10:08:18.

It takes about 2.2 seconds (same reads 2116) with
select dt from (
select	DT,
	ROW_number() over (order by dt) as recid
from dates
) as d
where recid between 999951 AND 1000000


Now this is not a complete test, since the table is only one column, containing all dates between 17530101 and 99991231 (clustered).


SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

(50 row(s) affected)
Table 'Dates'. Scan count 1, logical reads 2116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 1953 ms,  elapsed time = 3431 ms.

(50 row(s) affected)
Table 'Dates'. Scan count 1, logical reads 6352, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 2250 ms,  elapsed time = 6514 ms.


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/20/2008 05:24:00
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 02/20/2008 :  13:23:24  Show Profile  Reply with Quote
Peso,

Try using the TOP inside the CTE query also....
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/20/2008 :  13:39:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
paging 10 million records is a complete and utter nonsense.
you should narrow your criteria to make the result set smaller.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/20/2008 :  14:34:05  Show Profile  Reply with Quote
Think needle and haystack

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/20/2008 :  16:14:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe it is a new internet dating site?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/20/2008 :  16:26:24  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by spirit1

paging 10 million records is a complete and utter nonsense.
you should narrow your criteria to make the result set smaller.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out


I strongly second that!

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000