The execution time of my query takes about 27 minutes. I really don't know why it takes so long. Especially I have an other query with the same output, but this one takes only 1,4 Seconds. I want to know what the mean difference between both queries are and why the first one takes so long to execute.
First one, which takes about 27 minutes:
select ATTACHMENT_ID from PES_ESB_ATTACHMENT where ATTACHMENT_ID in(
select distinct ATTACHMENT_ID from PES_ESB_ATTACHMENT A
left outer join PES_ESB_SHIFTDOCUMENTATION SD on A.MOMENT=SD.ATTACHMENT_MOMENT
left outer join PES_ESB_SHIFTTASK T on A.MOMENT=T.ATTACHMENT_MOMENT
where
SD.ATTACHMENT_MOMENT is null
and
T.ATTACHMENT_MOMENT is null
And ( 163697831 - A.MOMENT ) > 86400
)
Second one with 1,4 seconds :
select ATTACHMENT_ID from PES_ESB_ATTACHMENT where ATTACHMENT_ID in(
select ATTACHMENT_ID from (
select distinct ATTACHMENT_ID, A.MOMENT
from PES_ESB_ATTACHMENT A
left outer join PES_ESB_SHIFTDOCUMENTATION SD on A.MOMENT=SD.ATTACHMENT_MOMENT
left outer join PES_ESB_SHIFTTASK T on A.MOMENT=T.ATTACHMENT_MOMENT
where
SD.ATTACHMENT_MOMENT is null
and
T.ATTACHMENT_MOMENT is null
)tbl1
)
and
( 163697831 - MOMENT ) > 86400
How there can be such a big gap ?
Easy. In the second query there is an additional filter available to the optimiser on the outermost part of the query to PES_ESB_ATTACHMENT.
and
( 163697831 - MOMENT ) > 86400
It looks like this is inside the correlation query on the long query. This clearly allows the server to cut down on the records it must run through the correlated subquery.
I recommend you look at the query plans, they will look very different.