Tuning a stored procedure

Hello,

I have a procedure that was taking around 11-12 seconds to run on SQL Server 2012 Enterprise edition.

There is table named fs.performance that has 21995968 records and it is referenced a couple of times in the procedure.

I analyzed the procedure in DETA and it suggested a couple of indexes on table fs.performance. I created those indexes and the longest this procedure takes to run (tested with different paramaters) is around 5 seconds now.

This procedure gets called from application using different parameters. It runs fast for some parameters and slow for some other. Please suggest some ways to tune this procedure so that I get the results within under a second

Attached are the details.

Thanks in advance.

Do you get different performance based on parameter values?. If yes, you can create the procedure using WITH RECOMPILE option so it generates a new plan for every execution.

I see that you are using a temp table #results, you can convert that to a table variable as well.