Hi:

Recently I have been looking into our production environment and trying to figure out the problem with slow performance. One of the variable in this exercise is to look at existing queries and fine-tune them. My question is how?
1. Re-write the queries (this is what I would like to do)
2. Evaluate the access plan and do something
3. Update statistics (REORG, RUSTATS)
4. Update db or dbm cfg parameters

Are there any set guidelines which helps someone write good queries? Question is vague so feel free to comment.

dollar

My approach to your question on exisiting queries:

First make sure your statistics are OK and do a REORG.
Then evaluate the access plans and try to make changes to a part of the query to improve it
You may consider tuning some db/dbm parameters (like query degree, parallelism, sortheap, queryopt etc and bufferpool size, tablespace-bufferpool mapping, indexes etc) and also OS related stuff like I/O ..

If you think the query is very badly written, yes, you can consider a rewrite .. Rewriting all existing queries, is a very tedious and timeconsuming process ...


As a quick note , for new queries :

1) SELECT only the columns you want
2) Create suitable indexes and keep statistics upto date and define appropriate bufferpools
3) Minimize sorting (reduce DISTINCT, ORDER BY, use UNION ALL instead of UNION whenever possible)
4) Primary-Foreign Key constraints columns are properly indexed
5) For dynamic sql, Whenever possible, use parameter markers to avoid compilation overhead

There could be more points, but I think these are the most basic and important ones

You may want to refer Peter Gulutzan's SQL Performance tuning book ... Will cost you about $40-50