Luciano Rodrigues wrote:

>Hi,
>
>
>

...snip...

>This view get 248.538 reads from EMPRESAS table and
>165.692 reads from PARAMCONTROLE table, even I use any kind of result
>set limitation like "where", "first 1".
>
>I would like to know how this reads can be reduced.
>
>Best regards.
>
>
>

Luciano,

Try this:

select
.....

From
Produtos Pro
join Empresas Emp on (1=1)
Left Join ItemListaPreco Item on (Item.CodProduto = Pro.Codigo and Item.FlagPrincipal = 1)
Left Join ParamControle Par on (Par.Empresa = Emp.EmpresaEstoque)
Group By
Pro.Codigo, Emp.EmpresaEstoque, Item.CodLista, Item.FlagPrincipal, Item.aliqipi,
Item.PercDesconto1, Item.PercDesconto2, Item.PercDesconto3, Item.PercDesconto4,
Item.PercDesconto5


Another thing:
There is no relationship between "Produto and Empresa" or "Parametro and
Produto" or "Parametro and ItemListaPreco", did you note that I add a
(1=1) to the join clause of Produto and Empresa ?

In general I get better plans if I put last all the left joins,
sometimes the oposite....

The left join forces the optimizer to resolve the join until that place,
if you put it on the last, the optimizer can make more combinations, and
sometimes this results in better plans.

Could you explain what you want to get, if you prefer you can write in
portuguese to my e-mail.

If you really want the cartesian product between Produto and Empresa
(and then using a group by to consolidate the values), I think the
response time will be slow... Maybe in a Stored Procedure you could add
the logic to get the maximum values for each of the fields, and perform
the calcs inside it, just scanning the Produto table. I think the
performance will be screaming fast comparing to what you get with this view.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br