Need to improve the running time of the SP. Need help.

Sridhar Madabushi 2014-09-03 12:13:37

I have the following SP which generates a report. It is using multiple temp tables to gather data from the source tables. I am trying to build a dynamic query using the temp tables and create an output report.

The SP runs fine and is giving the expected results. However, it is taking a lot of time to complete and my task is to reduce the runnigntime and improve the performance.

I've tried different options like creating indices on the temp tables before selecting data from them, converting case statements in the 'WHERE' CLAUSE into 'BOOLEAN' EXPRESSIONS.

However, the problem still persists with the execition time.

Please help.

Sridhar Madabushi 2014-09-03 12:50:34
Adding the actual SP and the modified copy with additional indices on the temp tables.link text

link text

link text

Aaron Bertrand 2014-09-11 18:54:20
You've anonymized the query plan, so there's not much I can do to comment on the query logic. I do see a lot of expensive sorts and this may be because the indexes on your #temp tables are not ideal (or at least do not support the sort operations your queries trigger). If you can provided a non-anonymized, post-execution, actual execution plan, I can certainly take a closer look.