mrmuddybum schrieb am 01.04.2011 um 10:32 (-0000):

>
> 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?

I don't know why your query is running so slowly, but you could try a
CTE to see if it performs any better:

with GRIDS (GRID) as (
select distinct PERSONNELHISTORY.GROUPID
from PERSONNELHISTORY
where PERSONNELHISTORY.HISTORYCODE in (2,3)
and PERSONNELHISTORY.DATETIMECREATE >= '1-Mar-1901'
and not PERSONNELHISTORY.GROUPID=0
)
select distinct DOCSUSERCANVIEWEXTERNAL.DOCUMENTID
from DOCSUSERCANVIEWEXTERNAL as DUCVE
join GRIDS on DUCVE.GROUPID = GRIDS.GRID

Not tested.

--
Michael Ludwig