Hi,

> - one table with 15000 records and about 50 fields. Indexed on pkidc
> (a guid = the pk) and a unique index on a 8 caracter field (dossier).
> - 2 views based on tables to get the sum of some values (PMTANTSUM_PT
> and PLANTX_HON_TOT).
> - a third view (FACT_SOLDE_PT) based on the 2 views that sum those
> values in a field nane "solde".
>
> Those 3 views run in a matter of miliseconds. So the problem is not
> there. And they return between 50 to 160 records.
>
> Here is the select that is so slow (field renamed and not put entirely):
> select p.f1, p.f2, p.f3,p.PKIDC, Plantx_hon_tot.HON_TOT,
> Fact_solde_pt.SOLDE, Pmtantsum_pt.MONTANT_ANTICIPE
> FROM PATIENT p
> LEFT JOIN FACT_SOLDE_PT Fact_solde_pt
> ON p.DOSSIER = Fact_solde_pt.DOSSIER
> LEFT JOIN PMTANTSUM_PT Pmtantsum_pt
> ON p.DOSSIER = Pmtantsum_pt.DOSSIER
> LEFT JOIN PLANTX_HON_TOT Plantx_hon_tot
> ON p.DOSSIER = Plantx_hon_tot.DOSSIER
> WHERE Fact_solde_pt.SOLDE > 0.00
>
> It seems when I look at the plan stat that FB goes through all the
> patient records many times - reading more than a million records.

Could you show us also the PLAN returned by the engine?
Looking at your query it seems very normal to me that indeed all records
from PATIENT are fetched, because you don't have any filter on this table.
If you don't need the LEFT JOINs then you can probably speed this query up.
I see for example in your WHERE clause that you've "Fact_solde_pt.SOLDE >
0.00" which will meant only the records which have at least a
"Fact_solde_pt.SOLDE" will be shown so a LEFT JOIN for that table isn't
needed.

What will be the performance and the PLAN with this query :

select
p.f1, p.f2, p.f3,p.PKIDC, Plantx_hon_tot.HON_TOT,
Fact_solde_pt.SOLDE, Pmtantsum_pt.MONTANT_ANTICIPE
FROM
FACT_SOLDE_PT Fact_solde_pt
JOIN PATIENT p
ON p.DOSSIER = Fact_solde_pt.DOSSIER
LEFT JOIN PMTANTSUM_PT Pmtantsum_pt
ON p.DOSSIER = Pmtantsum_pt.DOSSIER
LEFT JOIN PLANTX_HON_TOT Plantx_hon_tot
ON p.DOSSIER = Plantx_hon_tot.DOSSIER
WHERE
Fact_solde_pt.SOLDE > 0.00


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://80.126.130.81