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'

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'

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?

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

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


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


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info