How To: Optimize SQL Queries (Tips and Techniques)
Overview
Today many developers try to optimizing query in order to get high performance of the application. I had a chance to optimize many queries and these are my golden rules.
Define SELECT Fields instead of SELECT *
Many SQL developers use SELECT * as a shorthand to query all available data from a table . However, if a table has many fields and many rows, this query will utilize more resources and make slowness.
Inefficient
SELECT * FROM Users
Efficient
SELECT FirstName, LastName, Address, City, State, Zip FROM Users
Avoid Joins with WHERE clause
Some SQL developers prefer to make joins with WHERE clauses, such as the following:
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate FROM Customers, Sales WHERE Customers.CustomerID = Sales.CustomerID
This type of join uses CROSS JOIN in order to generate result. What’s that mean is, first makes all possible combinations variable in database engine. Let assume, Customers table has 1000 records and Sales table has 1000 records. so query would first generate 1,000,000 records first and then filter through CustomerID. This is inefficient of database resource used.
To prevent this, INNER JOIN should be used instead
Use WHERE instead of HAVING
HAVING statements are calculated after WHERE statements. If the intention is to filter a query based on conditions, WHERE statement is ideal and more efficient was instead of HAVING .
Usage of Wildcards
When searching plaintext data, such as names and Address, wildcards create the widest search possible. However, the widest search is also the most inefficient search.
SELECT Name FROM Users WHERE Name LIKE ‘%Man%’
This query goes through each and every row in database and match the criteria. Also This will give unexpected result too. Like ‘Super Man‘
More efficient query would be
SELECT Name FROM Users WHERE Name LIKE ‘Man%’
Limit the Result
As much as try to limit the result when execute the query. This will be depend on the scenario you trying to achieve.
For MS SQL, TOP can be used with SELECt statement.
For other DBMS, LIMIT can be used at the end of the query statement.
Adding more Index into Table.
We know, Primary key and Foreign Key columns are indexed base on data. Also non key index can be added into table so that database engine store data base on the index. That will really help to query data in fast manner.
Try to avoid Table Triggers
Use Stored Procedure instead of direct query
If same query is executed over and over again, better way to get high performance is to make a Stored Procedure.
Find out more about Stored Procedure
