Database Related

How To Tune SQL Queries To Improve Performance

Optimize Your Indexes:

SQL optimizer is highly dependent on the indexes that are defined for each table in the database. Indexes are to be handled and optimized just perfectly for best performance. No indexes will slow down the performance of your SELECT statements while too many indexes will slow down the performance of your INSERT, UPDATE, and DELETE statements. Hence, it is necessary to have the right balance of indexes on each table. A good approach is to estimate the number of unique values that each column will have for every particular field before creating indexes. If the number of unique values are too small for a large database, it is not good practice to create indexes for these. This is because each SELECT query will then return hundreds or thousands of results that will have to be searched sequentially. Thus, not only does it barely help in speeding up the SELECT queries but it also slows down the DML queries.

Foreign Key Constraints Are A NO:

Foreign key constraints are quite commonly used in databases to ensure data integrity. However, this benefit comes at the cost of performance. Therefore, if performance is the main goal, you can limit the foreign key constraints and move the data integrity rules up to the application layer. An example of a database system that uses this approach is one that consists of the system tables that are found in most Relational Database Management Systems (RDBMS). Even though the tables in these databases have relationships between them, there are no foreign key relationships thus ensuring both data integrity and performance.

The More, The Better:

The biggest constraint in data searching and manipulation is the very slow speed of hard disk I/O operations. It is by far the slowest resource on a computer, this becomes widely apparent when the size of a database increases. Now, several databases have the option for splitting the database onto multiple hard drives. They even give the option of splitting up tables onto different hard drives. Why do this? This is because with multiple physical hard drives, the I/O operations speed up by a great extent because the data is fetched in parallel (through all drives simultaneously).

Break It Down:

It is best practice to break down the data. The lesser the data that is to be retrieved, the faster a query will run. Filter down as much as possible on the server-end rather than filtering it down on the client end. When you filter at the server-end, lesser data is being sent and therefore, quicker results are obtained. An example could be:

Optimize this:

Select  FirstName, LastName, City
Where City  = ‘London’

Here the City column can be removed (since it will always be ‘London’) for improved performance in query searching.

 

 

Leave a Reply