Hi,

> I have 2 tables (definitions at the end of this message):
> the following select is fast: (1 sec)
> SELECT t_apos.ID_APOS,

t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE
(t_apos.f_status = 13 AND t_auftrag.f_adatum between '27.08.2004 00:00' AND
'27.11.2004 11:23' ) AND id_apos != 0

> Plan: PLAN JOIN (T_AUFTRAG INDEX (IND_AUFTRAG_ADATUM),T_APOS INDEX

(RDB$FOREIGN1))

>
> and this is slow: (>191 sec)
> SELECT t_apos.ID_APOS,

t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE
(t_apos.f_status = 13 AND t_auftrag.f_adatum between '27.08.2004 00:00' AND
'27.11.2004 11:23' ) AND id_apos > 0

> Plan:PLAN JOIN (T_AUFTRAG INDEX (IND_AUFTRAG_ADATUM),T_APOS INDEX

(RDB$FOREIGN1,RDB$PRIMARY44))

>
> Only difference is id_apos != 0 and id_apos >0
> Interbase Plananalyzer tells me that it needs > 190 seconds to fetch the

values (about 15.000)

First, please use everywhere aliasses to make the query readable.

SELECT
a.ID_APOS,
a.f_liefer_nr,
a.f_status,
a.f_id_auftrag,
a.f_id_kunde
FROM
t_apos a
JOIN t_auftrag ag ON ag.f_id_auftrag = a.id_auftrag
WHERE
ag.f_adatum between '27.08.2004 00:00' and '27.11.2004 11:23' and
a.f_status = 13 and
a.id_apos > 0

Change the comparison "id_apos > 0" to "id_apos + 0 > 0" to avoid using the
primary index or add a new compound index on (f_id_auftrag, id_apos)

> Can someone explain why this big difference happens ?

Because with > 0 the whole primary index RDB$PRIMARY44 is read and combined
with the index RDB$FOREIGN1 and this is done for every resulting record
T_AUFTRAG.

> Another difference i don't understand:
> This one
> SELECT t_apos.ID_APOS,

t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE
t_auftrag.f_id_kunde = 10 AND id_apos != 0

> PLAN:PLAN JOIN (T_AUFTRAG INDEX (RDB$FOREIGN146),T_APOS INDEX

(RDB$FOREIGN1))

>
> is always faster than
> SELECT t_apos.ID_APOS,

t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE
(t_auftrag.f_id_kunde = 10 AND t_auftrag.f_adatum between '27.08.2004 00:00'
AND '27.11.2004 11:23' ) AND id_apos != 0

> PLAN: PLAN JOIN (T_AUFTRAG INDEX

(RDB$FOREIGN146,IND_AUFTRAG_ADATUM),T_APOS INDEX (RDB$FOREIGN1))

> which takes more time when the difference in the dates is growing.
>
> The first one is faster although there are more values to fetch.
> Can someone please explain this ?

It seems many entries are present between your criteria on f_adatum, but not
that much if a f_id_kunde is known. Adding a compound index on (f_id_kunde,
f_adatum) could help a lot for this query.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info