Recently, I worked on optimizing a Store Procedure. Previously the SP took 24 seconds to execute for one single response. Now it takes only 0.01 second.
The system use the SP to store the Response data in Report table while a user submit the response. So it is very bad experience for the users to wait a long after submitting their responses.
While multiple user submit their response at the same time the Database server became irresponsible stage. We saw in NewrRelic the SP execution time was top of the Table. After deploying the optimized SP, NewRelic shows the SP execution time is bottom of the Table.
Though there are many good article about SP optimization in web, here I have shared my experience and way of optimization of the SP below.
At the stage, the execution time of the SP came down to 11 seconds . But I am not impressed with the timing because only 39 rows inserted into Report table for the the response I took for testing. So I tried to find out other areas where the SP take much time.
from Table A inner join Table B on B.AID = A.ID.
I just alter the condition by the following way
from Table A inner join Table B on A.ID = B.AID.
At the stage, the execution time of the SP came down to 6 seconds.
Now I execute the SP and found the execution time is 0.01 second.
- The SP use 3 select block with Union to prepare the Response data. Here use Union where else Union All is faster. So I have made the first change upon the SP with Union ALL which reduce 2/3 sec.
I have noticed 2 of the select Block have Inner Join after Left Join. Its very important to comes all Inner Join first than use Left Join in select statement. So I have made the change here.
- In above image you can see there a complex query is used as Left join. I have also noticed the same complex query used in 2 of the 3 blocks. So I decided to create a temp-table for this complex query and use the temp table for joining.
I have also notices the left join condition use ISNULL function which is little bit costly operation. As it is not possible to remove all the ISNULL function, I just make the temp table in a way so that I can reduce the ISNULL operation on temp table fields.
- I noticed the inner join condition use the following way
- I am dig down again, how can I reduce this 6 second. I run execution plan to see what areas taking the time. I noticed that execution plan show Cluster Index scan for a joining and it takes the times. But It should be non-Cluster index scan because I found there is a non-Cluster index on a Table. Finally, I found the other table missing an index. So I create a non-cluster index on that field.