Qry Tuning Tips

1) E.g. Table1 has 1000 rows and Table2 has 1 row.
 Select * from table1, table2 will give faster result than Select * from table2, table1

2) If 3 tables are joined, select the intersection table as the driving table. Intersection table is the table that has many tables dependent on it.

3) Joins: Table joins should be written first before any condition in the Where clause. The condition that filters the max records should be at the end below the joins. Parsing is done from BOTTOM to TOP.

4) Avoid using Select * from

5) Use DECODE to improve performance. (DECODE is like COALESCE)

6) Count(*) is faster than Count(1). Count(pkey) is the fastest though.

7) Restrict the records with WHERE clause instead of using HAVING clause.

8) Minimize Table lookup in Query:
 e.g. Select *  from tab1
 where col1 = (select col1 from tab2 where colx = 3)
 and col2 = (select col2 from tab2 where colx = 4)
 an efficient way to this is:
 Select * from tab1
 where (col1, col2) = (select col1, col2 from tab2 where col2 = 3)
 The same approach can be used for updates.

9) Use Exists clause instead of In clause and Not Exists instead of Not In clause

10) Use Exists in place of Distinct clause
 E.g. Select Distinct a.col1, a.col2 From tab1 a, tab2 b
 Where a.col3 = b.col3
 Instead the query can be written as:
 Select a.col1, a.col2
 From tab1 a
 Where Exists (select 'X' from tab2 b
 Where a.col3 = b.col3)

 11) Use Explain Plan to get the query execution process.

 12) Use Indexes for faster retrieval of data.

 13) Index will be used if the query has the column on which the index is created. If the columns that are not present in the index are selected, the index is not used.

 14) Avoid use of UNION clause as far as possible

 15) Avoid Is Null and Is Not Null on indexed columns

 16) Using Hints helps in performance improvement

 17) Avoid typecasting of indexed columns

 18) Not, !=, <, || will disable use of Indexes

 19) Arithmetic operations in the Where clause will disable indexes

 20) Use OR clause instead of In clause

 e.g. Select * from tab where col1 in ('a','b')
 instead use: Select * from tab where col1 = 'a' or col1 = 'b'

 21) Avoid unnecessary use of Union, Distinct, minus, intersect, order by and group by

22) DISTINCT - always results in a sort

      UNION - always results in a sort

      UNION ALL - does not sort, but retains any duplicates

23) ORDER BY

–may be faster if columns are indexed

–use it to guarantee the sequence of the data

24) GROUP BY

–specify only columns that need to be grouped

–may be faster if the columns are indexed

–do not include extra columns in SELECT list or GROUP BY because DB2 must sort the rows

24) Create indexes for columns you frequently:

–ORDER BY

–GROUP BY (better than a DISTINCT)

–SELECT DISTINCT

–JOIN

25) When the results of a join must be sorted -

–limiting the ORDER BY to columns of a single table can avoid a sort

–specifying columns from multiple tables causes a sort

 26) Favor coding explicit INNER and LEFT OUT joins over RIGHT OUTER joins

–EXPLAIN converts RIGHT to LEFT join

27)BETWEEN is usually more efficient than <= predicate and the >= predicate . Except when comparing a host variable to 2 columns

28) Avoid the % or the _ at the beginning because it prevents DB2 from using a matching index and may cause a scan. Use the % or the _ at the end to encourage index usage

29) For Subquery - when using negation logic:

–Use NOT Exists  (DB2 tests non-existence)

–Instead of NOT IN (DB2 must materialize the complete result set)

30) Use EXISTS to test for a condition and get a True or False returned by DB2 and not return any rows to the query

31) After the indexes, place the predicate that will eliminate the greatest number of rows first

32) Hi,

Check this points...It may help U.

1)         Avoid distinct where ever possible. Check whether distinct is

required or not. No distinct when PK or UK are retrieved.

2)         One can consider usage of union where OR condition exits &

eliminate distincts.

3)         Conditions which are likely to fail should be kept first in a set

of conditions separated by AND.

4)         Always use aliases.

5)         Do not involve columns in an expression.

      select * from emp where salary/12 >= 4000;

The query should be:

select * from emp where salary >= 4000 * 12;

i.e. Avoid using Arithmetic within SQL statements.Arithmetic in a SQL

statement will cause DB2 to avoid the use of an index.

6)         Try to avoid usage of in-built or user defined functions.

select * from employee where substr(name,1,1) = 'G';

The query should be:

select * from employee where name = 'G%';

7)         Avoid datatype mismatch since it may lead to implicit/explicit

casting.

select * from emp where sal = '1000';

The query should be:

select * from emp where sal = 1000;

8)         Substitute unnecessary group by & having with where clause.

select avg(salary) as avgsalary, dept from employee group by dept

having dept = 'information systems';

The query should be:

select avg(salary) as avgsalary, dept from employee where dept =

'information systems';