What is Database Tuning?

Database Tuning is a group of activity of making database application more robust. Database tuning is used to optimized the performance of the database. It aims to increase throughput and lower the response time. Database tuning is one of an essential process to maximize use of system resources to perform work as efficiently and rapidly as possible.

Basically, Database performance tuning is a responsibility of a Database administrator(DBA) but database developers also play a significant role in the task. During the database performance tuning, the developers need to know few important things regarding the coding and best practices. Although newer relational databases and hardware configuration runs SQL queries with a small response time but there is always a room for improvement. So Let’s have a look how a developer can avoid the mistakes to make database application more robust

Statistics Of Database

The database statistics contains all the information about the indexes and the distributions with each other. A developer needs to optimized the least expensive path that satisfies a query. Missing statistics information can cause to take a more expensive path rather than more optimized path. Therefore this increases the overall response time for a query.

SQL optimizer always chooses the least expensive path which gives the least number of rows in the fastest time. Let’s go through the following query to understand the statistics.

SELECT * 
FROM Employee 
WHERE city="Shanghai"
AND phone="+23894554";

As We know a table uses only one index. Therefore it all depends on the developer to choose what column you are using for the index. In above case, if you use “phone” as an index then the response time will be low.

Creating optimizing Indexes

The SQL optimizer massively depends on Indexes set for a particular table. If you have no Index set for a table then it degrades the performance of select query and if you have a lot of Indexes for a particular table then it slows down your DML(Insert, Update, Delete) queries. Hence it is important to balance the Indexes on tables in a database.

Composite index: In this type of indexing more than one fields are used. Such indexes should be created when you have more than one field in the where clause of the query.

clustered Index: In clustered indexing, a table includes physical order of data that can be determined by the clustered index. Each table contains only one clustered index. It is used in the implementation of a telephone directory. Oracle calls it Index-Organized Table (IOT) whereas DB2, MS SQL Server and Sybase all call it clustered index.

Specify optimizer hints in SELECT

In most cases, the optimizer selects an index according to the statistics but it is better to specify the index name in your SELECT query like this:

SELECT * 
FROM Employee 
WITH ( Index(IdxPhone) ) 
WHERE city="Shanghai"
AND phone="+23894554";

Use EXPLAIN

Most of the newer database return the execution query plan for a SELECT query. Therefore, the EXPLAIN value can be used in fine tuning of the SQL Query.
The different database uses different SQL syntax like:
In Oracle: EXPLAIN PLAN FOR >Your query<
In MS SQL Server: Set SHOWPLAN_ALL ON >Your query<
In DB2: EXPLAIN PLAN SET queryno = xxx for >Your query<

Choose limited data in SELECT

It is a good choice of filtering data at server side rather than on the client side. So you must avoid (SELECT *) if there is no need on the client side. Instead of choosing everything you must select only the required fields in the query. for example:

SELECT Fname, Lname
From Employee;

Avoid Foreign key constraints

As we know in the relational databases a foreign key ensures the data integrity. Therefore, if performance is your first choice then you can push the formula of the data integrity towards the application layer. System tables are a good example of this. These tables contain meta data information about user databases and also there is no foreign key relationship.

Drop Indexes before inserting data

If you want to make you DML queries faster then you must skip the index before loading the data into the table. As I told earlier that indexes reduce the performance of the DML queries. Once the insert operation complete you can restore the index again.

In conclusion, Database tuning increases the performance and reduce the response time. Therefore, developers can perform the database tuning with the right coding in the right places.