Alexandre:

I fixed it. But I´ve to drop the view and put all together in the same
query (same fecha index now it makes it faster, when using the view, it
doesn't)

Final version:

select coalesce(sum(cantidad),0) FROM FACTURAS INNER JOIN
ITEMS_FACTURAS ON (FACTURAS.SUCURSAL = ITEMS_FACTURAS.SUCURSAL) and
(FACTURAS.ID_FACTURA = ITEMS_FACTURAS.ID_FACTURA) where
facturas.sucursal = :sucursal and fecha > :fecha_desde and
codigo_producto = :codigo_producto and facturas.anulada = 0

Plan:
PLAN (PRODUCTOS INDEX (PK_PRODUCTOS))JOIN (ITEMS_FACTURAS INDEX
(FK_ITEMS_FACTURAS_PRODUCTOS), FACTURAS INDEX (PK_FACTURAS))(PRODUCTOS
INDEX (PRODUCTOS_IDX2))JOIN (ITEMS_FACTURAS INDEX
(FK_ITEMS_FACTURAS_PRODUCTOS), FACTURAS INDEX (PK_FACTURAS))(PRODUCTOS

Thanks for your time!!

Have a nice day.

Alexandre Benson Smith escribió:

> Jorge Andres Brugger wrote:
>
>> Alexandre:
>>
>> Thanks for your answer.
>>
>> Really, what I do later is:
>>
>> select sum(cantidad) from vw_items_facturas_no_anuladas where sucursal = 1
>> and fecha > '2007-04-30' and codigo_producto = 31344
>>
>>
>
> What is the plan for this single query ? The one you sent on the first
> message ?
>
> Do you have indices defined for:
> Items_Factura.Codigo_Producto
> Facturas.Fecha
> ?
>
> If not, define it, I hope it would improve a lot your query.
>
>
>> (called from inside SP).
>>
>> It tooks aprox 15 secs for each "codigo_producto", and I've about 35000 ...
>> so it takes too much.
>>
>> Adding a fecha index doesnt make it faster.
>>
>>
>
> Strange, what the plan before and after adding that index ? The same ?
>
>
>> If you have some other suggestion, I'll glad to read them.
>>
>>
>
> My suggestion is to add the two indices above, you told me Fecha was
> indexed and doesn't help, I think it's strange.
>
> Try to set the indices statistics of all indices on the two tables.
>
>
>> Regards
>>
>>
>
> see you !
>
>

--
Jorge Andrés Brugger
Informática
DASU - Obra Social del Personal de la Universidad Nacional de la Patagonia
Comodoro Rivadavia, Chubut, Argentina
Teléfono (0297) 446-4444 int. 103
Correo electrónico: jbrugger@...
Website: www.dasu.com.ar



[Non-text portions of this message have been removed]