Thread: different access plan
03-20-08, 12:16 #1Registered User
- Join Date
- Dec 2007
Unanswered: different access plan
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
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 12:30.
03-20-08, 12:31 #2Registered User
Originally Posted by dareman123
- Join Date
- May 2003
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.
03-21-08, 13:40 #3Registered User
- Join Date
- Jan 2007
- Jena, Germany
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