Can this performance be improved?

Gary 2013-07-26 15:23:39

Hi Folks,

Im new to this site and would not claim to be an sql expert.

The execution plan is below. Any help would be greatly appreciated.

link text

JNK 2013-07-26 15:32:43
Is there a reason that the first query is getting a count from a very complicated subquery? If you only need a count you can drop all the transformations etc. in the SELECT list…
Gary 2013-07-26 15:38:09
Yeah, i have tried that, this was built up dynamically in the .net code. If i remove that, it still doesnt speed it up…
wayne 2013-08-15 14:29:38
the query plan shows a timeout during the optimisation process and is typical of this type of code. As long as this is generated from .Net in this fashion you are going to have to endure the resulting performance penalties. Ultimately you need to give the optimiser a chance to come up with good plans in a stepped fashion. In other words you need to break this up into smaller chunks of work for the optimiser and at the end you combine those sets to create your result.
SQLkiwi 2013-08-31 20:37:37
+1 though timeout is not a good indicator that a query needs to be simplified. The other advice is worth the +1.