>>
>>When you run this query with Actual Execution plan information, what you get?
>
>I don't know what exactly to tell you. I get an Execution Plan tab displayed with some graphical stuff in it but no particular message that I can tell you. What do you need to know?
How does it run if you remove the total column from the query (the one with subquery)? I think it may be killing the performance.
If it's not broken, fix it until it is.
My Blog