asked on

paging records through a stored procedure

I'm currently attempting to implement paging on my site without loading all of the records into memory, and then setting the cursor, but rather directly through a stored procedure using the ROWCOUNT property. I'm using a 4Guys article for reference:

http://www.4guysfromrolla.com/webtech/042606-1.shtml

the following is a simplified version of the select statement (without all of the JOINS) that I want to implement:

DECLARE @first_id int, @startRow int, @maximumRows;

-- @maximumRows is actually a parameter I pass through the stored procedure. I put it here just to distinguish it
set @maximumRows = 5
	
SET ROWCOUNT @startRowIndex;    -- @startRowIndex is a parameter as well I pass through

select   @first_id = d.DataID   
from     Data AS d 
where    d.AdminID=188  
order by PubDate desc, ModifiedDate desc;

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows;

select   d.*  
from   Data AS d
where   d.DataID >= @first_id AND
		d.AdminID=188  
order by PubDate desc, ModifiedDate desc

SET ROWCOUNT 0

Open in new window

I get the same exact data no matter what I pass through as the @startRowIndex parameter. If anyone can shed some light, I'd be extremely grateful :) I'm currently running MS Sql Server 2008

ASKER CERTIFIED SOLUTION

Snarf0001

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Big Monty

ASKER

Thanks for the response, I was under the impression that the last example didn't use a temp table in the article.

Anyways I'll try going through your link tomorrow when I get back to work, thx again

Snarf0001

You are correct. My fault, didn't read the article all the way through. Sorry.

Regardless, another two things.

1) When I actually implement your code, I AM getting proper distinct result sets when I change the inputs.
So I'm guessing it's something really small like a typeo or variable mismatch in your full code that just needs another pair of eyes to spot.

2) Especially with custom sorting and descending orders, you will most likely see better performance out of row_number(), so pls still have a look at that.

If you want to post the full code for your proc tomorrow I'll take a peek to see if I can spot the issue.

Big Monty

ASKER

Thanks, I'll report back in the morning (or possibly later tonight)

Big Monty

ASKER

here's the full SQL I'm using to pull the data. I'll also take a look at the rownumber link you sent me as well.

ALTER PROCEDURE [dbo].[sp_Data_GetItems_With_Paging] 
(
    @startRowIndex int,
    @maximumRows int,
    @siteID int,
    @accountID int,
    @adminID int
)
AS
DECLARE @first_id int, @startRow int
	
SET ROWCOUNT @startRowIndex

select   @first_id = d.DataID   
from     Data AS d 
where    d.AdminID=188  AND 
d.DataID in  (select distinct s.DataID from DataSiteIDs s, Accounts a   
			  where a.AccountID=2564 and a.AdminID=188 and s.SiteID in (select SiteID from SiteAccess where AccountID=2564)  )    AND 
(d.DataID in (select DataID from DataSiteIDs where SiteID=825296)) 
order by PubDate desc, ModifiedDate desc 

SET ROWCOUNT @maximumRows

select   d.*  , fi.Filename AS ImageFileName  , fi.Size AS ImageFileSize  , fi.Type AS ImageFileType  , fi.LoadDate AS ImageLoadDate  , 
fi.SourcePath AS ImageSourcePath  , fi.SourceDate AS ImageSourceDate  , fi.SourceTitle AS ImageSourceTitle  , fo.Filename AS ObjectFileName  , 
fo.Size AS ObjectFileSize  , fo.Type AS ObjectFileType  , fo.LoadDate AS ObjectLoadDate  , fo.SourcePath AS ObjectSourcePath  , 
fo.SourceDate AS ObjectSourceDate  , fo.SourceTitle AS ObjectSourceTitle  , fg.Filename AS GalleryFileName  , fd.Filename AS DetailFileName    
from   (((Data AS d LEFT OUTER JOIN Files AS fi ON d.ImageFileID = fi.FileID)  
LEFT OUTER JOIN Files AS fo ON d.ObjectFileID = fo.FileID)  
LEFT OUTER JOIN Files AS fg ON d.GalleryFileID = fg.FileID)  
LEFT OUTER JOIN Files AS fd ON d.DetailFileID = fd.FileID    
where   d.DataID >= @first_id AND
		d.AdminID=188  AND 
		d.DataID in  (select distinct s.DataID from DataSiteIDs s, Accounts a   
					  where a.AccountID=2564 and a.AdminID=188 and s.SiteID in (select SiteID from SiteAccess where AccountID=2564)  )    AND 
		(d.DataID in (select DataID from DataSiteIDs where SiteID=825296))  
order by PubDate desc, ModifiedDate desc

SET ROWCOUNT 0

GO

Open in new window

Big Monty

ASKER

making a lot of progress here. The following sql flies through its execution (because I removed the JOINS):

WITH Data1 AS (
select   d.*, ROW_NUMBER() over (order by PubDate) as 'RowNumber'  
from   Data AS d 
where   d.AdminID=188  AND 
d.DataID in  (select distinct s.DataID from DataSiteIDs s, Accounts a   
			  where a.AccountID=2564 and a.AdminID=188 and s.SiteID in (select SiteID from SiteAccess where AccountID=2564)  )    AND 
(d.DataID in (select DataID from DataSiteIDs where SiteID=825296))  
)
select * from Data1 where RowNumber between 0 and 10;

Open in new window

putting the joins in takes the query about 7-8 seconds to load, which isn't horrible, but I may be able to do without them.

going to run through some more tests and then close out this question assuming nothing big comes up.

thanks for the help!

Big Monty

ASKER

figured out to get the total number of rows, all set with this