Tuesday, January 20, 2009

First level of Performance Tuning in SQL Server

A Simple way to performance tune your query:
a) Check if the WHERE clause is using INDEX or FULL TABLE scan using query plan
b) Ensure the columns used in WHERE clause is in the order of the index creation (if applicable)
c) Include the WHERE condition that would limit the number of records to be searched and then the JOIN condition

Recently I have created a query which took nearly 4 minutes to get executed. I did the following changes, which made my query get executed in 2 minutes.
(I have changed the table names for this sample)
Initial Query:
SELECT FROM Employee empINNER JOIN Address adr ON emp.EmpID = adr.EmpIDLEFT OUTER JOIN Phone ph ON emp.EmpID = ph.EmpIDLEFT OUTER JOIN FamilyMembers fly ON emp.EmpID = fly.EmpIDLEFT OUTER JOIN BankDet bk ON emp.EmpID = bk.EmpID
I commented all the JOINs and uncommented each JOINs and verified the execution time. Only for Phone it took more time. Hence I moved the JOIN condition for Phone at the end of the JOINs. This limited the records to be processed for the Phone table and reduced the timing.

Tuned Query:
SELECT FROM Employee empINNER JOIN Address adr ON emp.EmpID = adr.EmpIDLEFT OUTER JOIN FamilyMembers fly ON emp.EmpID = fly.EmpIDLEFT OUTER JOIN BankDet bk ON emp.EmpID = bk.EmpIDLEFT OUTER JOIN Phone ph ON emp.EmpID = ph.EmpID

1 comment:

Unknown said...

a) What was the number of records in phone table?
b) Did you check the index of phone table? Did creating an index make the performance better?