would like to improve the query

saptek9 2016-09-26 21:18:50

SQLkiwi 2016-09-27 04:34:30
Well that's a file with a lot of statements in it. If you really cannot avoid the cursor logic for whatever reason, you have to at least make sure each statement within the cursor executes as efficiently as possible. The one that jumps out is the INSERT that runs for about 15 seconds each time. The part of the plan (and query) you need to focus on is:

Plan fragment

Review the indexing (or lack thereof) on these tables, with the aim of eliminating the Index Spool and repeated heap/clustered index scans. Most of these should be reasonably obvious, for example, the Index Spool can likely be removed by creating an index on Object3 with the Seek Predicate as key and the other output columns as keys or includes as appropriate. The other scans can be removed by similarly creating an index to support the predicates and output columns.