Guys,

Environment: FB 1.5.1 on windows.

I have a problem with the following view:

CREATE VIEW VPEDIDOVENDAPARCELA (
PEDIDOVENDAID,
PEDIDOVENDANUMERO,
PEDIDOVENDAITEMDATAENTREGA,
PEDIDOVENDAITEMTOTAL,
MOEDASIGLA,
ID,
PARCELA,
DESCRICAO,
PRAZODIAS,
PORCENTUALPARCELA,
QUITADA,
MULTIEMPRESAID,
MULTIEMPRESACODIGO
) AS

select
p.PedidoVendaId,
p.Numero,
i.DataEntrega,
SUM(i.cmp_PrecoVenda),
m.Sigla,
pa.PedidoVendaParcelaId,
pa.Parcela,
pa.Descricao,
pa.PrazoDias,
pa.PorcentualParcela,
pa.Quitada,
p.MultiEmpresaID,
me.Codigo
from
PedidoVenda p join
Moeda m on (m.MoedaId = p.MoedaId) join
PedidoVendaItem i on (i.PedidoVendaId = p.PedidoVendaId) join
PedidoVendaParcela pa on (pa.PedidoVendaId = p.PedidoVendaId) join
MultiEmpresa me on (me.MultiEmpresaID = p.MultiEmpresaID)
group by
p.PedidoVendaId,
p.Numero,
i.DataEntrega,
m.Sigla,
pa.PedidoVendaParcelaId,
pa.Parcela,
pa.Descricao,
pa.PrazoDias,
pa.PorcentualParcela,
pa.Quitada,
p.MultiEmpresaID,
me.Codigo

I use to do the following with it:

Select * from vPedidoVendaParcela where PedidoVendaID = 10

This query are slow, about 8 seconds, returned me 2 rows, the plan is
PLAN SORT (JOIN (VPEDIDOVENDAPARCELA ME NATURAL,VPEDIDOVENDAPARCELA P
INDEX (FK_PEDIDOVENDA_MEMPRESA),VPEDIDOVENDAPARCELA M INDEX
(PK_MOEDA),VPEDIDOVENDAPARCELA I INDEX
(FK_PEDIDOVENDAITEM_PEDIDOVENDA),VPEDIDOVENDAPARCELA PA INDEX
(AK_PEDIDOVENDAPARCELA_PARCELA)))

I have figured I have bad indexes due to FK's

So i changed my view to
recreate VIEW VPEDIDOVENDAPARCELA (
PEDIDOVENDAID,
PEDIDOVENDANUMERO,
PEDIDOVENDAITEMDATAENTREGA,
PEDIDOVENDAITEMTOTAL,
MOEDASIGLA,
ID,
PARCELA,
DESCRICAO,
PRAZODIAS,
PORCENTUALPARCELA,
QUITADA,
MULTIEMPRESAID,
MULTIEMPRESACODIGO
) AS select
p.PedidoVendaId,
p.Numero,
i.DataEntrega,
SUM(i.cmp_PrecoVenda),
m.Sigla,
pa.PedidoVendaParcelaId,
pa.Parcela,
pa.Descricao,
pa.PrazoDias,
pa.PorcentualParcela,
pa.Quitada,
p.MultiEmpresaID,
me.Codigo
from
PedidoVenda p join
Moeda m on (m.MoedaId = p.MoedaId + 0) join
PedidoVendaItem i on (i.PedidoVendaId = p.PedidoVendaId) join
PedidoVendaParcela pa on (pa.PedidoVendaId = p.PedidoVendaId) join
MultiEmpresa me on (me.MultiEmpresaID = p.MultiEmpresaID + 0)
group by
p.PedidoVendaId,
p.Numero,
i.DataEntrega,
m.Sigla,
pa.PedidoVendaParcelaId,
pa.Parcela,
pa.Descricao,
pa.PrazoDias,
pa.PorcentualParcela,
pa.Quitada,
p.MultiEmpresaID,
me.Codigo

Select * from vPedidoVendaParcela where PedidoVendaID = 10

This query are still slow, about the same time, the plan is

PLAN SORT (JOIN (VPEDIDOVENDAPARCELA P NATURAL,VPEDIDOVENDAPARCELA ME
INDEX (PK_MULTIEMPRESA),VPEDIDOVENDAPARCELA M INDEX
(PK_MOEDA),VPEDIDOVENDAPARCELA I INDEX
(FK_PEDIDOVENDAITEM_PEDIDOVENDA),VPEDIDOVENDAPARCELA PA INDEX
(AK_PEDIDOVENDAPARCELA_PARCELA)))

if I do this:

select
p.PedidoVendaId,
p.Numero,
i.DataEntrega,
SUM(i.cmp_PrecoVenda),
m.Sigla,
pa.PedidoVendaParcelaId,
pa.Parcela,
pa.Descricao,
pa.PrazoDias,
pa.PorcentualParcela,
pa.Quitada,
p.MultiEmpresaID,
me.Codigo
from
PedidoVenda p join
Moeda m on (m.MoedaId = p.MoedaId) join
PedidoVendaItem i on (i.PedidoVendaId = p.PedidoVendaId) join
PedidoVendaParcela pa on (pa.PedidoVendaId = p.PedidoVendaId) join
MultiEmpresa me on (me.MultiEmpresaID = p.MultiEmpresaID)
where
P.PedidoVendaID = 10
group by
p.PedidoVendaId,
p.Numero,
i.DataEntrega,
m.Sigla,
pa.PedidoVendaParcelaId,
pa.Parcela,
pa.Descricao,
pa.PrazoDias,
pa.PorcentualParcela,
pa.Quitada,
p.MultiEmpresaID,
me.Codigo

what should be what I expected that my select on view was "converted "
the plan is
PLAN SORT (JOIN (P INDEX (PK_PEDIDOVENDA),ME INDEX (PK_MULTIEMPRESA),I
INDEX (FK_PEDIDOVENDAITEM_PEDIDOVENDA),PA INDEX
(AK_PEDIDOVENDAPARCELA_PARCELA),M INDEX (PK_MOEDA)))
execution time = 6ms

I tried this too:

recreate VIEW VPEDIDOVENDAPARCELA (
PEDIDOVENDAID,
PEDIDOVENDANUMERO,
PEDIDOVENDAITEMDATAENTREGA,
PEDIDOVENDAITEMTOTAL,
MOEDASIGLA,
ID,
PARCELA,
DESCRICAO,
PRAZODIAS,
PORCENTUALPARCELA,
QUITADA,
MULTIEMPRESAID,
MULTIEMPRESACODIGO
) AS select
p.PedidoVendaId,
p.Numero,
i.DataEntrega,
SUM(i.cmp_PrecoVenda),
m.Sigla,
pa.PedidoVendaParcelaId,
pa.Parcela,
pa.Descricao,
pa.PrazoDias,
pa.PorcentualParcela,
pa.Quitada,
p.MultiEmpresaID,
me.Codigo
from
PedidoVenda p left join
Moeda m on (m.MoedaId = p.MoedaId) join
PedidoVendaItem i on (i.PedidoVendaId = p.PedidoVendaId) join
PedidoVendaParcela pa on (pa.PedidoVendaId = p.PedidoVendaId) join
MultiEmpresa me on (me.MultiEmpresaID = p.MultiEmpresaID)
group by
p.PedidoVendaId,
p.Numero,
i.DataEntrega,
m.Sigla,
pa.PedidoVendaParcelaId,
pa.Parcela,
pa.Descricao,
pa.PrazoDias,
pa.PorcentualParcela,
pa.Quitada,
p.MultiEmpresaID,
me.Codigo

I hoped the left join will force "PedidoVenda P" to be the first
searched table, and that this will enable the use of the PK index on
PedidoVenda.PedidoVendaID

the plan is:
PLAN SORT (JOIN (JOIN (VPEDIDOVENDAPARCELA P NATURAL,VPEDIDOVENDAPARCELA
M INDEX (PK_MOEDA)),JOIN (VPEDIDOVENDAPARCELA I INDEX
(FK_PEDIDOVENDAITEM_PEDIDOVENDA),VPEDIDOVENDAPARCELA ME INDEX
(PK_MULTIEMPRESA),VPEDIDOVENDAPARCELA PA INDEX
(AK_PEDIDOVENDAPARCELA_PARCELA))))

execution time around 9secs.

What I can think is that FB is unable to use an index to filter a view
with aggregate query, I think the engine should perform the full table
aggragate and then filter it.

Any ideas ?

Thank you very much !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br