Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Limiting Large Result Sets

Reply
Thread Tools

Limiting Large Result Sets

 
 
smartnhandsome@gmail.com
Guest
Posts: n/a
 
      08-29-2006
Hi,

I would be glad if someone could provide solution for this problem.

working ENV:
Jdeveloper 10.1.3 - Development
Oracle 9i - Database
Windows XP - OS

I want to retrieve about 30 rows at a time . The table contains over 2
million rows. I would like to begin at the first row and drag 30 rows
over the network. Then get the next 30 and repeat the process untill
all rows are displayed.

I made 2 approaches for this:
1) cache based
2) query based

Using cache based approach, i was running out of memory error because
all millions rows cannt be cached. Thats obvious. This kind of approach
was working good for 10,000s of rows.

Using query based approach, i was able to retreive all the million rows
by limiting the query. I run the query everytime to get the rows. This
is working absolutely fine but performance is not really good. And this
kind of approach is not acceptable at my work place.

So, i need to do work with resultset itself. I tried to set max rows on
statement using:
prepareStatment.setMaxRows(10);
resultset = prepareStatment.executeQuery();

Here, i can obtain first 10 rows but if i need to get from 11- 20 and
20-30 rows and so on.......how do i achieve it????

Any other suggestions are also acceptible.
Looking forward for the reply.

Thanks.

 
Reply With Quote
 
 
 
 
steve
Guest
Posts: n/a
 
      08-29-2006
On Wed, 30 Aug 2006 06:04:18 +0800, http://www.velocityreviews.com/forums/(E-Mail Removed) wrote
(in article <(E-Mail Removed) .com>):

> Hi,
>
> I would be glad if someone could provide solution for this problem.
>
> working ENV:
> Jdeveloper 10.1.3 - Development
> Oracle 9i - Database
> Windows XP - OS
>
> I want to retrieve about 30 rows at a time . The table contains over 2
> million rows. I would like to begin at the first row and drag 30 rows
> over the network. Then get the next 30 and repeat the process untill
> all rows are displayed.
>
> I made 2 approaches for this:
> 1) cache based
> 2) query based
>
> Using cache based approach, i was running out of memory error because
> all millions rows cannt be cached. Thats obvious. This kind of approach
> was working good for 10,000s of rows.
>
> Using query based approach, i was able to retreive all the million rows
> by limiting the query. I run the query everytime to get the rows. This
> is working absolutely fine but performance is not really good. And this
> kind of approach is not acceptable at my work place.
>
> So, i need to do work with resultset itself. I tried to set max rows on
> statement using:
> prepareStatment.setMaxRows(10);
> resultset = prepareStatment.executeQuery();
>
> Here, i can obtain first 10 rows but if i need to get from 11- 20 and
> 20-30 rows and so on.......how do i achieve it????
>
> Any other suggestions are also acceptible.
> Looking forward for the reply.
>
> Thanks.
>



there are a number of ways.

do your self a big favour and ensure you download 10G release 2 drivers
10.2.0.1.0 jdbc
http://www.oracle.com/technology/sof...docs/jdbc_1020
1.html
you need ojdbc14.jar possibly orai18n.jar

1. by setting up the driver correctly.
2. by reading the docs.

http://download-east.oracle.com/docs...b10979/toc.htm

specifically section 17



17.5 Fetch Size
By default, when Oracle JDBC executes a query, it receives the result set 10
rows at a time from the database cursor. This is the default Oracle
row-prefetch value. You can change the number of rows retrieved with each
trip to the database cursor by changing the row-prefetch value (see "Oracle
Row Prefetching" for more information).
JDBC 2.0 also allows you to specify the number of rows fetched with each
database round trip for a query, and this number is referred to as the fetch
size. In Oracle JDBC, the row-prefetch value is used as the default fetch
size in a statement object. Setting the fetch size overrides the row-prefetch
setting and affects subsequent queries executed through that statement
object.
Fetch size is also used in a result set. When the statement object executes a
query, the fetch size of the statement object is passed to the result set
object produced by the query. However, you can also set the fetch size in the
result set object to override the statement fetch size that was passed to it.
(Also note that changes made to a statement object's fetch size after a
result set is produced will have no affect on that result set.)
The result set fetch size, either set explicitly, or by default equal to the
statement fetch size that was passed to it, determines the number of rows
that are retrieved in any subsequent trips to the database for that result
set. This includes any trips that are still required to complete the original
query, as well as any refetching of data into the result set. (Data can be
refetched, either explicitly or implicitly, to update a scroll-sensitive or
scroll-insensitive/updatable result set. See "Refetching Rows".)

Steve



 
Reply With Quote
 
 
 
 
Tom Cole
Guest
Posts: n/a
 
      08-30-2006
Oracle supports SQL LIMIT command:

LIMIT <start_row>, <rows_to_return>

 
Reply With Quote
 
=?ISO-8859-1?Q?Arne_Vajh=F8j?=
Guest
Posts: n/a
 
      08-30-2006
Tom Cole wrote:
> Oracle supports SQL LIMIT command:
>
> LIMIT <start_row>, <rows_to_return>


Not my Oracle 9i.

For many year rownum has been the answer.

Arne
 
Reply With Quote
 
=?ISO-8859-1?Q?Arne_Vajh=F8j?=
Guest
Posts: n/a
 
      08-30-2006
(E-Mail Removed) wrote:
> working ENV:
> Jdeveloper 10.1.3 - Development
> Oracle 9i - Database
> Windows XP - OS
>
> I want to retrieve about 30 rows at a time . The table contains over 2
> million rows. I would like to begin at the first row and drag 30 rows
> over the network. Then get the next 30 and repeat the process untill
> all rows are displayed.


> Any other suggestions are also acceptible.


Try look at rownum !

http://www.oracle.com/technology/ora...o56asktom.html

Arne
 
Reply With Quote
 
smartnhandsome@gmail.com
Guest
Posts: n/a
 
      08-30-2006
Thanks for every ones replies to make the question more clear this
what i wanted from the code
I have a method which can accept a SQL query as input parameter and
number of results the user wants

public RowSetDynaClass getResults(String sql,int resultNumber)
{
prepareStatment= conn.prepareStatement(sql.toString()),
ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultS et.CONCUR_READ_ONLY);
prepareStatment.setMaxRows(resultNumber);
resultset = prepareStatment.executeQuery();
rowsdc = new RowSetDynaClass(resultset);

}

So the user gets back a result for the first 10 rows, now the user
again requests another set of 10 rows. This method is again called and
here is the real problem starts he now wants to get results from 11-20.
Simply put the question is how to maintain the state of the results
with out modifying the query the user inputs??

 
Reply With Quote
 
Oliver Wong
Guest
Posts: n/a
 
      08-30-2006

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...
> Thanks for every ones replies to make the question more clear this
> what i wanted from the code
> I have a method which can accept a SQL query as input parameter and
> number of results the user wants
>
> public RowSetDynaClass getResults(String sql,int resultNumber)
> {
> prepareStatment= conn.prepareStatement(sql.toString()),


Why are you calling String.toString() ?

> ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultS et.CONCUR_READ_ONLY);
> prepareStatment.setMaxRows(resultNumber);


Wouldn't a better name for the second parameter be "maxRows"?

> resultset = prepareStatment.executeQuery();
> rowsdc = new RowSetDynaClass(resultset);
>
> }


I noticed you don't actually return anything from this method.

>
> So the user gets back a result for the first 10 rows, now the user
> again requests another set of 10 rows. This method is again called and
> here is the real problem starts he now wants to get results from 11-20.
> Simply put the question is how to maintain the state of the results
> with out modifying the query the user inputs??
>


How about creating an object which maintains its own state and returning
that?

- Oliver

 
Reply With Quote
 
smartnhandsome@gmail.com
Guest
Posts: n/a
 
      08-30-2006
Thanks Oliver its just snippet yes I do return the object ...I did not
really get what you mean when you say "object that does its own session
management" can you please elaborate

 
Reply With Quote
 
Oliver Wong
Guest
Posts: n/a
 
      08-30-2006
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...
> Thanks Oliver its just snippet yes I do return the object ...I did not
> really get what you mean when you say "object that does its own session
> management" can you please elaborate
>


Return an object which provides an API for the client to read 10 rows at
a time. This object will keep track of where the client currently is in the
overall result set.

This is similar to the behaviour of the object returned by the
List.iterator().

- Oliver

 
Reply With Quote
 
steve
Guest
Posts: n/a
 
      08-30-2006
On Thu, 31 Aug 2006 03:34:31 +0800, Oliver Wong wrote
(in article <bLlJg.19822$365.14263@edtnps89>):

> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) ups.com...
>> Thanks Oliver its just snippet yes I do return the object ...I did not
>> really get what you mean when you say "object that does its own session
>> management" can you please elaborate
>>

>
> Return an object which provides an API for the client to read 10 rows at
> a time. This object will keep track of where the client currently is in the
> overall result set.
>
> This is similar to the behaviour of the object returned by the
> List.iterator().
>
> - Oliver
>


there is no need for this complexity, it is all built in.
all that it requires is for the poser to actually READ the oracle
documentation.

it is not as if he is the only person to want to do this, there are thousands
of clients with this requirement, and it is all controllable from the JDBC
driver.


steve

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
6500: User-Based Rate Limiting AND Total Rate Limiting Patrick Cervicek Cisco 0 08-07-2007 03:07 PM
1. Ruby result: 101 seconds , 2. Java result:9.8 seconds, 3. Perl result:62 seconds Michael Tan Ruby 32 07-21-2005 03:23 PM
Looking for the best way to consolidate result sets into single responsemessage Edmond wong Java 0 04-14-2005 04:25 AM
Sql Server multiple XML result sets to ADO.NET randall g ASP .Net 1 12-29-2003 09:24 PM
Shaping data/multiple result sets - hmmm... Mike Kingscott ASP .Net 0 08-27-2003 12:39 PM



Advertisments