Tip 1: Always use WHERE Clause in SELECT Queries while we don’t
need all the rows to be returned. This will help to narrow the return rows else
it will perform a whole table scan and waste the Sql server resources with increasing
the network traffic. While scanning the whole it will lock the Table which may prevent
other users to access the table.
It is seen many times developers use codes like
SELECT * FROM UserDetailsTable WHERE LOWER(UserName)='telsa'
Instead of writing it like the below
SELECT * FROM UserDetailsTable WHERE UserName='telsa'
Infact both the queries does the same work but the 2nd one is better and retrieves
rows more speedly than the first query. Because Sql Server is not case sensitive
Tip 3: While running a query, the operators used with the WHERE
clause directly affect the performance. The operators shown below are in their decreasing
order of their performance.
- >,>=,<, <=
Tip 4 : When we are writing queries containing
NOT IN, then this is going to offer poor performance as the optimizer need to use
nested table scan to perform this activity. This can be avoided by using EXISTS
or NOT EXISTS
When there is a choice to use IN or EXIST, we should go with EXIST clause for better
Tip 5: It is always best practice to use the Index seek while the
columns are covered by an index, this will force the Query Optimizer to use the
index while using IN or OR clauses as a part of our WHERE clause.
SELECT * FROM UserDetailsTable WHERE Status = 1 AND DeptID IN (406,530,956)
Takes more time than
SELECT * FROM UserDetailsTable (INDEX=IX_DeptID) WHERE Status = 1 AND DeptID IN
Tip 6: While we use IN, in the sql query it better to use one or
more leading characters in the clause instead of using the wildcard character at
SELECT * FROM UserDetailsTable WHERE Name LIKE 'm%'
SELECT * FROM UserDetailsTable WHERE Name LIKE '%m'
In the first query the Query optimizer is having the ability to use an index to
perform the query and there by reducing the load on sql server. But in the second
query, no suitable index can be created while running the query.
Tip 7: While there is case to use IN or BETWEEN clause in the
query, it is always advisable to use BETWEEN for better result.
SELECT * FROM UserDetailsTable WHERE ID BETWEEN (5000 AND 5005)
Performs better than
SELECT * FROM UserDetailsTable WHERE ID IN (5000,5001,5002,5003,5004,5005)
Tip 8: Always avoid the use of SUBSTRING function in the query.
SELECT * FROM UserDetailsTable WHERE Name LIKE 'n%
Is much better than writing
SELECT * FROM UserDetailsTable WHERE SUBSTRING(Name,1,1)='n'
Tip 9 : The queries having WHERE clause connected by AND operators
are evaluated from left to right in the order they are written. So certain things
should be taken care of like
- Provide the least likely true expressions first in the AND. By doing this
if the AND expression is false at the initial stage the clause will end immediately.
So it will save execution time
- If all the parts of the AND expression
are equally like being false then better to put the Complex expression first. So
if the complex works are false then less works to be done.
Tip 10: Its sometimes better to combine queries using UNION ALL
instead of using many OR clauses.
SELECT ID, FirstName, LastName FROM UserDetailsTable
WHERE City = 'Wichita' or ZIP = '67201' or State= 'Kansas'
The above query to use and index, it is required to have indexes on all the 3 columns.
The same query can be written as
SELECT ID, FirstName, LastName FROM UserDetailsTable WHERE City = 'Wichita'
SELECT ID, FirstName, LastName FROM UserDetailsTable WHERE ZIP = '67201'
SELECT ID, FirstName, LastName FROM UserDetailsTable WHERE State= 'Kansas'
Both the queries will provide same results but if there is only an index on City
and no indexes on the zip or state, then the first query will not use the index
and a table scan is performed. But the 2nd one will use the index as the part of
Tip 11: While the select statement contains a HAVING clause,
its better to make the WHERE clause to do most of the works (removing the undesired
rows) for the Query instead of letting the HAVING clause to do the works.
e.g. in a SELECT statement with GROUP BY and HAVING clause, things happens
like first WHERE clause will select appropriate rows then GROUP BY divide them to
group of rows and finally the HAVING clause have less works to perform, which will
boost the performance.
Tip 12: Let’s take 2 situations
- A query that takes 30 seconds to run, and then displays all of the required results.
- A query that takes 60 seconds to run, but displays the first screen full of records
in less than 1 second.
By looking at the above 2 situations a developer may choose to follow the 1st
option, as it uses less resources and faster in performance. But actually the 2nd
one is more acceptable by a DBA. An application may provide immediate feedback to
the user, but actually this may not be happening at the background.
We can use a hint like
SELECT * FROM UserDetailsTable WHERE City = 'Wichita' OPTION(FAST n)
where n = number of rows that we want to display as fast as possible. This hint
helps to return the specified number of rows as fast as possible without bothering
about the time taken by the overall query.