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

I checked the Reads statistics for the stored procedure named

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 (code re-factoring) and I got the following results shown in Figure 2. The improvements are significant.

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

What I did?

I replaced the code in the INNER JOIN of the stored procedure with a #temp table (). The INNER JOIN data is first stored in the #temp table (#tmpOutcomeData) and then it’s joined. With this I achieved the improving results shown above in Figure 2.

Results check

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 I recommend reading this post.