Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    12

    Unanswered: Problems with last ResultSet method

    Hi,

    I'm retrieving a great number of rows in a ResultSet and after I want to move to the last row using the last() method but the Jdbc driver reads all rows before placing in the last row.

    Can someone tell me how to optimize it or another way to get the same result?

    Thanks and regards
    Jlgadan

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: Problems with last ResultSet method

    Can you explain what exactly this query is doing?

    dollar

    Originally posted by jlgadan
    Hi,

    I'm retrieving a great number of rows in a ResultSet and after I want to move to the last row using the last() method but the Jdbc driver reads all rows before placing in the last row.

    Can someone tell me how to optimize it or another way to get the same result?

    Thanks and regards
    Jlgadan

  3. #3
    Join Date
    Mar 2003
    Posts
    12
    It's a simply query, the where clause is by an index and ordered by the same one.

    E.g.
    SELECT INDEX_FIELD1, INDEX_FIELD2 FROM TABLE1
    WHERE INDEX_FIELD1 = 'VAL1' AND INDEX_FIELD2='VAL2'
    ORDER BY INDEX_FIELD1, INDEX_FIELD2

    The number of rows retrieved are 114.000 and I need to access at the last one without reading all rows. I've been testing the positioned by the last() method and spends a lot of time.


    Thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you define your order by clause with the DESC option and then use FETECH FIRST 1 ROW ONLY in the select statement, I assume that would get the row you wanted without reading all the rows (if I understand your question correctly).

    However, DB2 "may" need to materialize the answer set in a temp table first and then sort the results in descending sequence. If this were necessary it would take DB2 some time and resources to do this. But if you use the ALLOW REVERSE SCANS on the CREATE INDEX you should be able to alleviate that problem.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You could also try:

    SELECT MAX(INDEX_FIELD1 CONCAT INDEX_FIELD2)
    FROM TABLE1
    WHERE INDEX_FIELD1 = 'VAL1' AND INDEX_FIELD2='VAL2'

    But I really don't know how that would perform. It's possible that DB2 would go straight to the last entry in the composite index and retrieve the last row. I know it would work well on a single column index. You could do a visual explain and some testing to find out.

  6. #6
    Join Date
    Mar 2003
    Posts
    12
    Thanks a lot for your answers.

    I tried using the DESC option in the order by clause but DB2 took some time to do it then I created another index by the same fields with DESC option and looks it's effience.

    I'm going to try creating the indexes with the ALLOW REVERSE SCANS in the CREATE INDEX because it's much better than creating two indexes for each one.

Posting Permissions

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