Query Optimization
1.
Remove *
from SELECT and use columns which are only necessary in code
2.
Remove any
unnecessary joins from table
3.
Always use WHERE Clause in SELECT Queries while
we don’t need all the rows to be returned.
4.
Avoid SELECT * FROM OrderTable WHERE
LOWER(UserName)='telsa' , because case insensitive.
5.
The operators shown below are in their
decreasing order of their performance.
=
>,>=,<, <=
LIKE
<>
ie, better to go for '=' rather than 'LIKE' or '<>'
6.
NOT IN, then this is going to offer poor
performance, this can be avoided by using EXISTS or NOT EXISTS.
7.
When there is a choice to use IN or EXIST, we
should go with EXIST clause for better performance.
8.
SELECT * FROM OrderTable WHERE Status = 1 AND
OrderID IN (222,444,111,555)
Takes more time than
SELECT * FROM OrderTable with (INDEX=IX_OrderID)
WHERE Status = 1 AND OrderID IN (
222,444,111,555 6)
9.
While there is case to use IN or BETWEEN clauses
in the query, it is always advisable to use BETWEEN for better result.
10.
Always avoid the use of SUBSTRING function in
the query.
11.
Provide the least likely true expressions first
in the AND. Because if the first condition is false the result also false, ie it never want to check the second condition of AND operation .
12.
It is sometimes better to combine queries using
UNION ALL instead of using many OR clauses.
13.
SELECT * FROM CustomerTable WHERE City =
'Wichita' OPTION(FAST n). In some scenario's we have to display only 10-15 result set in the web page . But in database it having more than Ten Thousand row. In this time the data base will give the 'n' result set very fast, and rest of the data will update soon.
14.
Avoid Expensive Operators Such as NOT LIKE
15.
WITH NOLOCK and WITH READUNCOMMITTED
16.
Avoid Long-Running Transactions. it has to do by the sys admin.
17.
Use Set NOCOUNT ON in stored procedures.
18. You can rewrite a subquery to use JOIN and achieve better
performance.
19.
Performance increase by adding a unique key for
table. (it can index the table by unique key)
