But it was when CONT went NATURAL that you got subsecond performance, not ARTICOL_DOC.

Try

select distinct
id_cont_debit as id_repartitor
from articol_doc
inner join document on document.id_document=articol_doc.id_document+0
inner join cont on cont.id_cont+0=articol_doc.id_cont_debit
where
(cont.repartitor = 1) and
(document.id_agent_economic = 253)

(i.e. add another +0)

The line (cont.repartitor is not null) simply isn't necessary - it cannot be null when it has to equal 1.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Tiberiu Horvath
Sent: 14. august 2008 17:02
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Faster select - exists

changed to

select distinct
id_cont_debit as id_repartitor
from articol_doc
inner join document on document.id_document=articol_doc.id_document+0
inner join cont on cont.id_cont=articol_doc.id_cont_debit
where
(cont.repartitor is not null) and
(cont.repartitor = 1) and
(document.id_agent_economic = 253)

plan

PLAN SORT (JOIN (ARTICOL_DOC NATURAL, DOCUMENT INDEX (PK_DOCUMENT), CONT
INDEX (PK_CONT))):
PLAN SORT (JOIN (ARTICOL_DOC NATURAL, DOCUMENT INDEX (PK_DOCUMENT), CONT
INDEX (PK_CONT)))

Adapted plan:
PLAN SORT (JOIN (ARTICOL_DOC NATURAL, DOCUMENT INDEX (PK_DOCUMENT), CONT
INDEX (PK_CONT)))


query time 1 sec 200 ms .

You are right, this is a huge increase in performance ...


Thank you,

Tiberiu



From: Svein Erling Tysvær
Sent: Thursday, August 14, 2008 5:57 PM
To: 'firebird-support@yahoogroups.com'
Subject: RE: [firebird-support] Faster select - exists


So, the first query uses

PLAN SORT (JOIN (CONT NATURAL, DOCUMENT INDEX
(FK_DOCUMENT_REFERENCE_AGENT_EC), ARTICOL_DOC INDEX
(FK_ARTICOL__REFERENCE_CONT1, FK_ARTICOL__REFERENCE_DOCUMENT)))

and the second

PLAN (DOCUMENT INDEX (PK_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_CONT1))
PLAN SORT ((CONT NATURAL))

Seems like a bad choice from the optimizer for the first query. Try

select distinct
id_cont_debit as id_repartitor
from articol_doc
inner join document on document.id_document=articol_doc.id_document+0
inner join cont on cont.id_cont=articol_doc.id_cont_debit
where
(cont.repartitor is not null) and
(cont.repartitor = 1) and
(document.id_agent_economic = 253)

And see if that changes the plan to something like:

PLAN SORT (JOIN (CONT NATURAL, ARTICOL_DOC INDEX
(FK_ARTICOL__REFERENCE_CONT1), DOCUMENT INDEX
(PK_DOCUMENT)))

I guess that would give the two queries a similar performance.

Your use of EXISTS prevented Firebird from choosing a suboptimal plan, but I
think that was coincidental.

Set