i




 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Sql Query Optimization ...Tips
11-23-2011, 05:44 AM
Post: #1
Sql Query Optimization ...Tips
1. Use columns name instead of * in SELECT statements.

Use:
Code:
SELECT column1,column2,column3 FROM TableName
Insted of
Code:
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.
Code:
SELECT firstname,lastname FROM Employee
WHERE department ='HR'


4. Avoid using function calls in queries.

function calls prevent sql server to use indexes.
Code:
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

Code:
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.

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

better solution would be:
Code:
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:
Code:
SELECT * FROM dbo.TableName

insted of
Code:
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
Code:
CREATE PROC dbo.MyProc
AS
SET NOCOUNT ON;



Quote


Possibly Related Threads...
Thread: Author Replies Views: Last Post
  Index Optimization tips Shafiur Rahman 1 1,967 07-12-2011 11:51 AM
Last Post: Michael Caine
  Transact-SQL Optimization Tips Shafiur Rahman 1 1,919 12-21-2010 07:15 AM
Last Post: Usman
  Index Optimization tips Shafiur Rahman 2 1,890 10-27-2010 12:04 PM
Last Post: Usman



User(s) browsing this thread: 1 Guest(s)