Results 1 to 3 of 3

Thread: Resultset

  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Question Unanswered: Resultset

    Hello there,

    I am trying to retrieve data from a large database, so far nothing fancy.
    My database contains 1000000 record and {step0} I am searching this database with a sql query. So far no novelty.
    When I get my result I sometime depending on the query get a 10000 answers (rows). So far nothing strange.
    Nonetheless this query is eating a lot of time.
    Afterward I send this re-fprmatted result to a web browser for human validation.
    My concern is with some query that returns heavy result
    1 / after I send the result to the browser and I close (and lose) my dataconnection, because I am waiting for client answer
    2 / each time I am re-querying the database {step0} I get the same 10000 answers, and I have to browse to the next part of the resultset.
    This is taking to long to acheive....

    Did anyone solved this problem ?
    Best regard

    !!! After all don't waste your time answering, I will redesign the query...

  2. #2
    Join Date
    May 2003
    Posts
    11
    I'm sorry if this sounds too basic, but are your tables indexed? Did you runstats to update the indexes?

    You can use SELECT a, b FROM foo FETCH FIRST 100 ROWS ONLY to get a small subset of data. If you data is somehow ordered, you can put a WHERE clause on your query to show your users "pages" of data. Here is an example:

    Page 1:
    SELECT a, b FROM foo ORDER BY a ASC FETCH FIRST 100 ROWS ONLY
    $MaximumA = the last value of A
    Page 2+:
    SELECT a, b FROM foo WHERE a > $MaximumA ORDER BY a ASC FETCH FIRST 100 ROWS ONLY

    Your pages will have overlap, unless "a" is your primary key. It depends on your data definitions.

    What language are you coding in? HTH

  3. #3
    Join Date
    Jul 2003
    Posts
    2
    Thanks very much for your quick answer.
    I didn't know the FECTH sql keyword.
    I am using JAVA to encapsulate database acces.
    Best regard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •