Performance tuning a stored procedure in Sql Server

The performance of a stored procedure is quite a bit of challenge in any real time scenario. So here are some points where you can do a performance tuning of your stored procedures.                                                    

1.       In the stored check if there are some loops used where some operations are called within that loop. If there are some loops used please remove those loops and rewrite that logic. If required put the loops on the .net code side because loops in Sql Server are slower than that of in C#.net. Even a loop for 10 iterations will take more time and have more chances of failure in Sql Server than you having the same loop run for 100 iterations in C#.net.

2.       Check if there are any temp tables used in the stored procedure. The temp tables should be dropped when they are not required for further processing. Otherwise those causes performance hits.

3.       If the stored procedure is called in a loop in C#.net side then there are a lot of chances of deadlocks and failures. When I joined a project I was asked to fix a deadlock issue in a stored procedure. The developer was calling a stored procedure and passing the parameters of a collection one at a time. So if there were 100 items in the collection with having 4 properties each then the it would call the stored procedure a 100 times passing 4 parameters at a time and was doing an insert operations on the tables. If there was another user who was also trying to do an insert or update on the tables it resulted in deadlocks.

Instead of these we can send the whole data into an XML in the for/for each or whatever loop you may have. And then pass the xml in a single shot to the stored procedure. In the stored procedure we write in such a way that the whole data is inserted into the tables in a single shot. Hence reducing the deadlocks and enhancing the performance.

4.       Another way to reduce deadlocks is to fetch the required set of data into a temp table do the operations insert or update the data into the tables.

5.       The joins are also are troublesome in few scenarios. Either we can write the logic if joins are unavoidable then we need to alter the way the joins have been put such that the same results are obtained with the joins fetching the required number of records.

6.       Have indexes on the most searched or most often queried columns.