Optimizar consulta cruze de tablas

[Toni]

Hola! Buenas a todos!

Quiero ver si me podeis hechar una mano para optimizar una consulta que realizado en Firebird 2.5. Esta consulta la realizo mediante un procedimiento almacenado que retorna el resultado. El objetivo de la consulta es fusionar los datos de dos tablas. Los datos de las tablas son una el stock y la otra el stock pendiente. Por fusionar quiero decir que salgan todos los datos coincidentes y los que no, en el caso de los coincidentes que salgan en un solo registro.

Un ejemplo del codigo es el siguiente:

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)
      FROM "StockTotalPorAlmacen" ST
      FULL OUTER 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
      BEGIN

      SUSPEND;

 END

La consulta funciona correctamente, pero en los casos en que en la tabla pendientes hay muchos registros se enlentece mucho, como un minuto..

Las dos tablas tienen un indice que coincide con la union de las dos tablas y segun parece lo usa, pero claro este tipo de union full outer multiplica el numero de accesos...

[Toni]

Nadie tiene una idea?

[Casimiro Notevi]

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 Notevi]

¿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..