homepage Welcome to WebmasterWorld Guest from 54.145.243.51
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
sql query for retriving the only 10 records per page
performance optimization
pritesh

5+ Year Member



 
Msg#: 3741194 posted 12:40 pm on Sep 9, 2008 (gmt 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....

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3741194 posted 3:23 pm on Sep 9, 2008 (gmt 0)

It depends on your DB. I believe MySQL allows something like this.

Anyango

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 3741194 posted 7:21 pm on Sep 9, 2008 (gmt 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]

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3741194 posted 8:49 pm on Sep 9, 2008 (gmt 0)

Nope- not in MS SQL. There are some kludgy workarounds, but they break down with any complicated queries.

blaketar

10+ Year Member



 
Msg#: 3741194 posted 3:20 am on Sep 10, 2008 (gmt 0)

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

returns rows 10 - 20

pritesh

5+ Year Member



 
Msg#: 3741194 posted 5:14 am on Sep 10, 2008 (gmt 0)

I am using MSSQL will it work for that also?

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3741194 posted 3:30 pm on Sep 10, 2008 (gmt 0)

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

mattur

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3741194 posted 4:09 pm on Sep 10, 2008 (gmt 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)

carguy84

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 3741194 posted 6:01 am on Sep 12, 2008 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved