Sebastian Leupold wrote:
Ernst-Peter, did you do any performance checks, whether the costs of the sub-query don't supersede the costs of the left join? I can't follow the argumentation that a left join creates a carthesian product, if proper fields are joined - AFAIK performance depends on the indexes being used for join and where.
Well, my remark that the outer join is a problem in general , is not correct. But in this query in the Sproc structure it is. Please note that we have > 500 portals and about 100 modules.
Testing in our problem site: Original SProc: more then 41secs tot reterieve data for 1 portal. Modifies SProc: subsecond respons.
The query plan for the original SProc query spends about 80% of the query costs in the outer join execution.
BTW I remember this problem from our MS Access times, where a lot of people had a problem selecting from 1 table, that did not have a related row in another. The outer join in combination with a filter on Null dimmed the lights in the office 
.