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.

> If I have a view with an outer join that is used across my application,
> sometimes the left join is ok, sometimes the where clause is just on the
> fields of the "right table" on a left join. I will ask you if the

optimizer

> can analize this and turn this query in a join instead of left join.
>
> See an example:

CREATE VIEW VNOTAFISCALITEM (
<...>
PEDIDOVENDAID,
<...>
) AS
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);

> This view join the invoice table (NotaFiscal) with the Sales Order Table
> (PedidoVenda), I could have Invoices without Sales Order, so I need a left
> join.
>
> Sometimes I use it with the following SQL (to show all Invoice Items
> related to some Sales Order):
>
> Select * from vNotaFiscalItem where PedidoVendaID = 9000
>
> this automatically could be identified by the optimizer to change the

"from

> clause" to:

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.

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

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

> This is the most common usage... So the speedy is ok in the most cases...
>
> 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 :

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


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