Club Delphi  
    FTP   CCD     Buscar   Trucos   Trabajo   Foros

Retroceder   Foros Club Delphi > Bases de datos > Firebird e Interbase
Registrarse FAQ Miembros Calendario Guía de estilo Buscar Temas de Hoy Marcar Foros Como Leídos

Respuesta
 
Herramientas Buscar en Tema Desplegado
  #1  
Antiguo 10-09-2014
Toni Toni is offline
Miembro
 
Registrado: may 2003
Ubicación: Barcelona - España
Posts: 355
Poder: 15
Toni Va por buen camino
Smile Optimizar consulta cruze de tablas

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...
__________________
Saludos,

Bitman

Última edición por Toni fecha: 10-09-2014 a las 14:30:11.
Responder Con Cita
  #2  
Antiguo 19-09-2014
Toni Toni is offline
Miembro
 
Registrado: may 2003
Ubicación: Barcelona - España
Posts: 355
Poder: 15
Toni Va por buen camino
Nadie tiene una idea?
__________________
Saludos,

Bitman
Responder Con Cita
  #3  
Antiguo 19-09-2014
Avatar de Casimiro Notevi
Casimiro Notevi Casimiro Notevi is offline
Moderador
 
Registrado: sep 2004
Ubicación: En algún lugar.
Posts: 26.983
Poder: 10
Casimiro Notevi Tiene un aura espectacularCasimiro Notevi Tiene un aura espectacular
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.
Responder Con Cita
  #4  
Antiguo 19-09-2014
Toni Toni is offline
Miembro
 
Registrado: may 2003
Ubicación: Barcelona - España
Posts: 355
Poder: 15
Toni Va por buen camino
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.
__________________
Saludos,

Bitman

Última edición por Toni fecha: 19-09-2014 a las 17:00:03.
Responder Con Cita
  #5  
Antiguo 19-09-2014
Avatar de Casimiro Notevi
Casimiro Notevi Casimiro Notevi is offline
Moderador
 
Registrado: sep 2004
Ubicación: En algún lugar.
Posts: 26.983
Poder: 10
Casimiro Notevi Tiene un aura espectacularCasimiro Notevi Tiene un aura espectacular
¿Y exactamente qué campos de qué tablas con qué filtros son los que necesitas?
Responder Con Cita
  #6  
Antiguo 19-09-2014
Toni Toni is offline
Miembro
 
Registrado: may 2003
Ubicación: Barcelona - España
Posts: 355
Poder: 15
Toni Va por buen camino
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..
__________________
Saludos,

Bitman

Última edición por Toni fecha: 19-09-2014 a las 17:07:05.
Responder Con Cita
  #7  
Antiguo 21-09-2014
Avatar de ecfisa
ecfisa ecfisa is offline
Moderador
 
Registrado: dic 2005
Ubicación: Tres Arroyos, Argentina
Posts: 9.648
Poder: 27
ecfisa es conocido por todosecfisa es conocido por todosecfisa es conocido por todosecfisa es conocido por todosecfisa es conocido por todosecfisa es conocido por todos
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:
Cita:
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
__________________
Daniel Didriksen

Guía de estilo - Uso de las etiquetas - La otra guía de estilo ....
Responder Con Cita
  #8  
Antiguo 22-09-2014
Toni Toni is offline
Miembro
 
Registrado: may 2003
Ubicación: Barcelona - España
Posts: 355
Poder: 15
Toni Va por buen camino
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..
__________________
Saludos,

Bitman
Responder Con Cita
  #9  
Antiguo 22-09-2014
Avatar de Casimiro Notevi
Casimiro Notevi Casimiro Notevi is offline
Moderador
 
Registrado: sep 2004
Ubicación: En algún lugar.
Posts: 26.983
Poder: 10
Casimiro Notevi Tiene un aura espectacularCasimiro Notevi Tiene un aura espectacular
¿Desde cuando no haces un backup/restore para eliminar la basura?
Responder Con Cita
  #10  
Antiguo 22-09-2014
Toni Toni is offline
Miembro
 
Registrado: may 2003
Ubicación: Barcelona - España
Posts: 355
Poder: 15
Toni Va por buen camino
Con la base de datos que realizado las pruebas le hice un backup/restore. y es con esta que me va lento. Si en lugar de utilizar un cruce de tabla tipo full join, utilizase una tabla temporal con todos los campos que se quiere retornar y en dicha tabla copiase primero todos los registros de "StockTotalPorAlmacen" y en segundo lugar utilzase un sentencia insert or update para la otra tabla, no seria mucho mas rapido? Pero seguramente podria tener problemas de acceso a la tabla temporal si pide la consulta otro usuario, no?
__________________
Saludos,

Bitman
Responder Con Cita
  #11  
Antiguo 22-09-2014
Avatar de Casimiro Notevi
Casimiro Notevi Casimiro Notevi is offline
Moderador
 
Registrado: sep 2004
Ubicación: En algún lugar.
Posts: 26.983
Poder: 10
Casimiro Notevi Tiene un aura espectacularCasimiro Notevi Tiene un aura espectacular
Lo que puedes hacer es ejecutar esa SQL quitándole una parte, pruebas, quitas otra parte, pruebas, quitas otra parte, pruebas, etc... hasta encontrar la parte que la hace lenta. Luego investigas y optimizas la parte lenta.
Ya sabes: divides y vencerás.
Responder Con Cita
  #12  
Antiguo 22-09-2014
Toni Toni is offline
Miembro
 
Registrado: may 2003
Ubicación: Barcelona - España
Posts: 355
Poder: 15
Toni Va por buen camino
Gracias Casimiro! Normalmente es el principio que utilizo. De hecho aqui ya he planteado la parte donde creo que tengo el problema. Pero el procedimiento tiene otras complicaciones, aunque el cuello de botella lo tengo ahi.

La otra posibilidad que tengo es lo que comentaba de usar una tabla temporal, me implica mas cambios en la aplicación y antes queria agotar todas las posibilidades optimizandola. Pero gracias por el consejo, porque es la base para encontrar los problemas.
__________________
Saludos,

Bitman
Responder Con Cita
  #13  
Antiguo 09-10-2014
Toni Toni is offline
Miembro
 
Registrado: may 2003
Ubicación: Barcelona - España
Posts: 355
Poder: 15
Toni Va por buen camino
Hola!

Finalmente comentar que ya resolvi el problema y ahora la consulta que antes tardaba 1 minuto aproximadamente ahora tarda unos 100ms.

En definitiva en este tipo de consulta que cruza y tiene que devolver el contenido de ambas tablas con tantos registros, hacer con un simple select forzamos que Firebird realice un trabajo pesado. En este caso realizaba mas de 1 millon de accesos.

Finalmente lo he resuelto con la opción que comente de utilizar un tabla temporal que nos proporciona Firebird:

Código SQL [-]

CREATE  GLOBAL TEMPORARY TABLE "TablaTemporal" (
    "Campo1" INTEGER NOT NULL,
    "Campo2" VARCHAR(10) ,
    "Campo3" VARCHAR(10) );

La ventaja de estas tablas es que las podemos crear para que despues de la transacción automaticamente se eliminen todos los registros.

Creo esta tabla temporal con todos los campos que coinciden de las tablas que quiero cruzar y lo primero que hago es un insert de la tabla A y en segundo lugar con un simple for select proceso la tabla B y voy buscando correspondencias en la tabla temporal. Si existe el registro añado los datos que me faltan y sino existe el registro lo creo nuevo.

Código SQL [-]

INSERT INTO TABLATEMPORAL FROM TABLA_A;

FOR SELECT TABLA_B

  IF(EXISTS(SELECT * FROM TABLATEMPORAL)) THEN
   BEGIN
      UPDATE TABLATEMPORAL
   END
   ELSE
   BEGIN
      INSERT INTO TABLATEMPORAL
   END

END

De esta forma el numero de registros que se procesa es minimo y el resultado es muy rapido.

Espero haberme explicado bien!

Pongo un enlace que explican muy bien como utilizar las tablas temporales:

http://firebird21.wordpress.com/2013...as-temporales/
__________________
Saludos,

Bitman

Última edición por Toni fecha: 09-10-2014 a las 21:17:20.
Responder Con Cita
  #14  
Antiguo 09-10-2014
Avatar de Casimiro Notevi
Casimiro Notevi Casimiro Notevi is offline
Moderador
 
Registrado: sep 2004
Ubicación: En algún lugar.
Posts: 26.983
Poder: 10
Casimiro Notevi Tiene un aura espectacularCasimiro Notevi Tiene un aura espectacular
Está bien saberlo
Responder Con Cita
Respuesta


Herramientas Buscar en Tema
Buscar en Tema:

Búsqueda Avanzada
Desplegado

Normas de Publicación
no Puedes crear nuevos temas
no Puedes responder a temas
no Puedes adjuntar archivos
no Puedes editar tus mensajes

El código vB está habilitado
Las caritas están habilitado
Código [IMG] está habilitado
Código HTML está deshabilitado
Saltar a Foro

Temas Similares
Tema Autor Foro Respuestas Último mensaje
Optimizar consulta JAI_ME Varios 7 19-08-2013 10:38:09
Optimizar consulta con Union ALL chinosoft Firebird e Interbase 2 06-10-2010 18:02:19
optimizar consulta martinchooozzz SQL 5 15-12-2009 18:11:42
Optimizar Consulta en Firebird AGAG4 Firebird e Interbase 14 10-01-2006 02:11:30
Optimizar Consulta dunia_lv MS SQL Server 2 21-04-2005 09:43:51


La franja horaria es GMT +2. Ahora son las 04:59:32.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Traducción al castellano por el equipo de moderadores del Club Delphi
Copyright 1996-2007 Club Delphi