Tuning and optimisation of SQL in DB2 - Tips and Tricks

Most SQL queries are written with little importance to performance tuning. Often, this is not a problem when programs are tested in test environment, where performance is not an issue. But, production environments calls for lesser execution time. Inefficient or poorly tuned queries might lead to longer execution time resulting in lock escalation, deadlocks, etc. By tuning the query, applications can be made to run faster.  

Ø       Using BETWEEN instead of using <= and >= conditions. 

Ø       Use IN instead of LIKE 

Ø       Avoid using DISTINCT as it adds to overhead while filtering duplicates. 

Ø       Avoid using COUNT(*), instead use COUNT(COL_NAME) or EXISTS for existence check. 

Ø       Use NOT EXISTS instead of NOT IN 

Ø       Use ORDER BY clause when sequence is important. 

Ø       Use joins instead of sub queries. A join can be more efficient than a correlated subquery or a subquery using IN 

Ø       Use UNION ALL in place of too many OR condition in the query

Ø       If the table is used frequently for SELECT queries, it is advisable to include more indexes in the WHERE condition. If the table is used frequently for INSERT query, it is advisable to avoid or use fewer indexes. 

Ø       Avoid using SELECT * (select only the required columns from the table), since it will prevent the DB2 optimizer from choosing the best access path. Each query should access only the columns needed for the function to be performed. This will result in maximum flexibility and efficiency. 

Ø       Avoid using IS NULL or IS NOT NULL condition on indexed columns. 

Ø       Limit the usage of Scalar functions such as SUBSTR, CHAR, INT etc. in the WHERE clause. If any scalar function is used for a column in the WHERE clause, the DB2 optimizer will not use a matching index column for the retrieval of the records from the table. 

Ø       Use OPTIMIZE FOR n ROWS clause if number of records to be retrieved is already known. The OPTIMIZE FOR clause tells DB2 to select an access path that returns the first qualifying n rows quickly. 

Ø       Avoid using <>in the SQL

Ø       Use ORDER BY when the sequence of rows being returned is important. This is because the data retrieved by a SELECT statement executed consecutively might change. 

Ø       Use NOT EXISTS instead of NOT IN 

Ø       It is advisable not to use JOIN without predicates unless it is really needed 

Ø       Specify only the columns that will be updated when coding the FOR UPDATE OF clause of  a DECLARE CURSOR statement 

Ø       Use LOAD utility instead of Multiple INSERT 

Ø       Please note that the data type SMALLINT can hold any value between -32768 and +32767 even though the equivalent DCLGEN declaration of it is S9(04) COMP(whose max value and min values are +9999999 and -9999999). Same case with INTEGER and DECIMAL data types 

Ø       Use RESTRICT ON DROP clause to restrict accidental dropping of table, database and table space. To subsequently drop the table, it must first be altered to remove the RESTRICT ON DROP clause 

comments powered by Disqus