For those who are following this thread:

I have tested the original query (without any optimization tricks) on FB
1.5.2 and on FB 2.0

FB 1.5.2 gave me the same plan as 1.5.1

FB 2.0 gave me the optimum plan. So I think that a lot of queries will
be better optimized in FB 2.0, I will try to make other measures and
report back

I have done another test, a select on a view with aggregate column.

Very, very good ! :-)

FB 1.5 make the whole aggrate and applies the where clause to the having
clause, so the execution time are really slow on large tables, FB 2.0
instead perform the query with the where clause as it was not a view,
so the execution time is lightining fast

Select * from vPedidoVendaParcela where PedidoVendaID = 1500

FB 1.5.2 = 6249 ms. - number of reads (1 + 76298 + 76293 + 23916 + 23916)
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)))


FB 2.0 = 6 ms - number of reads (1 + 1 + 1 + 1 + 1)
PLAN SORT (JOIN (VPEDIDOVENDAPARCELA P INDEX (PK_PEDIDOVENDA),
VPEDIDOVENDAPARCELA I INDEX (FK_PEDIDOVENDAITEM_PEDIDOVENDA),
VPEDIDOVENDAPARCELA M INDEX (PK_MOEDA), VPEDIDOVENDAPARCELA ME INDEX
(PK_MULTIEMPRESA), VPEDIDOVENDAPARCELA PA INDEX
(AK_PEDIDOVENDAPARCELA_PARCELA)))

The query was runned a bunch of times so I am sure everything stays on
cache.

Impressive huh ? :-D

The other test was to see if outer joins was "promoted" to inner joins
if I put a where clause on a field of the outer table and the search
value was not null.

This still working the same way on both fb 1.5.x and 2.0.

1.5.2
PLAN JOIN (JOIN (JOIN (VCLIENTE LP NATURAL,VCLIENTE C INDEX
(FK_CLIENTE_LISTAPRECO),VCLIENTE ED INDEX (PK_EMPRESA),VCLIENTE E INDEX
(PK_EMPRESA),VCLIENTE S INDEX (PK_SEGMENTOMERCADO),VCLIENTE V INDEX
(PK_VENDEDOR),VCLIENTE ET INDEX (PK_EMPRESA)),VCLIENTE EN INDEX
(FK_EMPRESA_ENDERECO)),VCLIENTE ES INDEX (AK_ESTADO))

2.0
PLAN JOIN (JOIN (JOIN (VCLIENTE LP NATURAL, VCLIENTE C INDEX
(FK_CLIENTE_LISTAPRECO), VCLIENTE E INDEX (PK_EMPRESA), VCLIENTE ET
INDEX (PK_EMPRESA), VCLIENTE ED INDEX (PK_EMPRESA), VCLIENTE S INDEX
(PK_SEGMENTOMERCADO), VCLIENTE V INDEX (PK_VENDEDOR)), VCLIENTE EN INDEX
(FK_EMPRESA_ENDERECO)), VCLIENTE ES INDEX (AK_ESTADO))

Another test:
select
*
from
PedidoVenda PV join
Empresa E on (E.EmpresaID = PV.ClienteID) join
PedidoVendaItem PVI on (PVI.PedidoVendaID = PV.PedidoVendaID) left join
NotaFiscalItem NFI on (NFI.PedidoVendaItemID = PVI.PedidoVendaItemID)
join
Produto P on (P.ProdutoID = PVI.ProdutoID) join
PedidoVendaVendedor PVV on (PVV.PedidoVendaID = PV.PedidoVendaID) join
Vendedor V on (V.VendedorID = PVV.VendedorID)
where
PVI.PedidoVendaItemID = 2000

FB 1.5.2 - Execution Time 700ms
PLAN JOIN (JOIN (JOIN (PV NATURAL,PVI INDEX (PK_PEDIDOVENDAITEM),E INDEX
(PK_EMPRESA)),NFI INDEX (FK_NOTAFISCALITEM_PVITEM)),JOIN (V NATURAL,PVV
INDEX (AK_PEDIDOVENDAVENDEDOR),P INDEX (PK_PRODUTO)))

FB 2.0 Execution Time 150ms
PLAN JOIN (JOIN (JOIN (JOIN (PVI INDEX (PK_PEDIDOVENDAITEM), PV INDEX
(PK_PEDIDOVENDA), E INDEX (PK_EMPRESA)), NFI INDEX
(FK_NOTAFISCALITEM_PVITEM)), PVV INDEX (AK_PEDIDOVENDAVENDEDOR), P INDEX
(PK_PRODUTO)), V INDEX (PK_VENDEDOR))

Another one:
select
*
from
PedidoVenda PV join
Empresa E on (E.EmpresaID = PV.ClienteID) join
PedidoVendaItem PVI on (PVI.PedidoVendaID = PV.PedidoVendaID) left join
NotaFiscalItem NFI on (NFI.PedidoVendaItemID = PVI.PedidoVendaItemID)
left join
MovimentoEstoque ME on (ME.DocumentoID = NFI.NotaFiscalItemID and
DocumentoTipo = 1) join
Produto P on (P.ProdutoID = PVI.ProdutoID) join
PedidoVendaVendedor PVV on (PVV.PedidoVendaID = PV.PedidoVendaID) join
Vendedor V on (V.VendedorID = PVV.VendedorID)
where
PVI.PedidoVendaItemID = 2000

FB 1.5.2 - Execution Time 800ms
PLAN JOIN (JOIN (JOIN (JOIN (PV NATURAL,PVI INDEX (PK_PEDIDOVENDAITEM),E
INDEX (PK_EMPRESA)),NFI INDEX (FK_NOTAFISCALITEM_PVITEM)),ME INDEX
(SK_MOVIMENTOESTOQUE_DOCUMENTO)),JOIN (V NATURAL,PVV INDEX
(AK_PEDIDOVENDAVENDEDOR),P INDEX (PK_PRODUTO)))

FB 2.0 - Execution Time 177 ms
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (PVI INDEX (PK_PEDIDOVENDAITEM), PV
INDEX (PK_PEDIDOVENDA), E INDEX (PK_EMPRESA)), NFI INDEX
(FK_NOTAFISCALITEM_PVITEM)), ME INDEX (SK_MOVIMENTOESTOQUE_DOCUMENTO)),
PVV INDEX (AK_PEDIDOVENDAVENDEDOR), P INDEX (PK_PRODUTO)), V INDEX
(PK_VENDEDOR))

The last queries where the FB 1.5.2 uses a Natural Scan on table
PedidoVenda (PV) the diference betwen 1.5.2 and 2.0 should be very much
impressive if the table PedidoVenda was big enough (it has only 29k
rows), FB 1.5.2 do a lot of 29k reads on the involved tables, FB 2.0 did
just 1 read, so I think the speed on FB 2.0 tend to be "constant" even
if the table get very big, on the other hand on FB 1.5.2 the speed will
decrease as the table get bigger !

I will convert one database that has some tables with 1.5m rows, 800k
rows, 600k rows to FB 2.0 and will do some tests with it.

Helen, Should I post this type of message on fb-general ?

Congratulations Arno ! Keep up the excelent work !

see you !

--

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



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005