Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    30

    Unanswered: different access plan

    hi,
    this query eventhough it has indexes on all the columns except on timestamp columns on 2 tables its not using the indexes on devlopment server.
    on the production its using the indexes,but eventhough its using the indexes its running very slow.its even taking more time on dev.
    the data is same in both dev and prod. the DFT_QUERYOPT was also the same which is set to 7

    1) my question is why db2 is not using the indexes on dev when its using on prod with data and indexes beeing same on both the databases.
    2) what is the difference between using OR and UNION. will union improve the performance of the queries and if so how?.

    any help is appreciated
    thanks


    SELECT a.col1,
    b.col4,
    c.col1,
    d.col3
    FROM abc.tab1 a
    INNER JOIN abc.tab2 b ON a.c5 = b.c6 AND a.c2 <> 121
    INNER JOIN abc.tab3 c ON ON b.c2 = c.c4 AND a.c8 = 12
    INNER JOIN abc.tab4 d ON c.c7 = d.c9
    WHERE ( a.c_timestamp >= '2007-06-06-08.32.45'
    OR b.c_timestamp >= '2007-09-01-08.32.45'
    OR c.c_timestamp >= '2007-01-01-08.32.45'
    OR d.c_timestamp >= '2006-12-01-08.32.45' )
    Last edited by dareman123; 03-20-08 at 13:30.

  2. #2
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by dareman123
    hi,
    this query eventhough it has indexes on all the columns except on timestamp columns on 2 tables its not using the indexes on devlopment server.
    on the production its using the indexes,but eventhough its using the indexes its running very slow.its even taking more time on dev.
    the data is same in both dev and prod.

    1) my question is why db2 is not using the indexes on dev when its using on prod with data and indexes beeing same on both the databases.
    2) what is the difference between using OR and UNION. will union improve the performance of the queries and if so how?.

    any help is appreciated
    thanks


    SELECT a.col1,
    b.col4,
    c.col1,
    d.col3
    FROM abc.tab1 a
    INNER JOIN abc.tab2 b ON a.c5 = b.c6 AND a.c2 <> 121
    INNER JOIN abc.tab3 c ON ON b.c2 = c.c4 AND a.c8 = 12
    INNER JOIN abc.tab4 d ON c.c7 = d.c9
    WHERE ( a.c_timestamp >= '2007-06-06-08.32.45'
    OR b.c_timestamp >= '2007-09-01-08.32.45'
    OR c.c_timestamp >= '2007-01-01-08.32.45'
    OR d.c_timestamp >= '2006-12-01-08.32.45' )

    I believe there are different statistic between your dev and production.
    The simplest statistic is 'how many rows in the table', 'how many rows hit by this index value'... etc.


    If there is zero rows(close to zeros rows) from your development server, using index may be slower than table scan. The story will be totally different if you have millions of rows.

    You can always unload the statistic from your production server to development server to mimic the environment.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have you configured and tuned the production server already? You may want to run the DB2 Configuration Advisor to get a good base line and then do further tuning if necessary.

    Apart from that, we cannot tell you anything on why things are slow if you don't provide any details like DB2 version and platform, DDL statements for tables and indexes, number of rows, whether stats are current, whether statement compilation or execution takes a long time, ... The usual stuff.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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