Query Optimization Help – Takes over an hour to run
I tried to generate an actual plan but the software gave me an error.
I saved it manually for this upload, does this work?
Some of the comments in the code seem to indicate someone is at least aware of some of the problems, but major performance improvements will require a transition to a more relational and optimizer-friendly architecture. I'm sorry if that all sounds a bit negative, but there is an awful lot of code and query tree there, so the best I can do is point to the fundamental causes. Addressing those will probably be painful, and perhaps not even practical, but there we are.
I ended up creating a table that has the phone numbers cleaned of non-numeric values ahead of time. I also changed the output so that Excel is used to aggregate the data instead of relying on SQL. The query is now finishing in under a minute. Attached is the updated plan estimate.