How To: Optimize SQL Queries (Tips and Techniques)

SQL Query Optimization


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.




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.

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

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

Find more about Tiggers

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

Remember Golden Rule is, always run query in off peak time if dataset is huge.

Leave a Reply