Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    34

    Unanswered: size IN LIST having big impact on performance

    DB2 v9 z/OS

    Did see some postings already on this topic but no clear answers yet
    First query : IN LIST with 120 values (returns 593 rows)
    Second query : IN LIST with 130 values (returns 668 rows)

    Same query with IN LIST of 120 values compared to same query with IN LIST of 130 values having a performance degradation with factor 10 as
    an index is used in the first select (and data pages fetched from tablespace) while the second query performs a table scan .
    Monitoring showed first query takes 0.116495 seconds while second query
    takes 1.694264 seconds .

    The only way I managed to force an index scan with the second query with 130 values in the IN LIST (or more) is to add OPTIMIZE FOR 1 ROWS
    (this still returns the 668 rows in case of IN LIST with these 130 values but
    the actual elapsed time went from 1.694264 sec to 0.028218 sec)

    SQL & DDL :
    SELECT col1, col2, col3, col4, col5
    FROM table
    WHERE col2 IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
    , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
    , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
    , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
    , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
    , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
    , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
    , ? , ? , ? ) AND
    col3 IN ('?', '?','?','?','?','?','?')

    Table :
    Column Name Col No Col Type Length Scale Null Def FP Col Card
    ------------------ ------ -------- ------ ------ ---- --- -- -----------
    col1 1 DECIMAL 13 0 N N N 152855
    col2 2 DECIMAL 9 0 N N N 29639
    col3 3 CHAR 4 0 N N N 7
    col4 4 DECIMAL 9 0 N N N 12288
    col5 5 DATE 4 0 Y Y N 1


    unique index (col2, col3, col5) :
    Column Name Seq No O Col Type Length Scale Null Def FP Col Card
    ------------------ ------ - -------- ------ ------ ---- --- -- -----------
    col2 1 A DECIMAL 9 0 N N N 29639
    col3 2 A CHAR 4 0 N N N 7
    col5 3 A DATE 4 0 Y Y N 1


    Anyone on z/OS v9 experienced same issues already ?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Hey Swiss,
    This is nothing new, I ran across it back in V5. It seems you already have your solution. Another option would be the runstats that are being collected. Ensure the Keycard and freqval are being collected. Also, if you take a look at the DB2-L archives over on IDUG.org There have been several discussions on the topic over the last several years that Pat Bossman and Terry Purcell(both work on the optimizer) have weighed in on. I always try to see if I can turn an IN list into and EXISTS/NOT EXISTS when possible.
    Dave Nance

  3. #3
    Join Date
    Jul 2007
    Posts
    34
    Thanks Dave . Am only on db2/Zos since v7 .. will check the idug.org

Posting Permissions

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