>>>This monstrous query is very hard to read and impossible to understand. Can you break it to managable pieces using either CTE or temp tables?
>>
>>Hi Naomi - as I said my TSql is very limited - don't know what CTE is and how would temp tables help ? - thanks
>
>To learn more about CTE check out my blog post
>
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries>
>I recently was trying to fine tune the complex stored procedure as well. Using some advices from Peter Larsson and dividing this problem into smaller pieces and using temp tables for intermediate results helped to reduce the time quite significantly.
I was doing some Mysql recently and the benefit of temporary tables was that you could optimise the temp table indexes as well.