Hi Arno,

Arno Brinkman wrote:

>Hi Alexandre,
>
>
>
>You've 4 very small tables (SegmentoMercado, Vendedor, ListaPreco and Estado). I assume they use all
>1 data-page, that meant the optimizer estimate the number of records to be page-size/record-size. In
>your situation i guess (almost know for sure) that the estimated number is much higher as the real
>number of records and this could be the problem. Another thing is that you've more than 5 inner
>joins. The FB1.5 optimizer (and previous versions) is limited to not try all combinations possible,
>because that would be to expensive in time compared with actual execute/fetch.
>
>
>

understood.

MSSQL 6.5 (that I used a lot) has this limitation too, just 5 tables are
combined.

>Just out of curiousity what does this query (spliting so that we have 5 inner joins) ?
>
>SELECT
> *
>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)
> LEFT JOIN Endereco en ON ((en.EmpresaID = c.EmpresaID) and (en.Tipo = 1))
> JOIN Empresa et ON (et.EmpresaId = c.TransportadoraId)
> JOIN Empresa ed ON (ed.EmpresaId = c.DistribuidorId)
> LEFT JOIN Estado es ON (es.EstadoID = en.EstadoID)
>WHERE
> e.Nome = 'Alexandre Benson Smith'
>
>
>

I think this query would limit to 4 inner joins before the first left
right, so the optimizer could do a better job discovering the optimum
join order ?

I tried, but I got a bad plan too uses index FK_CLIENTE_LISTAPRECO that
as you saw has just 1 value :-(. I am almost sure Helen and Ann is
banging her head why I create a FK for a table with just so few values
and why I not make the RI rules with triggers :-) , I prefer declarative
constraints, as far as it does not bug me on bad plans or the sweep
time, until now I could live with it, when I find a query that uses that
bad index I add +0 to avoid this.

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

adding +0 to avoid this index lead me to another variant of the bad plan

JOIN ListaPreco lp ON (lp.ListaPrecoId = c.ListaPrecoId + 0)

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

>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