Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Cool Unanswered: Fetch first oddity

    Hi all,
    Any ideas while I research would be appreciated.

    Have a developer asked me to help him out on a performance issue. He has this query, he wants the data sorted on a column and to only fetch the first 10 rows. Simple right?
    Now he has his query:
    Code:
    select some_columns
      from this_table, that_table
    where this_col = that_col
    order by my_sort_col
    fetch first 10 rows only
    This runs in about 8 seconds elapsed. He then runs the exact same query without the FETCH FIRST and the results are instantaneous. I say, "oh, its because the data has already been fetched so its still in the bufferpools. He then runs the first query with the FETCH FIRST again, another 8 seconds elapsed.
    I still have to do the explains on both, but thought I'd throw the question out here while I look at it closer.

    Thanks again.
    Dave

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Does it make a difference if he *also* uses the 'Optimize for ... rows' clause (in both cases)?
    Also, is the query coming from JDBC (or CLI,ODBC) - check cursor blocking settings.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Alrighty then,,, I ran the explains and there is a difference that I am really surprised by. I tried on the fetch first query to use OPTIMIZE for 2201, the number of rows that are actually in the resultset, but no difference in the access path. This is with an ODBC connection using AQT or DS, comes across the same.

    The explains are even accessing the tables in a different order and using different indexes. I was just surprised as I had not seen(maybe noticed is a better term) this behavior previously.
    Dave

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    I would check what kind of runstats are present (assess via section actuals), and check the impact of different queryopt values on the plan. Docs say the 'fetch first' clause 'can improve performance' which I understand as performance would be same or better, not the opposite.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    yeah that's what I'm doing right now. These tables live on z/os so running runstats now with keycard and freqval statements
    See if that will result in a difference. The other thing I've found this 2201 rows is almost all of the data in the main table and there are two other tables that have parental type relationship and he is joining to them to get descriptions of the values. Without the FETCH FIRST we are starting with one of the parent tables as a tablespace scan, then hitting the child using an index, with the fetch first its the reverse TS on child then hit the parent via index only. Still surprised the FETCH FIRST would result in a different access path.
    Dave

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Not sure about DB2 on Z, but on LUW I'd also look at the impact of statistical view(s) for the joins.

Posting Permissions

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