Hi Tiberiu!

There are a couple of things to consider. The most important is that you do not specify the plans these two selects choose to use - it may be that the first doesn't choose the optimal plan - either because it makes a poor decision or because you've prevented a plan from being used (+0 does restrict the options, but is sometimes good for optimization, your use of +0 prevents an index to be used for document.id_document). Without seeing the plan, it is hard to see why your first query needs 30 seconds. I'd say that if document.id_agent_economic = 200 is pretty selective and articol_doc.id_document and cont.id_cont are also selective and indexed, then the first query ought to be pretty quick (with the possible exception of cont.repartitor being indexed an non-selective).

If you ran the second query after the first, caching may also affect the speed.

Normally, I find that using EXISTS or JOIN perform similarly (or rather, normally both of them perform well enough for me not to look for an alternative), though I'm still on Firebird 1.5.4. Far from all queries can replace an inner join by an EXISTS, e.g.

select id_cont_debit, document.id_agent_economic ...

would mean that you had to join to document. Also, using EXISTS gives the optimizer less choice. If 'less choice' means 'less poor options', then that is good, but if it means 'only poor options remaining' then that is very bad. Also, even though I generally like EXISTS, I find JOIN a little bit more readable. So, I use JOIN a bit more frequently than I use EXISTS.

HTH,
Set

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

Firebird 2.1

this query is executed in 30 seconds

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


this one, that does the same, and is executed in 0.9 seconds


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 = 200) and
(document.id_document = articol_doc.id_document)
)
)


If this is true, why ever using inner joins ?
This way any inner join can be replaced. Am I right ?

Tiberiu