Optimizar consulta cruze de tablas

[Toni]

Nadie tiene una idea?

[Casimiro Noteví]

Es que es difícil poder ayudar si no tenemos unaa base de datos para probar, conocer los índices implicados, el plan, etc., además de conocer la estructura las tablas, etc.

[Toni]

Hola Casimiro,

Mira las tablas y sus indicies son los siguientes

Código SQL [-]
/* Tabla: StockPendientes */

CREATE TABLE "StockPendientes" (
    "idEmpresa" "tipoCodigoEmpresa" NOT NULL,
    "idAlmacen" "tipoCodigoAlmacen" NOT NULL COLLATE ISO8859_1,
    "idProducto" "tipoCodigoProducto" NOT NULL COLLATE ISO8859_1,
    "PendientesRecibir" "tipoUnidades",
    "PendientesFabrica" "tipoUnidades",
    "PendientesServir" "tipoUnidades",
    "StockReservado" "tipoUnidades",
    "StockEntrada" "tipoUnidades");



/* Primary keys definition */

ALTER TABLE "StockPendientes" ADD CONSTRAINT "PK_StockPendientes" PRIMARY KEY ("idEmpresa", "idAlmacen", "idProducto");


/* Indices definition */

CREATE UNIQUE INDEX "PK_StockPendientes" ON "StockPendientes" ("idEmpresa", "idAlmacen", "idProducto");

Código SQL [-]
/* Tabla: StockTotalPorAlmacen */

CREATE TABLE "StockTotalPorAlmacen" (
    "idEmpresa" "tipoCodigoEmpresa" NOT NULL,
    "idAlmacen" "tipoCodigoAlmacen" NOT NULL COLLATE ISO8859_1,
    "idProducto" "tipoCodigoProducto" NOT NULL COLLATE ISO8859_1,
    "QExistencias" "tipoUnidades");



/* Primary keys definition */

ALTER TABLE "StockTotalPorAlmacen" ADD CONSTRAINT "PK_StockTotalPorAlmacen" PRIMARY KEY ("idEmpresa", "idAlmacen", "idProducto");


/* Indices definition */

CREATE UNIQUE INDEX "PK_StockTotalPorAlmacen" ON "StockTotalPorAlmacen" ("idEmpresa", "idAlmacen", "idProducto");

Como el objetivo de este procedimiento es cruzar ambas tablas pues en los casos que hay un cierto volumen de registros la cosa se demora mucho y lo hace inoperativo. Por mucho puede ser unos >4000 registro en StockTotalPorAlmacen y >800 registros en StockPendientes.

El plan usado es que utilizada FB, pero segun puedo ver en el IBManager si utiliza la clave principal para las busquedas.

[Casimiro Noteví]

¿Y exactamente qué campos de qué tablas con qué filtros son los que necesitas?

[Toni]

Necesito el resultado total del cruce de ambas tablas.

Campos, los que se ven en el 'for select'

Código SQL [-]

       FOR SELECT 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,
                  coalesce(ST."QExistencias", 0),
                  cast(coalesce(F."PendientesFabrica",0) as integer),
                  coalesce(F."PendientesRecibir",0),
                  coalesce(F."PendientesServir",0),
                  (coalesce(ST."QExistencias",0))-(cast(coalesce(F."PendientesFabrica",0) as integer)),
                  coalesce(F."StockReservado",0),
                  coalesce(F."StockEntrada",0)

Filtros ninguno, cargo todo el resultado en un clientdataset.

En la mayoria de casos tal y como esta funciona con unos tiempos correctos. No suelen tener tantos registros en la tabla pendientes. Pero en un caso en concreto si y se vuelve tan lento que tal y como esta diseñado no es operativo. El motivo de la pregunta es ver si se puede optimizar la consulta tal cual esta planteado. Sino tendre que replantearlo todo tal y como esta diseñado..

[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

[Toni]

Hola Ecfisa,

Muchas gracias por tu aportacion. El procedimiento venia de una versión antigua de Firebird y hay cosas que se han quedado a la vieja usanza. Como tu bien me indicas los case que se pueden sustituir por la funcion coalesce(). Al igual que lo que me comentas que porque hay un casting 'PendietesFabrica', esto es algo historico del programa, que en el caso de esta columna habia datos con decimales y en esta consulta no interesaba que saliesen decimales.

De todas formas he integrado esos cambios en mi base de datos y no ha mejorado. No se porque motivo por lo que me indica el ems manager no me esta utilizando los indices que tiene ambas tablas.. Cuando estos coinciden con el orden de los campos en la clausula join.

Ya intente probar a forzar un plan en la consulta for select pero no me dejo el ems manager poner la sentencia.. no se si porque es una versión un tanto antigua para el Firebird 2.5 o que..