Optimizar consulta cruze de tablas

[ecfisa]

Hola Toni.

Algunos detalles y dudas.

Las líneas:

Código SQL [-]
   case  when ST."idEmpresa" is null then F."idEmpresa" else ST."idEmpresa" end,
   case  when ST."idAlmacen" is null then F."idAlmacen" else ST."idAlmacen" end,
   case  when ST."idProducto" is null then F."idProducto" else ST."idProducto" end,

Se pueden escribir con el mismo efecto de este modo:

Código SQL [-]
  COALESCE(ST.IDEMPRESA, F.IDEMPRESA),
  COALESCE(ST.IDALMACEN, F.IDALMACEN),
  COALESCE(ST.IDPRODUCTO, F.IDPRODUCTO),

Si en la definición de la tabla las columnas

Código SQL [-]
  ...
  "PendientesRecibir" "tipoUnidades",
  "PendientesFabrica" "tipoUnidades",
  "PendientesServir" "tipoUnidades",
  "StockReservado" "tipoUnidades",
  "StockEntrada" "tipoUnidades");

estan todas declaradas como de tipoUnidades, ¿Por que el cast en esta columna y no en las otras ?

Código SQL [-]
  cast(coalesce(F."PendientesFabrica",0) as integer),

Por si te llegara a servir de ayuda, hice una prueba con 5000 registros en STOCKTOTALPORALMACEN y 1000 registros en STOCKPENDIENTES los cualles llené aleatoriamente y que te adjunto:

Código SQL [-]
SET TERM ^;

CREATE OR ALTER PROCEDURE SP_STOCK
RETURNS (
  IDEMPRESA         TIPOCODIGOEMPRESA,
  IDALMACEN         TIPOCODIGOALMACEN,
  IDPRODUCTO        TIPOCODIGOPRODUCTO,
  QEXISTENCIAS      TIPOUNIDADES,
  PENDIENTESFABRICA TIPOUNIDADES,
  PENDIENTESRECIBIR TIPOUNIDADES,
  PENDIENTESSERVIR  TIPOUNIDADES,
  STOCKDISPONIBLE   TIPOUNIDADES,
  STOCKRESERVADO    TIPOUNIDADES,
  STOCKENTRADA      TIPOUNIDADES)
AS
BEGIN
  FOR SELECT
    COALESCE(ST.IDEMPRESA, F.IDEMPRESA),
    COALESCE(ST.IDALMACEN, F.IDALMACEN),
    COALESCE(ST.IDPRODUCTO, F.IDPRODUCTO),
    COALESCE(ST.QEXISTENCIAS, 0),
    COALESCE(F.PENDIENTESFABRICA, 0),
    COALESCE(F.PENDIENTESRECIBIR, 0),
    COALESCE(F.PENDIENTESSERVIR, 0),
    COALESCE(ST.QEXISTENCIAS,0) - COALESCE(F.PENDIENTESFABRICA,0),
    COALESCE(F.STOCKRESERVADO,0),
    COALESCE(F.STOCKRESERVADO,0)
  FROM STOCKTOTALPORALMACEN ST
  FULL JOIN STOCKPENDIENTES F ON F.IDEMPRESA = ST.IDEMPRESA
   AND F.IDALMACEN = ST.IDALMACEN AND F.IDPRODUCTO = ST.IDPRODUCTO
  INTO
    :IDEMPRESA,
    :IDALMACEN,
    :IDPRODUCTO,
    :QEXISTENCIAS,
    :PENDIENTESFABRICA,
    :PENDIENTESRECIBIR,
    :PENDIENTESSERVIR,
    :STOCKDISPONIBLE,
    :STOCKRESERVADO,
    :STOCKENTRADA
  DO
    SUSPEND;
END^

SET TERM ;^

Llamando al procedimiento con FlameRobin:

Código SQL [-]
SELECT IDEMPRESA,
       IDALMACEN,
       IDPRODUCTO,
       QEXISTENCIAS,
       PENDIENTESFABRICA,
       PENDIENTESRECIBIR,
       PENDIENTESSERVIR,
       STOCKDISPONIBLE,
       STOCKRESERVADO,
       STOCKENTRADA
FROM SP_STOCK

En varias ejecuciones obtuve este resultado por exceso:

Executing...
Done.
2895048 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 1425424 seq.
Delta memory: 0 bytes.
Total execution time: 0.941s
Script execution finished.

Como ignoraba la declaración de los dominios TIPOCODIGOEMPRESA, TIPOCODIGOALMACEN, TIPOCODIGOPRODUCTO y TIPOUNIDADES, definí para la prueba los 3 primeros como CHAR(4) y el último como INTEGER y solamente hice las pruebas de forma local.

Saludos