Hi Ann,

Ann W. Harrison wrote:

>Give it a try. Granted it's really ugly, but the whole path, index,
>pointer page, and data will all be in cache after the first subselect so
>it may be reasonably fast.
>
>Regards,
>
>
>Ann
>
>

You are right, it was really fast, almost as fast as with all inner
joins, in this case I have 8 reads on table endereco against 1 for the
same query with all inner joins.

The final query is

SELECT
c.EmpresaId,
e.Nome,
e.RazaoSocial,
e.CGC,
e.InscricaoEstadual,
c.UtilizacaoMaterial,
c.SegmentoMercadoId,
s.Descricao,
(Select en.Cidade from Endereco en where en.EmpresaID =
E.EmpresaID and Tipo = 1),
(Select en.CEP from Endereco en where en.EmpresaID = E.EmpresaID
and Tipo = 1),
(Select en.EstadoID from Endereco en where en.EmpresaID =
E.EmpresaID and Tipo = 1),
(Select es.UF from Estado es where EstadoID = (Select en.EstadoID
from Endereco en where en.EmpresaID = E.EmpresaID and Tipo = 1)),
(Select en.Telefone from Endereco en where en.EmpresaID =
E.EmpresaID and Tipo = 1),
(Select en.Fax from Endereco en where en.EmpresaID = E.EmpresaID
and Tipo = 1),
c.VendedorId,
v.Nome,
c.VendedorComissao,
c.TransportadoraId,
et.Nome,
c.DistribuidorId,
ed.Nome,
c.ListaPrecoId,
lp.Descricao
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))
PLAN (EN INDEX (FK_EMPRESA_ENDERECO))
PLAN (EN INDEX (FK_EMPRESA_ENDERECO))
PLAN (EN INDEX (FK_EMPRESA_ENDERECO))
PLAN (ES INDEX (PK_ESTADO))
PLAN (EN INDEX (FK_EMPRESA_ENDERECO))
PLAN (EN INDEX (FK_EMPRESA_ENDERECO))
PLAN (EN INDEX (FK_EMPRESA_ENDERECO))

I think I should take this approach more times when I need outer joins,
I had 2 reasons to avoid this:

1.) I thought it will be too expensive to make a bunch of subselects on
the same table (you prooved I am wrong here).
2.) Taking this view as a sample:
create view vABC (a1, b2, c3) as
select
a1, b2, c3
from
a
join b on a1 = b1
left join c on c2 = b2

then I use this query

select * from vABC where a1 = 12

the plan will be made with left joins to table C

but if I make this query
select * from vABC where c3 = 12

The optimizer could conver the outer join to an inner join, and make
this query be a lot more faster. IIRC Arno told it is not too difficult
to implement.

Using my original query as a sample:

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)
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))
LEFT JOIN Estado es ON (es.EstadoID = en.EstadoID)
WHERE
es.UF = 'AM'

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

now using all inner joins (the future optimization that could be done
internally by the optimizer)

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)
JOIN Empresa et ON (et.EmpresaId = c.TransportadoraId)
JOIN Empresa ed ON (ed.EmpresaId = c.DistribuidorId)
JOIN Endereco en ON ((en.EmpresaID = c.EmpresaID) and (en.Tipo = 1))
JOIN Estado es ON (es.EstadoID = en.EstadoID)
WHERE
es.UF = 'AM'

PLAN JOIN (ES INDEX (AK_ESTADO),EN INDEX (FK_ESTADO_ENDERECO),C INDEX
(PK_CLIENTE),ED INDEX (PK_EMPRESA),E INDEX (PK_EMPRESA),LP INDEX
(PK_LISTAPRECO),S INDEX (PK_SEGMENTOMERCADO),V INDEX (PK_VENDEDOR),ET
INDEX (PK_EMPRESA))

far better uh ?

now using the query with sub-selects

PLAN JOIN (VCLIENTE2 LP NATURAL,VCLIENTE2 C INDEX
(FK_CLIENTE_LISTAPRECO),VCLIENTE2 ED INDEX (PK_EMPRESA),VCLIENTE2 E
INDEX (PK_EMPRESA),VCLIENTE2 S INDEX (PK_SEGMENTOMERCADO),VCLIENTE2 V
INDEX (PK_VENDEDOR),VCLIENTE2 ET INDEX (PK_EMPRESA))
PLAN (ENDERECO

the plan stopped after ENDERECO and is incomplete :-( (tried on
IBPlanAlyzer, IBConsole and ISQL)

But I get 31K reads on one table, 20k on another, 10k on other two, with
the all inner join I got 75, 28, 25 (the total number of fetched records
is 25).

As always, a choice must be made, I think I will use the sub-select
approach, and when the outer joins are better handled and/or the outer
joins is promoted to inner joins if the where clause contains a field
from the outer table and the searched value is not null I will try the
ortodox approach of outer joins.

thanks again !

--

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