SQL QUERY TUNING

Tuning cautions

           SQL tuning in itself is a vast area, which demands expertise. Most of the companies end up hiring experts and application DBA for just to tune the poorly written queries. Just few queries that are written in a poor fashion can end up messing database. So small precautions taken during the query building saves huge for company and makes DBA life easy.

These are the simple steps I have formulated out of my experience in DB2 and SQL

1. Functions, use should be limited in the query, as one should remember use of functions will disable the use of index and forces SQL engine to do table scans. So make sure that functions are used only when they are really needed.

2. Sub-Queries should be replaced by joins where ever possible. Minimize the use of inner queries so as to boost up the performance.

3. DISTINCT, use of this key word is very important. In myth of forcing engine to use indexes most people use distinct which may prove very costly error. I have seen plans with query using distinct where DB2 Engine making use of Indexes for columns that are not primary column in index , i.e. Engine performing complete leaf search for the index column which is very costly in terms of time.

4.Please select only the columns that are needed, do not put * or retrieve unnecessary columns. This operation may increase the query run time.

5.Do not use additional filters or unnecessary filters in where clause which invite unneeded index scans.

6.Try to make use of UNION Instead of OR and IN and NOT IN for = and <>. I have noted that engine performs faster in case of the earlier when compared to later.

        SQL tuning is a expert task and only mastered by practice and experience. In some time near future I would like to provide my experiences in SQL.


         Please do not forget to send me your FeedBack!! Happy Coding!!