Hi,

I am changing from MSSQL to Firebird 1.5. One query that runs in less
than a second on MSSQL takes more than 20 minutes with FB! Other
queries are usually as fast as or faster on FB. Both engines run on
the same Windows 2000 P4 2.4 1 gig ram. Here are the details:

- 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.

Any idea how to speed this up? If I cannot find a solution, I will
have to ( :-( ) look at another solution. Apart from this problem, I
love FB!

Patrice Drolet
Logiciels INFO-DATA inc.