Any sugggestion for optimization

NEERAJ PRASAD SHARMA 2015-05-27 06:43:47

Dear mates,

Can anybody help here in optimizing this query, catch is i cant make schema changes.

sdyckes 2015-05-27 15:30:22
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

NEERAJ PRASAD SHARMA 2015-05-28 06:12:01
Ironically yes …
sdyckes 2015-05-28 12:55:23
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.
SQLkiwi 2015-06-05 01:43:08
Does "ironically yes" mean no, you cannot add indexes, or yes you can?
NEERAJ PRASAD SHARMA 2015-06-05 12:11:20
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.