Sql Query Optimization

Sql Query Optimization in MS SQL Server To Improve Performance of ASP.NET web applications.

Data retrieval FROM database in asp.net applications is a most common task and writing optimized queries can have a huge impact on perfirmance of application if database is huge or containing thousands of records in table.






Here i am mentioning few tips to remeber while writing sql queries for optimal performance.



1. Use columns name instead of * in SELECT statements.

1Use:

2 

3SELECT column1,column2,column3 FROM TableName

4 

5Insted of

6 

7SELECT * FROM TableName

by doing so we reduce 1 extra step of converting * into column names by sql server when query is processed.


2. Always create primary key in table.

Making primary key in table ensures that table has a clustered index created.
By doing this SELECT statements using the primary key will make data retrieval very fast because of clustered index on it.


3. Create non-clustered indexes on columns

Columns frequently used in search criteria,joins,foreign keys or used in ORDER BY clause should have index on.

the following query perform better if it has index on column department.

1SELECT firstname,lastname FROM Employee

2WHERE department ='HR'

4. Avoid using function calls in queries.

function calls prevent sql server to use indexes.

1SELECT OrderId,Amount FROM Orders

2WHERE OrderDate > GetDate()

In this query despite of having index on OrderDate, a full table scan will be performed to search each and every record of table because of function call, hence no advantage of indexes and a huge loss in performance.

better solution for this query would be to avoid calling get date in query like this

1Declare @DTime DateTime Set @DTime = GetDate()

2SELECT OrderId,Amount FROM Orders

3WHERE OrderDate > @DTime

5. avoid using COUNT() in a query to check existance of record.


COUNT() counts all matching values by doing a table scan.
In case of EXISTS,When it finds the first matching value, it returns TRUE and stops.

1SELECT column1 FROM TableName WHERE 0 < (SELECT count(*) FROM TableName2 WHERE condition)

better solution would be:

1SELECT column1 FROM TableName WHERE EXISTS (SELECT * FROM TableName2 WHERE condition)

6. Always try to use joins on indexed fields.


7. Avoid triggers as much as possible.


8. Use table variables insted of temporary tables.

Table variables reside in memory while temporary tables reside in the TempDb database So temporary tables require interaction with tempdb database.


9. Use UNION ALL instead of Using UNION.

UNION ALL is faster than UNION as it does not sort the result set for distinguished value.


10. Avoid using Cursors if we need records one by one, use while loop insted.


11. Avoid HAVING clause as it is just like filter after after all rows are SELECTed.


12. Use WHERE clause to narrow the search criteria and to reduce number of records returned in SELECT statment.


13. Use TOP keyword if we want TOP N records in SELECT statement.

and last but not the least


14. Use stored procedure instead of text queries. In stored procedures


1. Always use object name with schema this helps in directly finding the compiled plan insted of searching other objects

1Use:

2SELECT * FROM dbo.TableName

3 

4insted of

5 

6SELECT * FROM TableName

2. Use SET NOCOUNT ON

sql server returns number of rows effected in any SELECT or DML statement and we can reduce this step by setting NOCOUNT ON like this

1CREATE PROC dbo.MyProc

2AS

3SET NOCOUNT ON;