Hi Arno !

Arno Brinkman wrote:

>Hi Alexandre,
>
>

...snip...

>I'm afraid you hit one of the limitations of the current optimizer (FB1.5).
>For some reason (bad estimated values) it has calculated wrong join order, but i
>can't explain why because when E is the first table all other relations are
>joined by unique indexes which do get a higher priority in the JOIN order
>calculation. Are there very small (number of records) tables and large tables in
>this query?
>
>

Very small yes, large no, this database are pretty small.

the record count for each table are:

TableName COUNT
=================================================== =====
Empresa 10373
Cliente 10309
SegmentoMercado 6
Vendedor 25
ListaPreco 1
Endereco 10735
Estado 28

>Forcing JOIN order can indeed be done by adding + 0 or using LEFT JOIN:
>
>SELECT
> *
>FROM
> Empresa e
> JOIN Cliente c ON (c.EmpresaId = e.EmpresaId + 0)
> JOIN ListaPreco lp ON (lp.ListaPrecoId = c.ListaPrecoId + 0)
> JOIN SegmentoMercado s ON (s.SegmentoMercadoId = c.SegmentoMercadoId + 0)
> JOIN Vendedor v ON (v.VendedorId = c.VendedorId + 0)
> JOIN Empresa et ON (et.EmpresaId = c.TransportadoraId + 0)
> JOIN Empresa ed ON (ed.EmpresaId = c.DistribuidorId + 0)
> LEFT JOIN Endereco en ON ((en.EmpresaID = c.EmpresaID) and (en.Tipo = 1))
> LEFT JOIN Estado es ON (es.EstadoID = en.EstadoID)
>WHERE
> e.Nome = 'Alexandre Benson Smith'
>
>

:-)
This gave me the exact plan I expected for the query
PLAN JOIN (JOIN (JOIN (E INDEX (AK_EMPRESA_NOME),C INDEX (PK_CLIENTE),ED
INDEX (PK_EMPRESA),LP INDEX (PK_LISTAPRECO),S INDEX
(PK_SEGMENTOMERCADO),V INDEX (PK_VENDEDOR),ET INDEX (PK_EMPRESA)),EN
INDEX (FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))

But I can't create my view this way, beacause I could filter on other
columns and the join order could be reversed for table Empresa and
Cliente for example:

I have made a test to filter records based on field Vendedor.Nome the
only way I get the plan I expected was modifying the order of the tables
like this:
select * from Vendedor v
join Cliente c on v.VendedorId + 0 = c.VendedorId
join Empresa e on e.EmpresaId = c.EmpresaId
join SegmentoMercado s on s.SegmentoMercadoId = c.SegmentoMercadoId
join Empresa et on et.EmpresaId = c.TransportadoraId
join Empresa ed on ed.EmpresaId = c.DistribuidorId
join ListaPreco lp on lp.ListaPrecoId = c.ListaPrecoId
left join Endereco en on (en.EmpresaID = c.EmpresaID) and
(en.Tipo = 1)
left join Estado es on es.EstadoID = en.EstadoID
where
V.Nome = 'Eliane'

I got this plan:
PLAN JOIN (JOIN (JOIN (V INDEX (AK_VENDEDOR_NOME),C INDEX
(FK_VENDEDOR_CLIENTE),LP INDEX (PK_LISTAPRECO),E INDEX (PK_EMPRESA),S
INDEX (PK_SEGMENTOMERCADO),ET INDEX (PK_EMPRESA),ED INDEX
(PK_EMPRESA)),EN INDEX (FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))

If I just modify the order of the tables I get a different plan, the
changed tables are all before the left join, so the optimizer could feel
free to try alternatives join orders.

select * from Cliente c
join Vendedor v on v.VendedorId + 0 = c.VendedorId
join Empresa e on e.EmpresaId = c.EmpresaId
join SegmentoMercado s on s.SegmentoMercadoId = c.SegmentoMercadoId
join Empresa et on et.EmpresaId = c.TransportadoraId
join Empresa ed on ed.EmpresaId = c.DistribuidorId
join ListaPreco lp on lp.ListaPrecoId = c.ListaPrecoId
left join Endereco en on (en.EmpresaID = c.EmpresaID) and
(en.Tipo = 1)
left join Estado es on es.EstadoID = en.EstadoID
where
V.Nome = 'Eliane'

PLAN JOIN (JOIN (JOIN (JOIN (LP NATURAL,C INDEX
(FK_CLIENTE_LISTAPRECO),ED INDEX (PK_EMPRESA),E INDEX (PK_EMPRESA),S
INDEX (PK_SEGMENTOMERCADO),ET INDEX (PK_EMPRESA)),V INDEX
(AK_VENDEDOR_NOME)),EN INDEX (FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))

Weird huh ?

>
>The JOIN order calculation has been changed in FB2 and should do a better job.
>When you've time you could test if your original query runs fine under FB2 (but
>you must restore your database in FB2 to benefit from the optimizer changes).
>
>
>

I have installed a FB2 version a long time ago and have played with it a
little bit, I tried to do the same on Friday when I get this
optimization problem, I have downloaded it from from the pre-release
area the version is 2.0.0.10468, but I cannot make it run in parallel
with FB 1.5, I have unzipped it on a different folder (c:\fb2) started
as application (fbserver -a) and tried to use gsec, isql, etc. I think I
am having problemns reagarding finding the security database, when I
runned the install_super.bat script I have found the security database,
but my FB 1.5.1 stopped to work, so I let run FB2 aside from that day,
but I will try for sure the same query on FB2 as far I make it run in
parallel with FB 1.5.1 . But FB2 questions are off-topic here.

>Regards,
>Arno Brinkman
>ABVisie
>
>

thank you very much !

--

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