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 MontySenior Web Developer / CEO of ExchangeTree.org Asked:
Who is Participating?
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.
0

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 MontySenior Web Developer / CEO of ExchangeTree.org Author 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
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
Thanks, I'll report back in the morning (or possibly later tonight)
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author 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

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author 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!
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
figured out to get the total number of rows, all set with this
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.