At 23:19 08/03/2004 +0100, you wrote:

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

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 (
ID,
ITEM,
NOTAFISCALID,
NOTAFISCALNUMERO,
NOTAFISCALEMISSAO,
NOTAFISCALEMPRESAID,
NOTAFISCALEMPRESARAZAOSOCIAL,
PEDIDOVENDAITEMID,
PEDIDOVENDAITEM,
PEDIDOVENDAID,
PEDIDOVENDANUMERO,
NATUREZAOPERACAOID,
NATUREZAOPERACAOCFOP,
NATUREZAOPERACAODESCRICAO,
PRODUTOID,
PRODUTOCODIGO,
PRODUTODESCRICAO,
PRODUTOCLASSIFICACAOFISCAL,
UNIDADESIGLA,
QUANTIDADE,
UNITARIO,
DESCONTO,
PRECOVENDA,
NOTAFISCALTOTALPRODUTO,
NOTAFISCALTOTAL,
MULTIEMPRESAID,
MULTIEMPRESACODIGO
) AS

select
nfi.NotaFiscalItemId,
nfi.Item,
nfi.NotaFiscalId,
nf.Numero,
nf.Emissao,
nf.EmpresaId,
nf.EmpresaRazaoSocial,
nfi.PedidoVendaItemId,
pvi.Item,
pv.PedidoVendaId,
pv.Numero,
nfi.NaturezaOperacaoID,
noa.cmp_cfop,
noa.Descricao,
nfi.ProdutoId,
nfi.ProdutoCodigo,
nfi.ProdutoDescricao,
nfi.ProdutoClassificacaoFiscal,
nfi.ProdutoUnidadeSigla,
nfi.Quantidade,
nfi.Unitario,
nfi.Desconto,
nfi.CMP_PrecoVenda,
nf.TotalProduto,
nf.cmp_Total,
nf.MultiEmpresaID,
me.Codigo
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)

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:

Those are small tables but the diference could be noted...

FB 1.5.0.4290
Windows XP
Pentium III 1Ghz 256 MB RAM
NotaFiscal has 18743 records
NotaFiscalItem has 53094 records
PedidoVenda has 16497 records
PedidoVendaItem has 51450 records

using with left join:
PLAN JOIN (JOIN (JOIN (ME NATURAL,NF INDEX (RDB$FOREIGN56),NFI INDEX
(RDB$FOREIGN170),NOA INDEX (RDB$PRIMARY43)),PVI INDEX (RDB$PRIMARY65)),PV
INDEX (RDB$PRIMARY63))
Time 3.09 sec

If I tried to avoid bad indices with
from
NotaFiscalItem nfi join
NotaFiscal nf on (nf.NotaFiscalId = nfi.NotaFiscalId) join
NaturezaOperacao noa on (noa.NaturezaOperacaoID =
nfi.NaturezaOperacaoID + 0) join
MultiEmpresa me on (nf.MultiEmpresaID + 0 =
me.MultiEmpresaID) left join
PedidoVendaItem pvi on (pvi.PedidoVendaItemId =
nfi.PedidoVendaItemId) left join
PedidoVenda pv on (pv.PedidoVendaId = pvi.PedidoVendaId)
where
PV.PedidoVendaID = 9000

PLAN JOIN (JOIN (JOIN (NFI NATURAL,NOA INDEX (RDB$PRIMARY43),NF INDEX
(RDB$PRIMARY47),ME INDEX (RDB$PRIMARY45)),PVI INDEX (RDB$PRIMARY65)),PV
INDEX (RDB$PRIMARY63))
Time 5.0328 sec (even worse)

using with join:
PLAN JOIN (PVI INDEX (RDB$FOREIGN214),PV INDEX (RDB$PRIMARY63),NFI INDEX
(RDB$FOREIGN171),NOA INDEX (RDB$PRIMARY43),NF INDEX (RDB$PRIMARY47),ME
INDEX (RDB$PRIMARY45))
Time: 0.001 sec.

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

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

>What happens on MSSQL if you put the where clause condition also in the ON
>search condition.
>
>At least you've added a new item on my todo list now ;-)

Good....

Any comments or sugestions ?

>Regards,
>Arno Brinkman
>ABVisie

See you !


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]