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 remember while writing sql queries for optimal performance.

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

Use:

SELECT column1,column2,column3 FROM TableName

Insted of 

SELECT * 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.

SELECT firstname,lastname FROM Employee 
WHERE department ='HR'

4. Avoid using function calls in queries.

function calls prevent sql server to use indexes.

SELECT OrderId,Amount FROM Orders 
WHERE 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

Declare @DTime DateTime Set @DTime = GetDate()
SELECT OrderId,Amount FROM Orders 
WHERE 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.

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

better solution would be:

SELECT 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

Use:
SELECT * FROM dbo.TableName

insted of 

SELECT * 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

CREATE PROC dbo.MyProc
AS
SET NOCOUNT ON;

Tweet

If you like this post than join us or share