Oracle!

Got it in about 1 sec!

Thanks a lot. Now I will have to work on my SQL builder tool. :-(

Can you explain why the previous query was that fast on MSSQL and not
on FB? I can see your point but it seems that MSSQL was able to
optimize the query while FB needed more "precautions".

Patrice

--- In firebird-support@yahoogroups.com, "Arno Brinkman"
<firebird@a...> wrote:

> 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