Welcome to WebmasterWorld Guest from 54.145.122.109

Forum Moderators: open

Message Too Old, No Replies

sql query for retriving the only 10 records per page

performance optimization

     
12:40 pm on Sep 9, 2008 (gmt 0)

New User

5+ Year Member

joined:Sept 4, 2008
posts:6
votes: 0


Hi friends...
I want to run sql select query command to retrive the 10 records
on each page based on the page number.
for example if pagenumber = 2 then I want to retrive records only from 20 to 30.
Right now what I am doing is that I am selecting the all records and for displaying records from 20 to 30, first I am looping to make
cursor to read up to 20th position then from that I am displaying the records.

Code:
select * from table

int loop=pageid*10

while loop
sqlreader.read()
end while

Problem is that for large records it will take lots of time to run this query..
Please suggusts me some better way to do this....

3:23 pm on Sept 9, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5674
votes: 78


It depends on your DB. I believe MySQL allows something like this.
7:21 pm on Sept 9, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 24, 2005
posts:697
votes: 0


If it is MYSQL Database then you can use the Limit Keyword easily for this

for example
select * from table limit 10

// Means record 0 to 10 //

thats so normal right ? yea but this is the trick

select * from table limit 10,20

that means select 20 records after record 10, i think ACCESS database doesnt allow two parameters for the LIMIT keyword, not sure about SQL Server.

[edited by: Anyango at 7:22 pm (utc) on Sep. 9, 2008]

8:49 pm on Sept 9, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5674
votes: 78


Nope- not in MS SQL. There are some kludgy workarounds, but they break down with any complicated queries.
3:20 am on Sept 10, 2008 (gmt 0)

Full Member

10+ Year Member

joined:June 24, 2004
posts:202
votes: 0


SELECT * FROM mytable ORDER BY something LIMIT 10, OFFSET 20

returns rows 10 - 20

5:14 am on Sept 10, 2008 (gmt 0)

New User

5+ Year Member

joined:Sept 4, 2008
posts:6
votes: 0


I am using MSSQL will it work for that also?
3:30 pm on Sept 10, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5674
votes: 78


I am using MSSQL will it work for that also?

As I previously mentioned, no.

See the following for a kludgy workaround (works with the Pubs database):
select * from (
select top 10 emp_id,lname,fname from (
select top 30 emp_id,lname,fname
from employee
order by lname asc
) as newtbl order by lname desc
) as newtbl2 order by lname asc

4:09 pm on Sept 10, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Jan 29, 2002
posts:980
votes: 0


There's a good article on paging with SQL Server at aspfaq [databases.aspfaq.com].

(The article covers Classic ASP, but the SQL bits apply to .Net and the stored procedure at the end may be a good solution)

6:01 am on Sept 12, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 13, 2005
posts:1077
votes: 0


SQL 2005 has paging built into it, FYI.

We use 2005, but haven't moved to paging yet, we use a simple temp table to order the results and then join that table with the ten results we need.