Hi Arno,

Arno Brinkman wrote:

>Hi Alexandre,
>
><snip>
>
>I try to understand what really goes wrong.
>
>Could you post the PLANs returned by these queries:
>
>1)
>SELECT
> Count(*)
>FROM
> Empresa e
> JOIN Cliente c ON (c.EmpresaId = e.EmpresaId)
> JOIN ListaPreco lp ON (lp.ListaPrecoId = c.ListaPrecoId)
> JOIN SegmentoMercado s ON (s.SegmentoMercadoId = c.SegmentoMercadoId)
> JOIN Vendedor v ON (v.VendedorId = c.VendedorId)
> JOIN Empresa et ON (et.EmpresaId = c.TransportadoraId)
> JOIN Empresa ed ON (ed.EmpresaId = c.DistribuidorId)
> LEFT JOIN Endereco en ON ((en.EmpresaID = c.EmpresaID) and (en.Tipo = 1))
>WHERE
> e.Nome = 'Alexandre Benson Smith'
>
>

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

>2)
>SELECT
> Count(*)
>FROM
> Empresa e
> JOIN Cliente c ON (c.EmpresaId = e.EmpresaId)
> JOIN ListaPreco lp ON (lp.ListaPrecoId = c.ListaPrecoId)
> JOIN SegmentoMercado s ON (s.SegmentoMercadoId = c.SegmentoMercadoId)
> JOIN Vendedor v ON (v.VendedorId = c.VendedorId)
> JOIN Empresa et ON (et.EmpresaId = c.TransportadoraId)
> JOIN Empresa ed ON (ed.EmpresaId = c.DistribuidorId)
>WHERE
> e.Nome = 'Alexandre Benson Smith'
>
>

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

>>From your previous messages i understand that the LEFT JOIN is making the
>trouble for the join order, but when you use "+ 0" you can force that the index
>on "e.Nome" is used. That meant that the where comparison is delivered to the
>inner-joins, so that should be ok. Do you use the latest FB1.5.x version?
>
>

I use FB 1.5.1, I will try 1.5.2, but don't remember of great
optimization changes when I read the Release Notes.

Yes, if I use all inner joins the plan is ok.
if I put +0 on the majority of the index I force the index on e.nome to
be used, but as I wrote, this will solve the problem for this particular
search criteria, but I use this view to search for other columns too, so
if I put +0 on a lot of columns, when I search for on other fields I
will get a bad plan.

>Which page-size do you use and what is the result of this query:
>
>

Page Size 4096

>SELECT
> rf.RDB$RELATION_NAME,
> SUM(f.RDB$FIELD_LENGTH)
>FROM
> RDB$RELATION_FIELDS rf
> JOIN RDB$FIELDS f ON (f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE)
>WHERE
> rf.RDB$RELATION_NAME IN ('SEGMENTOMERCADO', 'VENDEDOR', 'LISTAPRECO',
>'ESTADO')
>GROUP BY
> rf.RDB$RELATION_NAME
>
>
>

RDB$RELATION_NAME SUM
================================ ====
ESTADO 50
LISTAPRECO 2092
SEGMENTOMERCADO 44
VENDEDOR 70


I have a varchar(2000) on table ListaPreco.

>off-topic: If you try FB2 don't use the same database as for FB1.5, but make a
>backup/restore.
>
>
>
>

Ok. I will try to make it run in parallel with FB 1.5 and report
privatelly the results.

>Regards,
>Arno Brinkman
>ABVisie
>
>

thanks

--

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