One of the first things Firebird taught me (well, I don't remember
whether if it was Firebird 0.9.4 or InterBase 5.6), was to never use
IN(<subselect>). At that time, the optimizer didn't check if the
subselect was correlated(*) or not, and hence executed the subselect for
every possible row in the outer select.
The optimizer has improved since then, but I got used to using EXISTS
and have never wanted to try IN(<subselect>) again and don't know which
pitfalls still exist. I expect that your use of DISTINCT adds to the
difficulties (and order by PROC ignored), but I don't know.
Try replacing your query with
select PROC from PROC
where exists(select * from MOVI
where MOVI.TIPO in ('1','A','B')
and MOVI.FECH between '20190301' and '20190412'
and MOVI.MIEM = '15JMS45D7A'
and MOVI.HECH = 'N'
and PROC.PROC = MOVI.PROC)
I assume you have indexes for MOVI.FECH, MOVI.MIEM and possibly
MOVI.PROC (those seems like selective fields for this particular query).
If such indexes are active, this doesn't seem like a query that should
take long (24 minutes sounds like indexes not being active for MOVI),
even though the EXISTS has to be executed 46000 times.
Alternatively, you could maybe rewrite the query to
select distinct PROC.PROC
from PROC
join MOVI on PROC.PROC = MOVI.PROC
where MOVI.TIPO in ('1','A','B')
and MOVI.FECH between '20190301' and '20190412'
and MOVI.MIEM = '15JMS45D7A'
and MOVI.HECH = 'N'
However, this will eliminate duplicates. You can keep duplicates with
adding a level, e.g.
with TMP(PROC) as
(select distinct PROC.PROC
from PROC
join MOVI on PROC.PROC = MOVI.PROC
where MOVI.TIPO in ('1','A','B')
and MOVI.FECH between '20190301' and '20190412'
and MOVI.MIEM = '15JMS45D7A'
and MOVI.HECH = 'N')
select PROC.PROC
from TMP
join PROC on TMP.PROC = PROC.PROC
If this doesn't help, please show the plans Karol requests (and tell us
something about the definition of indexes referenced and the selectivity
of fields involved in these indexes).
HTH,
Set
(*) with "correlated or not" I mean that the optimizer didn't understand
that the subselect in
SELECT *
FROM A
WHERE A.FIELD IN (SELECT B.FIELD FROM B)
can be executed only once and the result reused, as opposed to the
subselect in
SELECT *
FROM A
WHERE A.FIELD IN (SELECT B.FIELD FROM B WHERE A.FIELD2 = B.FIELD2)
that has to be executed repeatedly.
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
