>>
>>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.