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]