How to improve the performance of store procedure?


Hi

Here are some useful tips to improve the performance

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,”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: