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

It doesn't make a difference to the speed. If I remove both the total column and the WHERE clause then it runs in less than a second.