Hello Friends,
First of all really sorry that you have to wait for 2 weeks for my new article actually i stuck in some real problem in my office project and that is Performance improvement of a store procedure, & that's why our current topic is "SQL Server Performance Improvement"
Here are some important point which i find and used to improve performance of my store procedure i hope this will help you to.
1) Try to use where clause for restrict result.
2)Use predecessor "dbo." for tables.
3)Use proper join ("INNER JOIN ,OUTER JOIN ")
4) Try to avoid "OR" condition use "UNION" over there.
5) Try to avoid "IN" Operation .
6) Try To avoid "NOT IN" operation
7) Try to avoid "DISTINCT".
8) Try to avoid "CROSS JOIN".
9) Try to avoid use of Temporary Table. but if needed then define pre structure for that.
9) Define PRIMARY Key & UNIQUE Key Constraint for each table.
10) Try to avoid "HAVING Clause"
11)Include "SET NOCOUNT" at the first of your store Procedure.
12) Try to avoid "CURSOR".
13) Use "UNION ALL" Instead Of "UNION".
14) Try to create INDEX.
15) Create Index On column which is frequently used in Where , order by & Join.
16) Try to create index on Integer Column.
17) try to avoid "SELECT * " instead of it use "SELECT columnname,"
18) Use Sp_ExecuteSQL instead of EXECUTE
19)Use Explicity Index "With( INDEX( INDEXNAME)) with table.
20) Maximize the thread.
The above 20 points i used and my store procedure is fast.
if you people try to use above 20 points then it will benifitial to you also.
Rest if you need any kind of help of me and my SQL Expert friends then you can just put comment.
Thanks & enjoy SQL Server
Your Host & friends
Rajat :)
For more information you can try http://www.indiandotnet.wordpress.com also.
