Paging with SQL Server

[Jason Reed]

In my photo manager application I'm having some problems with a particular query that may pull a lot of data from the database. This is mostly because of my slow machine and so sometimes the data returns and I get a good data set and sometimes I'm getting an empty dataset which throws the datatablereader off and gives me an exception.

So I'm going to attempt to put some paging into this, however what I need to do for paging looks to me as though it is possible that I'm still going to query for lots of data before it applies the limits.

Right now my query looks like this

Code: (sql)

SELECT ca.catalog_id as catid,
            sf.img_id as imgid,
            sd.directory_name as dirname,
            sf.filename as fname,
            sf.total_bytes as size,
            sf.height as height,
            sf.width as width,
            sf.aspect as aspect,
            sf.md5Hash as md5,
            sf.rating as rating
FROM storage_files as sf
JOIN catalog_assignments as ca
ON sf.img_id = ca.img_id
JOIN storage_directories as sd
ON sf.directory_id = sd.directory_id
WHERE ca.catalog_id = @CID
ORDER BY sf.img_id;

From what it says here http://framework.zend.com/issues/browse/ZF-853
It looks like I will need to do this to perform a type of paging

Code: (sql)

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY sf.img_id)  as rnum,
            ca.catalog_id as catid,
            sf.img_id as imgid,
            sd.directory_name as dirname,
            sf.filename as fname,
            sf.total_bytes as size,
            sf.height as height,
            sf.width as width,
            sf.aspect as aspect,
            sf.md5Hash as md5,
            sf.rating as rating
FROM storage_files as sf
JOIN catalog_assignments as ca
ON sf.img_id = ca.img_id
JOIN storage_directories as sd
ON sf.directory_id = sd.directory_id
WHERE ca.catalog_id = @CID
ORDER BY sf.img_id) as empWithRNUM
WHERE rnum >= @MIN AND rnum <= @MAX;

Does that look right to you guys? To me it looks like I'll still be pulling all the data and then limiting it.

[Jason Reed]

I tested it in the SQL Server Management Studio.

It is indeed faster at returning the requested rows. I might have to see how it works then. Strange it looks like it would be slower.

[wrack]

You are on a right track but you still have to provide the pagesize and min and max value to page it rather than getting a full set of data back.

I am short on time so please have a quick look here http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

[Jason Reed]

I'm currently using a page size of 1000 rows. The MIN and MAX values already have placeholders in the so that's not a big problem I just need to make the variables that do this.