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
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.
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)
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'
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';
