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