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
LVL 34
Big MontyWeb Ninja at largeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Snarf0001Commented:
In the article given, there's a direct correlation to @startRowIndex and @first_id.
You aren't creating the temp table, and are never assigning any offset to @first_id.  So every time you run the proc, you're setting the maximum rows (rowcount) based on @startRowIndex, but then always grabbing the first ID in the table to base the entire query off.

Two things:
1) The proc in the article works based off the temp table, if you're just using the identity column (once you fix the @first_id issue), you still can't guarantee you'll get the same page size every time, because IDs can be skipped due to failed inserts / deletes.
2) The article is out of date.  It was written in 2006, and there are far better methods to do this now.  If you're on 2008, I would highly suggest you look at the row_number() function, will yield better results and easier code than pushing everything into a temp table;

https://msdn.microsoft.com/en-ca/library/ms186734(v=sql.100).aspx

Specifically example B, returning a subset.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big MontyWeb Ninja at largeAuthor Commented:
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
Snarf0001Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Big MontyWeb Ninja at largeAuthor Commented:
Thanks, I'll report back in the morning (or possibly later tonight)
Big MontyWeb Ninja at largeAuthor Commented:
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 MontyWeb Ninja at largeAuthor Commented:
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 MontyWeb Ninja at largeAuthor Commented:
figured out to get the total number of rows, all set with this
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.