Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: DB2 9.7 from DB2 9.1, an SQL runs slower with >0 criteria

    I'm using DB2 9.7 with AIX 6. Anyway we recently upgraded DB2 from 9.1 to 9.7. There is an SQL that ran fast in 9.1 but now in 9.7 it runs slow (runs for over 30 mins and use to run in 1 min or less). The problem is the F4211.SDITM>0.

    WHERE F4211.SDMCU=' 7001'
    AND F4211.SDURAB=4927698
    AND (F4211.SDOPDJ>=112067 AND F4211.SDOPDJ<=112341)
    AND (F4211.SDDCTO='SO' OR F4211.SDDCTO='SR')
    AND F4215.XHSSTS='25' AND NOT (F4211.SDLTTR='980' OR F4211.SDLTTR='982')
    AND F4211.SDITM>0
    ORDER BY F4211.SDDOCO, F4211.SDITM, F4211.SDLNID

    When I change that to "AND F4211.SDITM<>0" it runs very fast. Is there a reason why DB2 9.7 would run way slower? Why is there a difference of <>0 and >0? I ran an EXPLAIN on both criterias and the <>0 actually had way more timeruns.

    Could someone please help me out with understanding this?
    Thank you.

    CC

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    plz give the output of db2exfmt for these two querys( F4211.SDITM>0 and F4211.SDITM<> 0)

  3. #3
    Join Date
    Feb 2007
    Posts
    84

    Attachment for exfmt_output_GreaterThanZero.out

    Attached is the exfmt_output_GreaterThanZero.out. This is the query that runs very slow. But the Total Cost of the query is much lower the <>0.
    Attached Files Attached Files
    Last edited by rockycj; 10-01-12 at 11:58. Reason: Attachment didn't attach

  4. #4
    Join Date
    Feb 2007
    Posts
    84

    Attachment for exfmt_output_NotEqualToZero.out

    Attached is the exfmt_output_NotEqualToZero.out. This query runs very fast within less than one minute, yet the Total Cost is much higher than the Greater Than Zero query.
    Attached Files Attached Files

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What is the number of rows with SDITM = 0 in F4211? You may want to collect distribution statistics on this table and its indexes.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    hi,
    I think there are something strange in the plan of >0
    1, db2 did not choose sequence prefetch to do IXSCAN ( step 23 )
    2, if there is an equal predicate on SDMCU and a range predicate on SDITM,
    then the index column order should be like this: ( SDMCU, SDITM, ..... )。
    3, in step 22, db2 did not use list prefetch to fetch data page. (Maybe because of the overestimating for the filter factor of predicate ( SDITM > 0) ).
    So, i think
    the best way is to create a additional index (SDMCU, SDURAB, SDDCTO,SDITM) for the query. If you think there are already too many indexes on the table,
    maybe you can change the column order of the index F4211_9 to this (SDMCU, SDITM,
    , SDDRQJ)
    thx.

Posting Permissions

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