Introduction
A query is a request for information from a database. Initially when we have small amount of data everything runs well. Now It has been long time since your team have developed the application. The same query that you were executing earlier was working fine but now it is taking more time. Query optimizer attempts to determine the most efficient way to execute a given query. So in short “Query optimization” is the overall process of choosing the most efficient means of executing a SQL statement. The goal of optimizer attempts to generate the best execution plan for a SQL statement. The best execution plan is defined as the plan with the lowest cost among all considered candidate plan.
There are various approaches that we can follow to optimize the query. First I am going to share some basic tips, by following it your query will run smoother than earlier. Later on we will go in deep.
Basic Tips :
1) Only Select the Columns that you need
Never use *(asterisk) when you are retrieving data from any table. please just use the Column’s Name which you require, It will give you better performance.
Example
SELECT Id, FirstName, LastName, Age, Address FROM Customers;
Instead of:
SELECT * FROM EmployeeDetails;
2) Proper use of EXISTS AND IN
Use operator EXISTS, IN properly. Normally IN is slowest . IN is useful when most of the search criteria in sub-query and EXISTS is useful when most of the filter criteria is in the main query.
Example
SELECT * FROM Customers
WHERE EXISTS ( SELECT * FROM Orders WHERE Orders.CustomerID = Customers.ID)
Instead of:
SELECT * FROM Customers
WHERE ID IN ( SELECT CustomerID FROM Orders)
3) Having clause is used to filter the rows after all rows are selected. so it is just filter. So do not use having clause for any other purpose.
4) If you have to store large binary objects, first place them in the file system and add the file path in the database.
5) Use char/varchar columns data type instead of nchar/nvarchar data type if we do not need to store Unicode data. The char/varchar data
value uses only one byte to store one character whereas the nchar/nvarchar value uses two bytes to store one character.
6) Proper use of Index :
Before going to explain how to use index more appropriately let me first explain you what index is?
Basically index is used when we want to find the data more efficiently and quickly. Users can not see the index it is just use for speeding up the result.
suppose we have a database table called Student with four columns StudentId, StudentName, StudentAge & StudentAddress, assuming Student table has thousands of rows. Now if we have to find all the record of Student table who are named ‘Gaurav’? so we decide to run a simple query like
SELECT StudentId, StudentName, StudentAge, StudentAddress FROM Student WHERE StudentName = ‘Gaurav’
What happens when a table is without Index?
It will filter from all the records. But if we use index , The index provides a fast way to look up data based on the values within those columns.
Indexes cannot be created in a vacuum. In other words, before you can identify and create optimal indexes for your tables, you must thoroughly understand the kinds of queries that will be run against them.
This is not an easy task, especially if you are attempting to add indexes to a new database.
Once you know which queries run the most often and which are the most resource intensive.
Disadvantages of Index :-
- Use of index decrease performance on inserts, updates, and deletes.
- They take up space (this increases with the number of fields used and the length of the fields).
- Some databases will monocase values in fields that are indexed.
In the next part I will discuss about “Index in depth with real time scenario”.
References:
http://www.codeproject.com/Articles/34372/Top-steps-to-optimize-data-access-in-SQL-Server
http://beginner-sql-tutorial.com/sql-query-tuning.htm