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;
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;
