I am being told that this process use to complete in 6 to 9 seconds, now it is taking over an hour!

TomHSQL 2017-11-16 13:38:41

Initially I was concerned about parameter sniffing, adding with RECOMPILE as a test. The process still took over an hour to complete. Any insights would be helpful.

Thank you.

SDyckes2 2017-11-16 22:24:29
When posting a plan, it is best to generate the plan with Plan Explorer and save it as a .pesession file instead of a .queryanalysis file. There is additional information that is stored in our format. Also, please utilize the most up-to-date Plan Explorer version.

Is this problem due to more data being processed now than before, a scaling issue? The use of functions throughout the code leads me to believe this could be the case. But, seeing the difference in the Estimated vs Actual rows, I would check that the statistics are up-to-date for the tables and indexes being used.

There are 4 Key Lookups in Statement1 that can potentially be removed. There is also an implicit convert in Statement1, this warning is indicated by the exclamation point on the SELECT box in the execution plan, then by hovering over the box, a popup message will present itself with the conversion warning. Eliminating the implicit convert will increase the performance.

The UPDATE Object1 statements are updating a column by using a function. Utilizing a function causes the update to be processed via RBAR, which is not the optimal method of processing data in SQL Server. I would recommend investigating methods of updating batches of data instead of RBAR, which means rewriting the SQL Code. You may also be able to replace some of the sub-queries using a function call with the temp tables, processing the data with SQL instead of a function and removing the RBAR aspect of the sub-query.

It is difficult to provide more detailed fixes with the limitations of the anonymized plan.