Any sugggestion for optimization

I see that Table 6 is hit as a table scan multiple times for the query, and it is a Heap. Does no schema changes mean you cannot create or modify indexes?

Stephen

Ironically yes …

Without being able to manage the indexes, you will be limited to re-writing the query, adding query hints, and/or updating the statistics. Since the query is doing so many LEFT OUTER JOINS I would investigate changing the query to limit the rows being utilized in the outer joins and try to limit the results being returned. You can also check to see if the SORT operators could be removed from the subqueries.

Does "ironically yes" mean no, you cannot add indexes, or yes you can?

hi paul,
i cant create indexes on this ,I broke this query in pieces using temp table,
Yes performace improved but not up to the mark.
any suggestion.