Paging through records on HUGE table, anyone have a creative solution? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Paging through records on HUGE table, anyone have a creative solution?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-03 : 07:35:04

Boppo writes "Hello all,
I'm trying to write a stored proc for use on an asp page that I pass, for example, a @PageNumber and a @RecordCount. The proc is for a page that displays a certain number of rows from a history table (@RecordCount). I have a solution that works, the same solution that I've always used for record paging, but because of the size of the table I'm hitting its a little slow, and going to get slower.

This is an interface for a workflow system, and a busy day could mean an additional 10,000 rows to the recordset inserted to the temp table. Besides archiving / truncating my log, does anyone have any solutions that stray from the standard "drop everything to a temp table and select out based on an identity" solution? I'll probably end up truncating my log, but thought this was a fun applied theories problem. :-)

Here's what I'm doing:

alter proc gsp_GetPage
@PageNumber int,
@RecordCount int

as
begin

--begin: declarations
SET NOCOUNT ON

declare @FirstRec int,
@LastRec int

create table #temp (
id int IDENTITY,
JobName varchar(200),
ReportID int,
ReportName varchar(200),
CreatedByID int,
EmployeeName varchar(200),
CreationDate datetime,
Complete bit
)

--drop everything into temp table
insert into #temp (JobName, ReportID, ReportName, CreatedByID, EmployeeName, CreationDate, Complete)
select j.JobName,
r.ReportID,
r.ReportName,
r.CreatedByID,
e.EmployeeName,
r.CreationDate,
r.Complete
from report r
inner join jobs j
on j.JobID = r.JobID
inner join employees e
on e.NetworkID = r.CreatedBy
order by CreationDate desc

--determine where to start and stop
select @FirstRec = (@PageNumber - 1) * @RecordCount
select @LastRec = (@PageNumber * @RecordCount + 1)

--end: return recordset / cleanup
select *
from #temp
where id > @FirstRec and id < @LastRec

drop table #temp

end"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-03 : 07:36:29

Take a look here:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=27297

Also read the links inside that one.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-07-03 : 14:10:24

Don't I recall something about being able to set Indexes on temporary tables?

Would that speed up the paging?

Aj

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-03 : 18:29:55

It can, if you index it properly. No way to know for sure except to test it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources