Hi!

jbrugger wrote:

> This is a "never-ending" view (it just ends my patience :)

Trying to understand the plan on the view ends my patience and exceeds
my skills...

> CREATE VIEW VW_PRECIOS_REFERENCIA_UNITARIOS(
> CODIGO_MONODROGA,
> CODIGO_CONCENTRACION,
> CODIGO_FORMA_FARMACEUTICA,
> PRECIO_REFERENCIA_UNITARIO)
> AS
> SELECT VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_MONODROGA,
> VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_CONCENTRACION,
> VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_FORMA_FARMACEUTICA,
> case when precio_unitario_mas_vendido is null then
> (precio_unitario_maximo + precio_unitario_minimo)/2 else
> (precio_unitario_maximo + precio_unitario_minimo +
> precio_unitario_mas_vendido)/3 END
> FROM (VW_PRECIOS_UNITARIOS_MINIMOS INNER JOIN
> VW_PRECIOS_UNITARIOS_MAXIMOS ON
> (VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_FORMA_FARMACEUTICA =
> VW_PRECIOS_UNITARIOS_MAXIMOS.CODIGO_FORMA_FARMACEUTICA) AND
> (VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_CONCENTRACION =
> VW_PRECIOS_UNITARIOS_MAXIMOS.CODIGO_CONCENTRACION) AND
> (VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_MONODROGA =
> VW_PRECIOS_UNITARIOS_MAXIMOS.CODIGO_MONODROGA)) LEFT JOIN
> VW_PRECIOS_UNIT_MAS_DISPENSADOS ON
> (VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_FORMA_FARMACEUTICA =
> VW_PRECIOS_UNIT_MAS_DISPENSADOS.CODIGO_FORMA_FARMACEUTICA) AND
> (VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_CONCENTRACION =
> VW_PRECIOS_UNIT_MAS_DISPENSADOS.CODIGO_CONCENTRACION) AND
> (VW_PRECIOS_UNITARIOS_MINIMOS.CODIGO_MONODROGA =
> VW_PRECIOS_UNIT_MAS_DISPENSADOS.CODIGO_MONODROGA)
> ;

Can this be simplified to (I can't handle too long filenames):

SELECT MINI.CODIGO_MONODROGA,
MINI.CODIGO_CONCENTRACION,
MINI.CODIGO_FORMA_FARMACEUTICA,
case when DISP.precio_unitario_mas_vendido is null then
(MAXI.precio_unitario_maximo + MINI.precio_unitario_minimo)/2
else
(MAXI.precio_unitario_maximo + MINI.precio_unitario_minimo +
DISP.precio_unitario_mas_vendido)/3
END
FROM VW_PRECIOS_UNITARIOS_MINIMOS MINI
JOIN VW_PRECIOS_UNITARIOS_MAXIMOS MAXI
ON MINI.CODIGO_FORMA_FARMACEUTICA = MAXI.CODIGO_FORMA_FARMACEUTICA
AND MINI.CODIGO_CONCENTRACION = MAXI.CODIGO_CONCENTRACION
AND MINI.CODIGO_MONODROGA = MAXI.CODIGO_MONODROGA
LEFT JOIN VW_PRECIOS_UNIT_MAS_DISPENSADOS DISP
ON MINI.CODIGO_FORMA_FARMACEUTICA = DISP.CODIGO_FORMA_FARMACEUTICA
AND MINI.CODIGO_CONCENTRACION = DISP.CODIGO_CONCENTRACION
AND MINI.CODIGO_MONODROGA = DISP.CODIGO_MONODROGA
;

(note particularly the addition of aliases within the case construct, I
just guessed which table each field belong to)

> Plan:
> PLAN JOIN (MERGE (SORT (VW_PRECIOS_UNITARIOS_MAXIMOS
> VW_PRECIOS_UNITARIOS MEDICAMENTOS ORDER MEDICAMENTOS_IDX3 INDEX
> (MEDICAMENTOS_IDX1)), SORT (VW_PRECIOS_UNITARIOS_MINIMOS
> VW_PRECIOS_UNITARIOS MEDICAMENTOS ORDER MEDICAMENTOS_IDX3 INDEX
> (MEDICAMENTOS_IDX1))), JOIN (MERGE (SORT
> (VW_PRECIOS_UNIT_MAS_DISPENSADOS VW_TOTALES_DISPENSADOS_POR_MED
> CONSUMOS_FARMACIA ORDER FK_CONS_FARMACIA_MEDICAMENTOS), SORT (SORT
> (JOIN (VW_PRECIOS_UNIT_MAS_DISPENSADOS VW_MEDICAMENTOS_MAS_DISPENSADOS
> VW_TOTALES_DISPENSADOS_POR_MED CONSUMOS_FARMACIA ORDER
> FK_CONS_FARMACIA_MEDICAMENTOS, VW_PRECIOS_UNIT_MAS_DISPENSADOS
> VW_MEDICAMENTOS_MAS_DISPENSADOS MEDICAMENTOS INDEX
> (PK_MEDICAMENTOS))))), VW_PRECIOS_UNIT_MAS_DISPENSADOS MEDICAMENTOS
> INDEX (PK_MEDICAMENTOS)))

I understand that this view involves views on views, but apart from that
I'm stumped. Tell us the definition of all the involved views as well as
the selectivity on fields involved in the plan. I haven't yet any idea
of a possible solution to your problem, but my hunch is that it may
involve referencing less views. Views are good for simplifying
statements, but used exceedingly they may force Firebird to do too many
calculations.

By the way, which version of Firebird are you using?

Set

> All referenced views are pretty fast, except
> "VW_PRECIOS_UNIT_MAS_DISPENSADOS" which takes ~ 10 secs to complete.
>
> Data Tables have 250000 records (Consumos_Farmacia) and 35000 records
> (Medicamentos)
>
> Is there anything I could do to make this view usable?
>
> Tell me if you need more info about tables or views involved.
>
> Thanks!!!