--- In firebird-support@yahoogroups.com, "mrmuddybum" <mrmuddybum@...> wrote:

>
> Hi.
>
> I have an issue with the following query:
>
> select distinct DOCSUSERCANVIEWEXTERNAL.DOCUMENTID
> from DOCSUSERCANVIEWEXTERNAL
> where DOCSUSERCANVIEWEXTERNAL.GROUPID in
> (
> select distinct PERSONNELHISTORY.GROUPID
> from PERSONNELHISTORY
> where PERSONNELHISTORY.HISTORYCODE in (2,3)
> and PERSONNELHISTORY.DATETIMECREATE >= '1-Mar-1901'
> and not PERSONNELHISTORY.GROUPID=0
> )
>
> The subquery in this case returns the values 1,2,3,4 and if run independantly it returns in less than a second. If I then use these results in the 'in' statement of the parent, again this executes and returns results quickly.
>
> However, running the whole query as above, takes around 90 seconds to return a result set, suggesting that the query is executing the subquery against every row in the parent query rather than executing the subquery just once and using those results in the parent.
>
> Any ideas how to fix this?
>
> Cheers!
>

Hi,

try "exists" instead of "in"



select distinct DOCSUSERCANVIEWEXTERNAL.DOCUMENTID
from DOCSUSERCANVIEWEXTERNAL
where
EXISTS
(
select distinct PERSONNELHISTORY.GROUPID
from PERSONNELHISTORY
where PERSONNELHISTORY.HISTORYCODE in (2,3)
and PERSONNELHISTORY.DATETIMECREATE >= '1-Mar-1901'
and not PERSONNELHISTORY.GROUPID=0
AND PERSONNELHISTORY.GROUPID=DOCSUSERCANVIEWEXTERNAL.GROUPID
)

Karol Bieniaszewski