Sql Server Stored Procedure Performance Tuning

Now a days as user base is growing so does the problem of data management. We all face performance related problems while performing sql data operations, so mentioned below are some of the tips you can consider while fetching data using sql server:

1. Fetch Only Required Columns: Rather than fetching all the columns try to fetch columns which are needed by your application i.e.
         Replace,
                   Select * from table 
        With,
                   Select col1, col2, col3 from table

2. Apply proper indexing on columns: There are 2 types of indexes one is clustered index which is automatically created on primary key of a table and other index is non-clustered index which can be created on any column. To perform tune your queries it is advisable to create non-clustered index on columns which are mostly used with where clause of select query so that when we search/use column in where clause so sql can simply traverse the non-clustered index rather than traversing the entire table which saves time and hence gives performance gains. Be sure not to over create non-clustered index as index maintenance is also performance expensive operation which takes place when we update/insert something into the table or change the schema of the table by adding/removing some column. So whenever change to table occurs all indexes dependent on table will be recreated which itself is performance extensive so be careful about creating indexes.

3.  Use Temporary Tables: In Stored procedure, there might be a scenario where you need to retrieve data form another select query or you may need to use an inline query. While we perform such operation it leads to slow execution of stored procedure. Consider simple example where you have query like :

 Select EmpID,EmpName from Employee where EmpDepName In
(Select DepName form Department )

Now in this case we have an inline query. So while executing this query, it will execute inline query first and then our main query, which will affect our performance.
It is always a good practice to create a temporary table in stored procedure to improve our performance. Creating a temporary table allows you more flexibility and simplicity for doing further operation in stored procedure.
In above example we can use temporary table to fetch Department data first and then we can use that temporary table in main select query.

Select DepName INTO #temp from Department
Select EmpID,EmpName from Employee where EmpDepName In
(Select DepName From #temp)

4. Avoid Tables Frequently:
In stored procedure there might be a case where we need to use a same table several times. So while executing stored procedure, that table will travers several times. And suppose that table is bulky or having so much data then it will take long time to execute the complete stored procedure.
To avoid this we can take avoid the frequent use of table. For avoiding frequent use we can create a temporary table with that table and then we can use the new created temporary table in rest of the places.

5. Avoid unnecessary Joins/Tables:
Joins are used to fetch records from different tables according to our condition. But I have seen may developers use so many tables in query while fetching a data. There might be a possibility where developer take joins on tables which will not require in select query.
Taking an irrelevant joins/tables will results into slow execution because execution engine need to travers all tables which are there in query. So it’s better to keep our query clean and appropriate. We should use only required tables in query.

Happy Coding!!!