--- In firebird-support@yahoogroups.com, "infodatainc"
There is no rush to optimize the query engine, because I succeeded to
optimize it on the client side.

So now I automatically create a inner join for all the tables that are
in the where clause and when I create the join section of the SELECT,
all the inner ones are put at the beginning. So all my queries now are
as fast as they used to be on MSSQL with the same results.

Thanks for the discussion. It helped a lot.

Patrice Drolet

<infodatainc@y...> wrote:

> --- In firebird-support@yahoogroups.com, Alexandre Benson Smith
> <iblist@t...> wrote:
>
> Hi all,
>
> It seems that this subject was of some interests to others. I am
> trying to optimize my query builder but it is not obvious... I cannot
> hard code the kind of join, for sometimes I need a left outer and
> other times, a join - all depending on the where clause.
>
> Ì am not a C++ developper. I have a lot of experience in VFP and about
> one year exp. in Java. I doubt that I can help optimize FB - I might
> cause more troubles... ;-)
>
> Do you think that this optimization could be in the next version of FB?
>
> Thanks,
>
> Patrice Drolet
>
> Do you think
>
> > At 01:37 09/03/2004 +0100, you wrote:
> >
> > >Hi,
> > >
> > > > >...snip...
> > > > >I can't say (without more information :-) what MSSQL did
> different on
> > >this
> > > > >query compared to FB, but i guess this :
> > > > >
> > > > >The MSSQL optimizer is clever enough to see that the LEFT JOIN
> has an
> > > > >condition in the where clause and turns it into a INNER JOIN.
> You can see
> > > > >this by looking at the Execution PLAN in the SQL Query Analyzer.
> > > >
> > > > Hi Arno,
> > > >
> > > > This is a thing that I wish to ask you, for a long time.... :)
> > >
> > >Suggestions, ideas are always welcome.
> >
> > Sometimes I just tend to over simplify the things... And my

suggestions

> > could be more time consuming to apply then make the queries smarter
> (i.e.
> > make the programmer smarter).... :)
> >
> >
> > >FROM
> > > NotaFiscalItem nfi
> > > JOIN NotaFiscal nf ON (nf.NotaFiscalId = nfi.NotaFiscalId)
> > > JOIN NaturezaOperacao noa ON (noa.NaturezaOperacaoID =
> > >nfi.NaturezaOperacaoID)
> > > JOIN MultiEmpresa me ON (nf.MultiEmpresaID = me.MultiEmpresaID)
> > > JOIN PedidoVendaItem pvi ON (pvi.PedidoVendaItemId =
> > >nfi.PedidoVendaItemId)
> > > JOIN PedidoVenda pv ON (pv.PedidoVendaId = pvi.PedidoVendaId);
> > >
> > >Indeed not only the LEFT JOIN from "PedidoVenda" should be changed,
> but also
> > >the LEFT JOIN from "PedidoVendaItem". Because there's a equality in
> the ON
> > >conditions for the "PedidoVenda" JOIN this is possible.
> >
> > Oh yeah... In fact the relation are between PedidoVendaItem (Sales
> Order
> > Item) and NotaFiscalItem (Invoice Item) because one invoice can have
> items
> > from diferent sales order, I get the NotaFiscal (Invoice) joining
> > PedidoVendaItem to NotaFiscalItem and NotaFiscalItem to NotaFiscal
> or is
> > this case I got PedidoVenda (Sales Order) joining NotaFiscalItem
> (Invoice
> > Item) to PedidoVendaItem (Sales Order Item) and PedidoVendaItem to
> > PedidoVenda (Sales Order).
> >
> >
> > > > I done some tests and the change from an outer join to a join
> make the
> > > > query MUCH FASTER !!! in FB 1.5 i got the following plan and

times:

> > >
> > >Yes, the JOIN order can change and every table could use a index in
> this
> > >example.
> > >
> >
> > The optimizer could know that
> >
> > from
> > InvoiceItem II left join
> > SalesOrderItem SOI on (SOI.SalesOrderItemID =
> II.SalesOrderItemID) left
> > join
> > SalesOrder SO on (SO.SalesORderID = SOI.SalesOrderID) join
> > Invoice I on (I.InvoiceID = II.InvoiceID)
> >
> > is the same as
> > from
> > InvoiceItem II join
> > Invoice I on (I.InvoiceID = II.InvoiceID) left join
> > SalesOrderItem SOI on (SOI.SalesOrderItemID =
> II.SalesOrderItemID) left
> > join
> > SalesOrder SO on (SO.SalesORderID = SOI.SalesOrderID)
> >
> > The optimizer could understand this are equal queries ???
> >
> >
> > > > I have did some observations that with FB 1.0 the query will run
> much
> > > > faster if you put the "left joins" after all "joins". In FB 1.5
> I did some
> > > > tests and the both return the same execution plan. but when I
> was writing
> > > > this message I did another test... If I change the "from

clause" to:

> > >
> > >FROM
> > > NotaFiscalItem nfi
> > > JOIN NotaFiscal nf ON (nf.NotaFiscalId = nfi.NotaFiscalId)
> > > LEFT JOIN PedidoVendaItem pvi ON (pvi.PedidoVendaItemId =
> > >nfi.PedidoVendaItemId)
> > > LEFT JOIN PedidoVenda pv ON (pv.PedidoVendaId = pvi.PedidoVendaId)
> > > JOIN NaturezaOperacao noa ON (noa.NaturezaOperacaoID =
> > >nfi.NaturezaOperacaoID)
> > > JOIN MultiEmpresa me ON (nf.MultiEmpresaID = me.MultiEmpresaID)
> > >WHERE
> > > PV.PedidoVendaID = 9000
> > >
> > > > PLAN JOIN (JOIN (JOIN (JOIN (NFI NATURAL,NF INDEX
> (RDB$PRIMARY47)),PVI
> > > > INDEX (RDB$PRIMARY65)),PV INDEX (RDB$PRIMARY63)),JOIN (NOA INDEX
> > > > (RDB$PRIMARY43),ME INDEX (RDB$PRIMARY45)))
> > > > Time: 4.05 sec
> > >
> > >
> > > > When I filter on fileds of the tables on the "left side" of the
> "left
> > >joins" :)
> > >
> > >FROM
> > > NotaFiscalItem nfi
> > > JOIN NotaFiscal nf ON (nf.NotaFiscalId = nfi.NotaFiscalId)
> > > LEFT JOIN PedidoVendaItem pvi ON (pvi.PedidoVendaItemId =
> > >nfi.PedidoVendaItemId)
> > > LEFT JOIN PedidoVenda pv ON (pv.PedidoVendaId = pvi.PedidoVendaId)
> > > JOIN NaturezaOperacao noa ON (noa.NaturezaOperacaoID =
> > >nfi.NaturezaOperacaoID)
> > > JOIN MultiEmpresa me ON (nf.MultiEmpresaID = me.MultiEmpresaID)
> > >WHERE
> > > NFI.NotaFiscalID = 9000
> > >
> > > > PLAN JOIN (JOIN (JOIN (JOIN (NFI INDEX (RDB$FOREIGN170),NF INDEX
> > > > (RDB$PRIMARY47)),PVI INDEX (RDB$PRIMARY65)),PV INDEX
> (RDB$PRIMARY63)),JOIN
> > > > (NOA INDEX (RDB$PRIMARY43),ME INDEX (RDB$PRIMARY45)))
> > > > Time: 0.001 sec
> > >
> > >I don't understand what you propose in the above two queries. Both
> results
> > >should be different or the same by coincedence?
> > >Is there any reference between NFI.NotaFiscalID and PV.PedidoVendaID?
> > >
> >
> > You are correct, the results SHOULD be diferent... Those are just
> examples...
> > In the first I Filter on PedidoVendaID to show all Invoice Items

from a

> > given Sales Order and on the second I filter on NotaFiscalID to show
> all
> > InvoiceItem from a given Invoice. I just give those examples to show
> that
> > if I filter on the tables on the left side the speedy is great...
> >
> > > > This is used too: (To filter on CustomerID (EmpresaID) field)
> > > > from
> > > > NotaFiscalItem nfi join
> > > > NotaFiscal nf on (nf.NotaFiscalId =
> nfi.NotaFiscalId)
> > > > left join
> > > > PedidoVendaItem pvi on (pvi.PedidoVendaItemId =
> > > > nfi.PedidoVendaItemId) left join
> > > > PedidoVenda pv on (pv.PedidoVendaId =
> > >pvi.PedidoVendaId)
> > > > join
> > > > NaturezaOperacao noa on (noa.NaturezaOperacaoID =
> > > > nfi.NaturezaOperacaoID) join
> > > > MultiEmpresa me on (nf.MultiEmpresaID =
> > >me.MultiEmpresaID)
> > > > where
> > > > NF.EmpresaID = 3000
> > > >
> > > > PLAN JOIN (JOIN (JOIN (JOIN (NFI NATURAL,NF INDEX
> (RDB$PRIMARY47)),PVI
> > > > INDEX (RDB$PRIMARY65)),PV INDEX (RDB$PRIMARY63)),JOIN (NOA INDEX
> > > > (RDB$PRIMARY43),ME INDEX (RDB$PRIMARY45)))
> > > > Time: 1.0623 sec
> > >
> > >I wonder if this is the most efficient PLAN, but it depends on how
> big is 1
> > >record for the NotaFiscalItem and for NotaFiscal. Assuming that
> there are
> > >many more NotaFiscalItem records than NotaFiscal (and looking back
> on your
> > >record-info i see it is ;-) then i expected NotaFiscal at the first
> place.
> > >In fact i expected this kind of PLAN :
> >
> > I can give full metadata for the involved tables and indices
> statistcs if
> > you wish... I just don't put it because the message was big

enough... :)

> >
> > >PLAN JOIN (JOIN (JOIN (JOIN (NF INDEX (RDB$PRIMARY47), NFI INDEX
> > >(RDB$FOREIGN170)),PVI
> > > INDEX (RDB$PRIMARY65)),PV INDEX (RDB$PRIMARY63)),JOIN (NOA INDEX
> > > (RDB$PRIMARY43),ME INDEX (RDB$PRIMARY45)))
> > >
> >
> > I tried the suggested plan... but I got
> > Index RDB$FOREIGN170 cannot be used in the specified plan
> >
> > select
> > PV.PEdidoVendaID
> > from
> > NotaFiscalItem nfi join
> > NotaFiscal nf on (nf.NotaFiscalId =
> nfi.NotaFiscalId) join
> > NaturezaOperacao noa on (noa.NaturezaOperacaoID =
> > nfi.NaturezaOperacaoID) join
> > MultiEmpresa me on (nf.MultiEmpresaID =
> > me.MultiEmpresaID) left join
> > PedidoVendaItem pvi on (pvi.PedidoVendaItemId =
> > nfi.PedidoVendaItemId) left join
> > PedidoVenda pv on (pv.PedidoVendaId =
> pvi.PedidoVendaId)
> > where
> > NF.EmpresaID = 3000
> > PLAN JOIN (JOIN (JOIN (JOIN (NF INDEX (RDB$PRIMARY47), NFI INDEX
> > (RDB$FOREIGN170)),PVI
> > INDEX (RDB$PRIMARY65)),PV INDEX (RDB$PRIMARY63)),JOIN (NOA INDEX
> > (RDB$PRIMARY43),ME INDEX (RDB$PRIMARY45)))
> >
> > Index RDB$FOREIGN170 cannot be used in the specified plan
> >
> > :(
> >
> > So could not see if it's faster
> >
> > I tried
> > select
> > *
> > from
> > NotaFiscalItem nfi join
> > NotaFiscal nf on (nf.NotaFiscalId =
> nfi.NotaFiscalId) join
> > NaturezaOperacao noa on (noa.NaturezaOperacaoID =
> > nfi.NaturezaOperacaoID) join
> > MultiEmpresa me on (nf.MultiEmpresaID =
> > me.MultiEmpresaID) left join
> > PedidoVendaItem pvi on (pvi.PedidoVendaItemId =
> > nfi.PedidoVendaItemId) left join
> > PedidoVenda pv on (pv.PedidoVendaId =
> pvi.PedidoVendaId)
> > where
> > NF.EmpresaID = 3000
> >
> > the plan is:
> > PLAN JOIN (JOIN (JOIN (ME NATURAL,NF INDEX (RDB$FOREIGN158),NFI INDEX
> > (RDB$FOREIGN170),NOA INDEX (RDB$PRIMARY43)),PVI INDEX
> (RDB$PRIMARY65)),PV
> > INDEX (RDB$PRIMARY63))
> > Time 0.007 sec
> >
> > because of left joins are in the end, plan is diferent and much more
> > faster... So I have to still put the left joins at the end in FB

1.5...

> >
> > In fact I don't understand well plans... I think it is confuse to
> read a
> > plan.... When I need to optimize a query I run it on

IBPlanAnalizer, I

> > understand better the plan looking at the generated plan tree...
> >
> > >Regards,
> > >Arno Brinkman
> > >ABVisie
> >
> > Thanks again for your time and help....
> >
> >
> > Alexandre Benson Smith
> > Development
> > THOR Software e Comercial Ltda.
> > Santo Andre - Sao Paulo - Brazil
> > www.thorsoftware.com.br
> >
> > ----------
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004
> >
> >
> > [Non-text portions of this message have been removed]