Paging 10m+ records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Paging 10m+ records

Author

Topic

LiamW
Starting Member

5 Posts

Posted - 2008-02-19 : 09:29:54

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

30421 Posts

Posted - 2008-02-19 : 09:31:34

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

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

LiamW
Starting Member

5 Posts

Posted - 2008-02-19 : 09:55:49

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,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 10:08:18

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"

LiamW
Starting Member

5 Posts

Posted - 2008-02-19 : 10:39:03

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

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-19 : 13:22:28

Use a CTE!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 15:02:11

Please enlighten us of the superiority of a CTE in this case.

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

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-19 : 15:50:17

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 03:55:46

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 05:10:49

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"

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-20 : 13:23:24

Peso,

Try using the TOP inside the CTE query also....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-20 : 13:39:18

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

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-20 : 14:34:05

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 16:14:31

Maybe it is a new internet dating site?

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-20 : 16:26:24

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources