>Hi;
>
>I am using FB 2.5.3. in a fast environment (win8 64bit - QuadCore 3.4 Ghz - 8 GB ram).
>
>If I run this query it goes very very fast and returns 84 records:
>
>Preparing query: select distinct "PROC" from "GEST" where ("GEST"."FSAL" between '20080801' and '20080812') order by "PROC"
>Prepare time: 0.007s
>Field #01: GEST.PROC Alias:PROC Type:STRING(10)
>PLAN SORT ((GEST NATURAL))
>
>But if I run this one, it will return 84 records but it needs 10 minutes !!!:
>Preparing query: select "PROC"."PROC" from "PROC" where ("PROC"."PROC" in (
>select distinct "PROC" from "GEST" where ("GEST"."FSAL" between '20080801' and '20080812'))) order by "PROC"
>Prepare time: 0.003s
>Field #01: PROC.PROC Alias:PROC Type:STR ING(10)
>PLAN SORT ((GEST NATURAL))
>PLAN (PROC ORDER PROC_PK)
>
>Executing...
>Done.
>-1258819480 fetches, 0 marks, 28351438 reads, 0 writes.
>0 inserts, 0 updates, 0 deletes, 11439 index, 962254304 seq.
>Delta memory: 25308 bytes.
>Total execution time: 0:10:53 (hh:mm:ss)
>Script execution finished.
>
>PROC is a table with 11400 rows. Even if the query optimizer takes the worse path (analizing one by one) it seems that 10 minutes is too much ...
>In my opinion, both may run very quick. Why is the second one taking that long?.

Well, your query does tell Firebird to find the DISTINCT possible values of PROC in a subset of GEST 11400 times. Sure, an ideal optimizer would have been able to see that your subselect wasn't correlated (not referencing the outer select) and that you didn't really bother about whether the subselect returned distinct records or not. Newer versions of Firebird do try to transform IN(<SUBSELECT>) into EXISTS(SELECT...), but I guess that your DISTINCT makes it give up. I think someone once told (probably Ann) that one of the early InterBase betas (or maybe it was even before that) had an optimizer that always chose the quickest way to return a result set. It's only problem was that it could use days to find which way was the quickest!

There are several fair ways to reach the result you want:

1) The standard way to write your type of query
select p.PROC from PROC p
where exists(select * from GEST g where g.PROC = p.PROC and g.FSAL between '20080801' and '20080812')
order by PROC

2) Quicker than option 1, but the result could differ a bit and you may want to add DISTINCT
select p.PROC
from PROC p
join GEST g on p.PROC = g.PROC
where g.FSAL between '20080801' and '20080812'

3) Logically similar to your original query in that you treat things like two statements and put them together. A nice way to do things if you construct your SQL dynamically.
with tmp(PROC) as
(select distinct PROC from GEST where (FSAL between '20080801' and '20080812')
select p.PROC from PROC p
join tmp g on p.PROC = t.PROC

Myself, I sometimes use IN (<constant values>) (I even used it earlier today), but I stopped using IN(<subselect>) after 1 or 2 attempts 15 years ago. I've never missed it since.

By the way, your original query can benefit from an index for GEST.FSAL, but cannot use an index for PROC.PROC. My second and third suggestion above could use indexes for GEST.FSAL and PROC.PROC, whereas my first suggestion only could use indexes for GEST.FSAL and GEST.PROC.

HTH,
Set