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

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

the difference should not be that big ...

first query - 15 sec

select distinct
id_cont_debit as id_repartitor
from articol_doc
inner join document on document.id_document=articol_doc.id_document
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)

second query - exactly 500 ms

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