December 01, 2020, 08:54:07 pm
News: If someone gives you assistance give them a Good Star
Pages: [1]   Go Down
Author Topic: Paging with SQL Server  (Read 4007 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: February 26, 2008, 09:26:33 am »

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.
Logged

-- signature --

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #1 on: February 26, 2008, 11:58:29 am »

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.
Logged

-- signature --

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #2 on: February 26, 2008, 10:25:06 pm »

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 smile http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005
Logged

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #3 on: February 27, 2008, 05:29:41 am »

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.
Logged

-- signature --

Pages: [1]   Go Up
Print
 
Jump to: