Optimize this Query which has to run on 5 million records

jaspal · New User Joined: 22 May 2007 Posts: 68 Location: mumbai

Hi All,

i have to optimize one query that is in SQL ,i have to run this query in DB2 after changing it to DB2 format,
any one optimized query any time, i have to run this query on 5 million records.

presently it is taking 15 minutes to retrieve records...........?

bbessa · New User Joined: 03 Aug 2006 Posts: 13 Location: Brazil

Hi,

Just the query is not enough to help you optimizing the response time. But based on what you've sent, I can come up with this general tips:

1. Create MQT (materialized query tables) for the subselects if disk space is not a problem. Consider the ones with calculations (SUM,AVG) and datatype conversions as priority for the MQT creation.

2. Analyze your access path. Db2 visual explain is a great tool to help you study the db2 optimizer choices for your query, and it's free.

3. After analyzing the access path, review, redefine and create indexes accordingly. Just providing good indexes you can diminish the response time down to seconds or a couple of minutes. Also, you should consider partitioning the tablespace and using DPSIs.

4.Study runstats and DB2 statistics. Use them.

5. Put the most scanned tablespace in a separate buffer pool.

Good lucky ;)

bbessa

stodolas · Posted: Wed Jan 23, 2008 5:21 am

No MQT. That could possibly be the worst possible suggestion without knowing how the underlying tables are used.

If it is not a warehouse table and the data in the underlying tables is constantly updated, then the DB is constantly refreshing the data and then you are just letting DB2 take a big performance hit. This will slow down the whole subsystem.

bbessa · New User Joined: 03 Aug 2006 Posts: 13 Location: Brazil