SQL Query Performance Tuning Part II

Avoid functions on RHS of the operator

Often developers use functions or method with their
SQL queries. Consider the following example. select *
from Customer
where YEAR(AccountCreatedOn) = 2005
and MONTH(AccountCreatedOn) = 6
Note that even though AccountCreatedOn has an index,
the above query changes the where clause such a way
that this index cannot be used anymore.
Rewriting the query in the following way will
increase the performance tremendously.
Select *
From Customer
Where AccountCreatedOn between '6/1/2005'
and '6/30/2005'

Check the null Separately

If you are checking for null in the query with some
value say in where condition id in (null,1,2,3) then
check the null separately and the values separately
Instead of
Select * from table where id in (null, 1,2,3)
Use
Select * from table where id=null and id in (1,2,3)
When you are checking a list of values as not in the
performance will be low. Try to avoid not in where
clause instead use in clause which will return record
faster.

Predetermine expected growth

As mentioned earlier indexes have a negative impact
on DML queries. One way to minimize this negative
affect is to specify an appropriate value for fill
factor when creating indexes. When an index is
created, the data for indexed columns is stored on the
disk. When new rows of data are inserted into the
table or the values in the indexed columns are
changed, the database may have to reorganize the
storage of the data to make room for the new rows.
This reorganization can take additional toll on DML
queries. However, if you expect new rows on a regular
basis in any table, you can specify the expected
growth for an index. The terminology used for this
expected growth is different in every database. The
following table lists the terms used by different
RDBMS for expected growth.
Oracle: PCTFREE - Percent Free
Specify optimizer hints in SELECT
Although in most cases the query optimizer will pick
the appropriate index for a particular table based on
statistics, sometimes it is better to specify the
index name in your SELECT query. For example, consider
the following SELECT *
FROM customer
WITH ( Index(IdxPhone))
WHERE city = 'New York City'
and phone = '212-555-1212'
Notice the additional "WITH" clause after FROM. This
example is specific to MS SQL Server. Every database
use different syntax for specifying this value and
they are quite different from each other.

Use EXPLAIN

Most databases return the execution plan for any
SELECT statement that is created by the optimizer.
This plan is very useful in fine tuning SQL queries.
The following table lists SQL syntax for different
databases.
Oracle: EXPLAIN PLAN FOR >Your query<

Avoid foreign key constraints

Foreign keys constraints ensure data integrity at the
cost of performance. Therefore, if performance is your
primary goal you can push the data integrity rules to
your application layer. A good example of a database
design that avoids foreign key constraints is the
System tables in most databases. Every major RDBMS has
a set of tables known as system tables. These tables
contain meta data information about user databases.
Although there are relationships among these tables,
there is no foreign key relationship. This is because
the client, in this case the database itself, enforces
these rules.