Luciano R. Machado wrote:

>Hi,
>
>There´s the following view:
>
>CREATE VIEW VIEWPEDIDOS(
> EMPRESA,
> CODPEDIDO,
> VALORCUSTOFOR,
> VALORCUSTOULT,
> VALORCUSTOMED,
> VALORCUSTOSER)
>AS
>select Ped.Empresa, Ped.CodPedido,
> coalesce(sum(case when Mov.CodProduto <> 8888888
> then (ValorCustoFor * FatorRedCusto) * QuantAtendida
> else 0 end),0) as ValorCustoFor
>from Pedidos Ped
>left join MovEstoque Mov on Mov.Empresa = Ped.Empresa
> and Mov.CodPedido = Ped.CodPedido
> and Mov.NumLancamento <> 0
> and Ped.CodPedido <> 0
>group by Ped.Empresa, Ped.CodPedido
>;
>
>The table MovEstoque has 437000 records and Pedidos 121000.
>When it runs by:
>
>Select * from ViewPedidos
> where Empresa = 4
> and CodPedido = 100
>
>the both tables get read at all of their records, what´s taking
>about 2 minutes to complete the command.
>
>It´s basically "caused" by the Group By / Sum use, but shouldn´t
>it use the where filter instead of read all records to return just
>one record??
>
>Best Regards.
>
>
>

Luciano,

Views with agregates could not use indexes, since you could be searching
by the summary fields ( I think this will be enhaced in future versions)
but now, you will have to deal with it.

What you could do:
Maintain the totals on Table Pedidos with triggers
Use the select to the tables direct instead of use view, in this case
you will get a better performance, since the indices will be used.

see you !

--

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