Database Tuning – Effective Tips for Database Performance Tuning

Database tuning is
nothing but a set of activities that is required for the purpose of optimization
of the overall performances by the database. During the database performance tuning, the developers need to know few
important things regarding the coding. Let us now discuss about those few
important tips.

Statistics Of Database: The database statistics contain all the
information regarding the indexes and their relation with the distribution. The
developer needs to optimize the information to decide the query path which is
the least expensive. Now, let’s go
through the following query to understand the statistics in a proper way.

SELECT *

FROM Client

WHERE city =”Mexico City”

AND phone = +52 211 4650

Therefore you can only
use a single index for each table. The optimizer can run the query much faster
if the Idx phone numbers are used. After updating the statistics the database
can be informed about the data distribution and can rightly select the index to
run the right query.

Also Read: What is PHP’s MySQLnd and How It Performs Easy Read/Write Splitting?

Creating Optimized Index: You need to create a master key for each table
and set the key as the Enterprise Manager. You must be careful about the number
of indexes as you cannot work with a single index but a large number of indexes
will be responsible for a sluggish performance in the DML (INSERT, UPDATE,
DELETE) queries. Therefore, you are required to estimate the number of indexes
needed for a particular field.

For example, if you
select the Idx city index and look out for all the clients in the Mexico City,
you may end up with thousands of rows and columns. In this case, the index
optimization will not be a good idea as there can be unlimited number of
outcome for indexes.

There can be 2 indexes
which can be significantly used.

Composite Index: The index which include more than one field and
is responsible for multiple tasks can be considered as the composite index.
When you need to run the queries in the multiple fields, the composite index
can be very beneficial for you.

Clustered Index:  A table
may include the physical order of data that can be determined by the clustered
index. Each table can include only a single clustered index. The process is
quite similar to a telephone directory where the data can be arranged according
to the last name. When the range of values is being searched, the clustered
index can be essential in the procedure. Oracle
calls it with a special name which is Index-Organized Table while the SQL
server and Informix call it as Clustered Index itself.

Avoiding The Functions On RHS: When the operator uses the SQL queries, they
may end up using on the extreme right hand side and may lose some important
data in the process.

For example:

SELECT *

FROM Client

WHERE Year (AccountStartedOn) = 2009

AND Month (AccountStartedOn)= 
3

Here you can observe
that though the AccountStartedOn has an index, but when the query changes
during the process, the index cannot be used optimally.

So, to increase the
efficiency and speed, the query can be written somewhat like the following.

SELECT *

FROM Client

WHERE AccountStartedOn between 3/1/ 2009

AND 3/30/2009

Expected Growth can Be Pre-determined: As the indexes have a negative impact on all the
DML queries, the effect can be minimized if a specific value of the fill factor
can be mentioned during the creation of the indexes. It is also observed that
during the creation of the indexes the data is being saved in the hard disk on
its own. If the rows and the columns of the indexes increase, automatically the
database storage space needs to be reorganized according to the number of rows
and columns.If you can presume the increased number of the indexes, you can
easily assume the expected growth of the indexes naturally.

State The Optimizer Hints In SELECT: The optimizer can easily choose the query based
on the statistics which is specifically table based.  If you can mention the index name in the SELECT
option, the process can be smoother e.g.

SELECT *

FROM client

WITH (INDEX ( (IdxPhone))

WHERE city = Mexico City

AND phone = +52 211 4650

You can note the ‘With’
value mentioned after the ‘From’ value in this example. It can only be used in
the MS SQL server. Different database can use different syntaxes for mentioning
different values.

Using The EXPLAIN Value: During the
performance tuning of the SQL queries, the EXPLAIN value can be used for
optimization.

For example:

In Oracle it can
be used as EXPLAIN PLAN FOR  >YOUR
QUERY<

In Informix it can
be used as SET EXPLAIN

In Sybase ASE it
can be mentioned as SET SHOWPLAN_ALL ON >YOUR QUERY<

Foreign Key Obstructions Can Be
Avoided :  The
constraints that can be faced due to the foreign keys can easily be controlled.
The data integrity formulas can be altered and converted towards the
application layers. As the relational database management systems have
different system tables with the database designs, they may contain few Meta
data information regarding the database users. In this case, the database
itself is the client and so can be used during the creation of the indexes.

Input-Output Operations : When both the Input-Output operations
can be observed in the database management , the performance can be expected
to  be more efficient. When multiple data
comes into the scene, it can definitely be split into different hard disks for
serving different purposes.

Choosing Limited Data : If the developer can use limited
data , the performance of the query will be much better. The filtering process
can be taken care of in the end of the server instead of the client. This will
definitely optimize the data and will not take much data space.

Skip The Index Before Inserting Data
:  If you can skip the index before inserting a
large amount of data, there can be an optimum use of it. You can recreate the
index after the loading process is over.

Therefore, the database
performance tuning can be practiced by the developers with the right coding in
the right places.

Author Bio

George Watson is a
well known database expert who is specializing in the performance tuning since
10 years. In this article, he is sharing a few of his valuable suggestions to
the developers for using the right code for performance tuning. For more information you can opt
for the remote dba services – remotedba.com.