Paging through a recordset

[justsomeguy]

Has anyone figured out how to page through a recordset with ASP/VBScript using SQL Server 2000? I've got a query that is timing out on the database, so I need to have it return only a partial set of results and page through the entire result set. The LIMIT clause is exactly what I need, but SQL Server 2000 does not support LIMIT. I have seen a few techniques to try and use the TOP statment to get it to do something like this, but the query I have is so complex that I can't figure out how to map it to the examples they are showing for those techniques. Has anyone set anything up that lets you retrieve only part of the rows from a result set? For reference, here is one example of the query:

select   c.cid as cid,   c.title,   u.uid as uid,   u.fname,   u.lname,   s.start_date,   s.last_entered,   s.last_browser_used,   s.last_date_taken,   s.comment,   s.progress,   s.total_score,   s.completed,   s.visited,   s.aicc_lesson_status,   s.aicc_score,   s.aicc_time,   s.aicc_core_lesson,   s.counter,   c.launchcount,   s.instructor_uid,   s.remote_addr,   s.complete_date,   c.timetocomplete,   c.credits from (  users as u left outer join content as c   on c.cid in ( 	select cid	from user_content	where user_content.uid = u.uid	union	select cid	from group_content, group_users	where group_users.uid = u.uid and	  group_content.gid = group_users.gid	union	select cid	from usergroup_content, usergroup_users	where usergroup_users.uid = u.uid and	  usergroup_content.ugid = usergroup_users.ugid	union	select cid	from group_content, usergroup_groups, usergroup_users	where usergroup_users.uid = u.uid and	  usergroup_groups.ugid = usergroup_users.ugid and	  group_content.gid = usergroup_groups.gid  )  left outer join content_session as s  on (u.uid=s.uid and s.cid=c.cid) ) where 1=1 order by u.uid, c.cid

[Mr.Jay]

http://www.aspin.com/func/content?tree=asp...&id=4410410is a good little script

[justsomeguy]

That wouldn't work for this situation, it's not very efficient. It still returns the entire result set and just passes through it until it starts to write out rows. It also gets the data twice, the first time just to count through it all. I have a problem because if I try to select all half a million records from the database with the 2 joins and 4 unions, the database will just time out. I'm trying to look at the execution path to help optimize this, but the sorts and joins are killing the query when there are two sets of 200000 records or whatever to join.So returning all rows, and just displaying the ones you want, isn't going to work for this situation. I need a way to return only the rows I want, and a further problem is these rows come from joins, so there's not a primary key or something that I can use to help.

[Mr.Jay]

how about get rows ?http://www.asp101.com/samples/viewasp.asp?...=db_getrows.asp

[justsomeguy]

That may work, I'll look into it, but it worries me that it still retrieves all records. It might be possible to use a certain cursor type for the recordset that would not cause the entire result set to be returned, but only the rows I access. But I'm afraid that if I tell SQL Server to give me all of the rows, it will choke. It turns out that this application was designed (or not designed) in such a way that it puts a pretty strict limit on the max number of records, the way everything is organized it just doesn't scale well at all. I want to do a complete redesign, but I don't want customers to wait 3 months for a new product for a fix on a single report.I'll look into the getrows though, hopefully I can make it so that SQL Server will only return the rows I need.

[Mr.Jay]

Just found this linkhttp://www.4guysfromrolla.com/webtech/062899-1.shtmlIt was great reading it, i hope you find it as useful as i did

[justsomeguy]

Yeah, I had seen that, there's actually a more efficient way to do that described here:http://www.aspfaqs.com/webtech/042606-1.shtmlBut those really only work for a single table with an auto-increment primary key. My situation has several joins going on, so I wasn't able to use that. I ended up using something similar, and it does run quicker, but it's still timing out. I think a redesign is on the cards.

[jesh]

Are you able to use Table variables in SQL Server 2000 like you can in SQL Server 2005? If so, might it be faster to run multiple INSERTs into that table variable and then do the joins on it rather than the multiple sets of joins?

DECLARE @CIDs TABLE(	cid int)

[justsomeguy]

Yeah, you can use table variables. I've got this stored procedure right now which is selecting everything from a view:

CREATE PROCEDURE dbo.usp_test(  @startRow int,  @maxRows int)ASDECLARE @TempItems TABLE(  ID int IDENTITY,  cid nvarchar(8),  title nvarchar(100),  launchcount int,  credits int,  timetocomplete int,  uid nvarchar(50),  fname nvarchar(100),  lname nvarchar(100),  start_date smalldatetime,  last_entered smalldatetime,  last_browser_used nvarchar(100),  last_date_taken smalldatetime,  comment nvarchar(255),  progress int,  total_score int,  completed int,  visited int,  aicc_lesson_status nvarchar(255),  aicc_score nvarchar(255),  aicc_time nvarchar(255),  aicc_core_lesson nvarchar(255),  counter int,  instructor_uid nvarchar(50),  remote_addr nvarchar(15),  complete_date smalldatetime)DECLARE @maxRow intSET @maxRow = (@startRow + @maxRows) - 1SET ROWCOUNT @maxRowINSERT INTO @TempItems (cid, title, launchcount, credits, timetocomplete, uid, fname, lname,						start_date, last_entered, last_browser_used, last_date_taken, comment,						progress, total_score, completed, visited, aicc_lesson_status,						aicc_score, aicc_time, aicc_core_lesson, counter, instructor_uid,						remote_addr, complete_date)select cid, title, launchcount, credits, timetocomplete, uid, fname, lname,	   start_date, last_entered, last_browser_used, last_date_taken, comment,	   progress, total_score, completed, visited, aicc_lesson_status,	   aicc_score, aicc_time, aicc_core_lesson, counter, instructor_uid,	   remote_addr, complete_datefrom dbo.everythingSET ROWCOUNT @maxRowsSELECT * FROM @TempItems WHERE ID >= @startRowSET ROWCOUNT 0GO

And it does work pretty well, it doesn't select more rows then I need. The problem is that it still has to start at the beginning, so the end of the report (last page) still times out. That is, if I ask for 50 rows starting at row 1, it will only get the first 50 rows. If I ask for 50 rows starting at row 100, it gets the first 150 rows and then gives me the last 50 of those. So, it's good for paging if you can be reasonably sure that people will never hit the end (i.e. search results where people probably won't go past page 5), but for a report where people still need to see all the data it suffers from the same problem, the last page still returns all results and times out.I swear man, all I need is a little, tiny LIMIT statement. This is probably the strongest factor yet pushing this piece of software away from ASP/MS SQL and towards PHP/MySQL, and I'll be happy to do it right. Just need to find some time where I can afford to work on non-billable things.