Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    64

    Unanswered: Bad query performance but only occasionally

    Hi,

    db2 v9 in z/OS.

    I have a large query that joins several tables and views. It works fine generally finishing in less than 30 seconds.

    But only for some data conditions in the WHERE clause it slows down a lot and finishes in 8 minutes.

    e.g.

    ....
    AND ((ROLLNUM LIKE 'P15%) OR
    (ROLLNUM LIKE 'P16%) OR
    (ROLLNUM LIKE 'P17%))
    ....

    In this case it takes 8 minutes to finish. But for any other values of ROLLNUM, e.g.

    alone (ROLLNUM LIKE 'P15%)
    or
    alone (ROLLNUM LIKE 'P16%)
    or
    alone (ROLLNUM LIKE 'P17%)

    or both (ROLLNUM LIKE 'P16%) OR (ROLLNUM LIKE 'P17%) etc.

    or any other data value e.g.
    P33%, P34%, P35, it finishes in seconds.

    I have no idea why for those set of three data conditions P15, P16 and P17, it takes so long.

    I have checked the number of rows in the table for these conditions and there are other data values that have far more rows in table than these three. But they finish in seconds too but not these three.

    Any idea what should I be looking at to resolve this issue?

    Apologies if the problem description was not very clear.

    Thanks in advance!
    Amar

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I don't know so much about DB2 for z/OS,
    I guessed that many OR might avoid using indexes bacuase of increased filter factor.

    If my guess was right, the following additional condition might be worth to try.
    Code:
       AND ROLLNUM LIKE 'P1%'
       AND
       (   ROLLNUM LIKE 'P15%'
        OR ROLLNUM LIKE 'P16%'
        OR ROLLNUM LIKE 'P17%'
       )

  3. #3
    Join Date
    Apr 2004
    Posts
    64
    ^ Thanks for the response. It is using index on the table already and this column ROLLNUM is not part of the index.

    Besides it works fine for other data values as I stated earlier. Not sure what goes wrong for this 3 combination of data.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by xamar View Post
    Not sure what goes wrong for this 3 combination of data.
    The only way to be sure is to look at the query plan. However, if you're paid by the hour, you can continue guessing.

  5. #5
    Join Date
    Apr 2004
    Posts
    64
    Quote Originally Posted by n_i View Post
    The only way to be sure is to look at the query plan. However, if you're paid by the hour, you can continue guessing.
    Thanks n_i. I checked the plan_table already. As I mentioned earlier it is using indexes. What exactly would you look in query plan when it runs fast for almost all scenarios except those three data values I mentioned since you are not paid by hour, I assume.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    From your description I would expect the plans to be different for "fast" and "slow" queries.

Posting Permissions

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