Jack Coates <[EMAIL PROTECTED]> writes:
> I've got a query that needs some help, please. Is there a way to avoid
> all the looping? I've got freedom to work with the double-indented
> sections below ) AND (, but the initial select distinct wrapper is much
> more difficult to change. This is auto-generated code.

Well, you're not going to get any serious improvement without a
wholesale rewrite of the query --- I'd think that something driven by
a GROUP BY memberid_ HAVING count(*) = whatever at the outer level would
be a better way to approach it.  As you have it, the system has no
choice but to fully evaluate two very expensive subselects, from scratch,
for each outer row.


>               ( select count(*) from lyrActiveRecips, members_ a, outmail_  
>               where lyrActiveRecips.UserName = a.UserNameLC_  
>               and lyrActiveRecips.Domain = a.Domain_  
>               and a.MemberID_ = members_.MemberID_  
>               and outmail_.MessageID_ = lyrActiveRecips.MailingID  

Is memberid_ a unique identifier for members_, as one would think from
the name?  If so, can't you drop the join of members_ a in this
subselect, and just use the corresponding fields from the outer table?

>               ( select count(*) from lyrCompletedRecips, members_ a, outmail_
>               where a.MemberID_ = lyrCompletedRecips.MemberID  
>               and a.UserNameLC_ = members_.UserNameLC_  
>               and a.Domain_ = members_.Domain_  
>               and outmail_.MessageID_ = lyrCompletedRecips.MailingID  

Why are the join conditions different here from the other subselect?
Can't you rephrase them the same as above, and then again remove the
inner appearance of members_ ?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


Reply via email to