How to retrieve set of rows from a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

How to retrieve set of rows from a table?

Author

Topic

samit_java
Starting Member

4 Posts

Posted - 2005-07-21 : 02:47:03

I am trying to display 4 columns of a particular table. The browser

is displaying 50 records per page. 'Next' and 'Previous' buttons are

there for navigating through all the records. It may have even 1

million or more records. The table may have n number of records. I am

not getting any problem if the record size is approximately 50,000.

But for large number of records say, 1 lac or more, the browser is

taking too much time...roughly, for 1 lac record it is taking 35

seconds and for 2.5 lac record it is taking 4 minutes...as the no of

records fetched increases...page loading time also increases

exponentially. So, my aim is to collect partial data from the table

and display it. In oracle I can manage it using rownum and minus

clause, efficiently. But in SQL Server I am not able to handle it,

using those technique. My application runs both on Oracle and SQL

Server. Can anybody help me?

For more clarity :::: I want to collect 1 to 50 records from the

table. next time I want to collect records from 51th. position to

100th. position. And then from 101th. position to 15oth. and so on.
Of course in SQL Server.

I am writing my code in JSP

Thanks -
Samit

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-21 : 02:56:39

Have a look at Jeffs blogs
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

Also search the forums, there has been plenty of posts with regards to paging

Andy

Beauty is in the eyes of the beerholder

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-21 : 03:05:27

Hi you can try this,

i hope this works for you.. i dont know much about the web programming....

Select (Select Count(*) From TabName Where T.unquieColumn < TabName.unquieColumn) As Tab , YoursColumnsList Where
(Select Count(*) From TabName Where T.unquieColumn < TabName.unquieColumn) < 50
order by Tab Asc

which will get you first 50 records then for further you can either use logic of <=...

i hope this works for you.

Complicated things can be done by simple thinking

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-07-21 : 06:33:32

Use this query in the sql to get ur answere,

X = no of rows You require
Y = Record No from which You want to start
Put the table name and column names of ur table

select * from (
select top X * from (select top Y * from <TableName> order by columnname) as test order by <ColumnName> desc) as test2
order by <ColumnName>

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-07-21 : 06:36:54

SORRY IGNORE THE LAST QUERY USE THIS ONE

Use this query in the sql to get ur answere,

X = no of rows You require
Y = Record No from which You want to start
Put the table name and column names of ur table

select * from (
select top X * from (select top (Y + X) * from <TableName> order by columnname) as test order by <ColumnName> desc) as test2
order by <ColumnName>

raclede
Posting Yak Master

180 Posts

Posted - 2005-07-21 : 22:20:56

Basically I've tried all possible pagination but when the records reached 2MIL the paging slows just what you've said,you can try this:

http://weblogs.sqlteam.com/randyp/archive/2005/06/23/6335.aspx

I've tried and tested it with more than 3MIL records and its fast less than 3 secs..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources