Query Optimization Help – Takes over an hour to run

AltKev 2014-11-10 20:55:23

I'm trying to speed this up but so far I have not been successful. It currently runs over an hour.

I tried to generate an actual plan but the software gave me an error.

Dan Holmes 2014-11-10 20:59:27
I get an error "Unable to find assembly 'Intercerv.SqlServer, Version=8.1.9044.0…." when i try to open the plan.
AltKev 2014-11-10 21:01:23
link text

I saved it manually for this upload, does this work?

Dan Holmes 2014-11-10 21:03:58
no. same thing. Perhaps it is a version issue. What version of PE are you using? I am using 7.5.88.0
AltKev 2014-11-10 21:04:41
Version 2.6 (Build 8.1.9044.0)
SQLkiwi 2014-11-15 05:25:30
There's no easy way to say this: you have just about every common performance anti-pattern here! Complex, non-simplifiable views, repeated non-inline function access, repeated view materialization, mismatched collations, joins and predicates involving complex (statistics-proof) expressions, huge query trees, "one query to rule them all", repeated query evaluations caused by over-use of CTEs, row-goal issues caused by TOP, and most likely execution-time sort and hash spills caused by poor estimations (caused by opaque and complex code), residual predicates…the list is long.

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.

AltKev 2014-11-17 15:36:12
SQLkiwi, thank you very much for your reply. I appreciate your blunt answer. I am relatively new to SQL and most material I've come across so far have not included a lot of guidance for performance considerations.

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.

link text

SQLkiwi 2014-11-18 02:20:23
I'm glad you were able to make some progress. I do apologise if my assessment came off as too blunt, I was trying to be helpful in my own way 🙂