Limiting Large Result Sets

Discussion in 'Java' started by smartnhandsome@gmail.com, Aug 29, 2006.

  1. Guest

    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.
     
    , Aug 29, 2006
    #1
    1. Advertising

  2. steve Guest

    On Wed, 30 Aug 2006 06:04:18 +0800, wrote
    (in article <>):

    > 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/software/tech/java/sqlj_jdbc/htdocs/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/cd/B14117_01/java.101/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
     
    steve, Aug 29, 2006
    #2
    1. Advertising

  3. Tom Cole Guest

    Oracle supports SQL LIMIT command:

    LIMIT <start_row>, <rows_to_return>
     
    Tom Cole, Aug 30, 2006
    #3
  4. 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
     
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=, Aug 30, 2006
    #4
  5. 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/oramag/oracle/06-sep/o56asktom.html

    Arne
     
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=, Aug 30, 2006
    #5
  6. Guest

    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.ResultSet.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??
     
    , Aug 30, 2006
    #6
  7. Oliver Wong Guest

    <> wrote in message
    news:...
    > 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.ResultSet.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
     
    Oliver Wong, Aug 30, 2006
    #7
  8. Guest

    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
     
    , Aug 30, 2006
    #8
  9. Oliver Wong Guest

    <> wrote in message
    news:...
    > 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
     
    Oliver Wong, Aug 30, 2006
    #9
  10. steve Guest

    On Thu, 31 Aug 2006 03:34:31 +0800, Oliver Wong wrote
    (in article <bLlJg.19822$365.14263@edtnps89>):

    > <> wrote in message
    > news:...
    >> 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
     
    steve, Aug 30, 2006
    #10
  11. steve Guest

    On Wed, 30 Aug 2006 11:04:28 +0800, Arne Vajhøj wrote
    (in article <Re7Jg.4462$_q4.3258@dukeread09>):

    > 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



    no it has not!!
    READ the requirement.

    rownum is for returning a MAXIMUM number of records related to a query.
    normally for processing , it does not maintain any state or positional
    information.

    it is not for allowing a select of say 1,000,000 records and returning X
    number at a time.

    steve
     
    steve, Aug 30, 2006
    #11
  12. steve wrote:
    > On Wed, 30 Aug 2006 11:04:28 +0800, Arne Vajhøj wrote
    >> For many year rownum has been the answer.

    >
    > no it has not!!
    > READ the requirement.
    >
    > rownum is for returning a MAXIMUM number of records related to a query.
    > normally for processing , it does not maintain any state or positional
    > information.
    >
    > it is not for allowing a select of say 1,000,000 records and returning X
    > number at a time.


    http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

    browse down to pagination.

    Arne
     
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=, Aug 31, 2006
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike Kingscott

    Shaping data/multiple result sets - hmmm...

    Mike Kingscott, Aug 27, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    327
    Mike Kingscott
    Aug 27, 2003
  2. randall g
    Replies:
    1
    Views:
    1,814
  3. Edmond wong
    Replies:
    0
    Views:
    398
    Edmond wong
    Apr 14, 2005
  4. Replies:
    0
    Views:
    524
  5. Michael Tan
    Replies:
    32
    Views:
    1,054
    Ara.T.Howard
    Jul 21, 2005
Loading...

Share This Page