The code optimization is a mandatory task for the programmable objects in SQL Server. During time some code can start working “badly” i.e. become slow. In that case a code-refactoring is required.

I checked the Reads statistics for the stored procedure named spLivePage_LiveChanges (attached).

I obtained the following results for them shown in Figure 1.

Figure 1 Reads statistics for stored procedure spLivePage_LiveChanges

Then I changed spLivePage_LiveChanges to a new version (code re-factoring attached as spLivePage_LiveChanges_CHANGED).  

I replaced the code in the INNER JOIN of the stored procedure that contains a sub query with SELECT, with a #temp table. The INNER JOIN data is first stored in the #temp table (#tmpOutcomeData) and then is joined. I also added some indexes for better filtering.  With this I achieved the improving results shown in Figure 2.

Figure 2 Reads statistics for stored procedure spLivePage_LiveChanges (new version).

It’s important to check that the new version of the stored procedure returns the same results as its old version. In that case you’re sure you’d made the changes correctly and thus not changing the business logic of a part in the application.

If you want to know something more about the temp objects in SQL Server then you can check out this post.