Query optimization

Sooner or later SQL query performance becomes an issue, for any production database. Long-running queries may lead to table locking and data corruption issues, alongwith consuming system resources causing the server and application to run slowly. So, query optimization is important.

Guiding principles for query optimization:

1. Understand how your query is being executed by your database is executing your query

Nowadays all databases offer a way to understand how a query is executed. The first step for query optimization is to understand what the database is doing. Different databases provide different means to understand this. Syntax for MySQL, "EXPLAIN [SQL Query]"; In Oracle, "EXPLAIN PLAN FOR [SQL Query]".

2. Retrieve as little data as possible

The resources that are required to execute a query depends upon the size of data that is to be retrieved. Instead of using 'SELECT *' for retrieving values from a table, use SELECT with the column name whose value u are interested in viewing. The query would then execute faster.

3. Store intermediate results

Using views,unions and subqueries for simplifying execution is a common technique among DBA's. In order to execute a query, the results are immediately used within the query. However when the amount of data retrieved is large it may cause performance issues.

For large data one can use tables to temporarily store values, or break up SQL statements to efficiently execute it. To speed up the query performance even more, one can make use of index on the table.

Below are several specific query optimization strategies.

* Use Index
One of the quick and easy way to speed up a query processing is to make use of index.

* Aggregate Table
Pre-populating tables at higher levels so less amount of data need to be parsed.

* Vertical Partitioning
Partition the table by columns. The strategy helps reduce the amount of data a SQL query needs to process.

* Horizontal Partitioning
Partition the table by data value, most often time.

* Denormalization
The process of denormalization speeds up performance, because it combines multiple tables into a single table. Thus fewer table joins are needed.

* Server Tuning
Each server has its own parameters, and often tuning server parameters
to take full advantage of the hardware resources can significantly speed up query performance.