Welcome to WebmasterWorld Guest from 54.159.214.250

Forum Moderators: open

sql query for retriving the only 10 records per page

performance optimization

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

5+ Year Member



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 Sep 9, 2008 (gmt 0)

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



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

WebmasterWorld Senior Member 10+ Year Member



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 Sep 9, 2008 (gmt 0)

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



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

10+ Year Member



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

returns rows 10 - 20

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

5+ Year Member



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

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



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 Sep 10, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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 Sep 12, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month