Hi everybody,

Our first EDB application has gone in production this week and everything has gone nicely on EDB, except for what it appears to be a resource leak at the session level. The behavior is like this:

There's an After Insert trigger that calls a procedure that in turn call several functions and runs lots of EXECUTE IMMEDIATE's (Selects, Inserts, Updates), and one or two cursor as well, all within the context of a transaction.

The first time it is executed it runs fine, but with every subsequent run it grows slower and slower until the whole operation comes to a halt. To test this, I wrote an script to call the above mentioned procedure several time in a loop to reproduce it.

However, if the session is closed and reopened the cycle begins with good performance.

I've gone through the code to make sure every cursor is closed after each used, every statement is unprepared and transactions are all committed/rollbacked.

Is there something else I should check?

Advice needed....

Teammates already flaming me for not going with FireBird...

Regards,
Mario

Mario


Can you post the code that's causing the problem? A small demo app would be even better.

Roy Lambert [Team Elevate]

Roy,

Thank you for taking the time to check this out...

This is the procedure that get called for each insert on table "inv_movimiento_det".

Regards,

CREATE PROCEDURE "paInvActualizaCostoExistencia" (IN "pIdMovimientoDet" GUID COLLATE UNI)
BEGIN
  DECLARE MovIdUbicacion GUID;
  DECLARE MovIdBodega GUID;
  DECLARE MovIdSucursal GUID;
  DECLARE MovIdProductoEmpresa GUID;
  DECLARE MovIdMovimientoTipo GUID;
  DECLARE MovIdMovimientoTipoAplica GUID;

  DECLARE JerAfectaSaldo char(1);

  DECLARE MovIdMovimientoDetAplica GUID;

  DECLARE MovCantidad DECIMAL(19,4);
  DECLARE MovPrecioUnitario DECIMAL(19,4);
  DECLARE MovFecha DATE;
  DECLARE PrdCostoActual DECIMAL(19,4);

  DECLARE PrdExistenciaActual DECIMAL(19,4);
  DECLARE UbiExistenciaActual DECIMAL(19,4);
  DECLARE SucExistenciaActual DECIMAL(19,4);
  DECLARE BodExistenciaActual DECIMAL(19,4);

  DECLARE PrdFctInventario SMALLINT;
  DECLARE PrdAfectaCosto CHAR(1);
  DECLARE PrdProductoTipo VARCHAR(5);

  DECLARE CantidadAplicar DECIMAL(19, 4);
  DECLARE NuevoCosto DECIMAL(19,4);
  DECLARE Existe INT;

  EXECUTE IMMEDIATE '
     SELECT COUNT(*) INTO ? FROM inv_movimiento_det
     WHERE id_movimiento_det = ?'
  USING Existe, pIdMovimientoDet;

  IF (Existe = 1) THEN
     /* Obtener valores para el calculo*/
     EXECUTE IMMEDIATE '
                             SELECT
                                det.id_ubicacion,
                                det.id_producto_empresa,
                                det.cantidad,
                                det.precio_unitario,
                                pe.costo_unitario,
                                mov.fecha,
                                mt.fct_inventario,
                                mt.afecta_costo,
                                mov.id_bodega,
                                mov.id_sucursal,
                                det.id_movimiento_det_aplica,
                                mov.id_movimiento_tipo,
                                pe.id_producto_tipo
                             INTO
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?
                             FROM
                                inv_movimiento_det det
                                   INNER JOIN inv_movimiento mov ON det.id_movimiento = mov.id_movimiento
                                   INNER JOIN inv_producto_empresa pe ON det.id_producto_empresa = pe.id_producto_empresa
                                   INNER JOIN inv_movimiento_tipo mt ON mov.id_movimiento_tipo = mt.id_movimiento_tipo
  
                             WHERE
                                det.id_movimiento_det = ?
     '
     USING
        MovIdUbicacion,
        MovIdProductoEmpresa,
        MovCantidad,
        MovPrecioUnitario,
        PrdCostoActual,
        MovFecha,
        PrdFctInventario,
        PrdAfectaCosto,
        MovIdBodega,
        MovIdSucursal,
        MovIdMovimientoDetAplica,
        MovIdMovimientoTipo,
        PrdProductoTipo,
        pIdMovimientoDet;

     /* Para producto de tipo SERVICIO no llevar control de existencia y costos*/
     IF PrdProductoTipo = 'SERVI' THEN
        ABORT;
     END IF;

     SET PrdExistenciaActual = fnInvExistenciaGeneral(MovIdProductoEmpresa, MovFecha);
     SET SucExistenciaActual = fnInvExistenciaSucursal(MovIdProductoEmpresa, MovIdSucursal, MovFecha);
     SET BodExistenciaActual = fnInvExistenciaBodega(MovIdProductoEmpresa, MovIdBodega, MovFecha);
     SET UbiExistenciaActual = fnInvExistenciaUbicacion(MovIdProductoEmpresa, MovIdUbicacion, MovFecha);

     SET CantidadAplicar = MovCantidad * PrdFctInventario;

     /* Si afecta costo, actualizar informacion general del producto */
     IF (PrdAfectaCosto = 'S') AND (CantidadAplicar > 0) THEN
        SET NuevoCosto =
        (
           (
              prdCostoActual * prdExistenciaActual
           ) +
           (
              MovPrecioUnitario * MovCantidad
           )
        ) / (prdExistenciaActual + MovCantidad);

  
         EXECUTE IMMEDIATE
           'UPDATE inv_producto_empresa SET ultimo_costo = ?, costo_unitario = ?, fecha_ultima_compra = ?
              WHERE id_producto_empresa = ?'
        USING PrdCostoActual, NuevoCosto, MovFecha, MovIdProductoEmpresa;

         EXECUTE IMMEDIATE
           'INSERT  INTO inv_producto_hist_costo (id_producto_hist_costo, id_producto_empresa, id_movimiento_det, fecha, costo, costo_anterior)
              VALUES (?, ?, ?, ?, ?, ?)'
        USING CURRENT_GUID, MovIdProductoEmpresa, pIdMovimientoDet, MovFecha, NuevoCosto, PrdCostoActual;
     ELSE
        SET NuevoCosto = PrdCostoActual;
     END IF;

     /*Si afecta existencias.... */
     IF (CantidadAplicar <> 0) THEN
     /* Determina si existe un registro de existencia para el dia del movimiento, de lo contrario creelo*/
        EXECUTE IMMEDIATE '
           SELECT COUNT(*) INTO ? FROM inv_producto_existencia
           WHERE id_producto_empresa = ?
           AND   id_ubicacion = ?
           AND   fecha = ?'
        USING Existe, MovIdProductoEmpresa, MovIdUbicacion, MovFecha;

        IF (Existe = 0) THEN
           EXECUTE IMMEDIATE '
           INSERT INTO inv_producto_existencia (id_producto_existencia, id_producto_empresa, id_ubicacion, fecha, ingresos, salidas, cantidad_inicial) VALUES
              (?,
              ?,
              ?,
              ?,
              0,
              0,
              ?);
           '
           USING
              CURRENT_GUID,
              MovIdProductoEmpresa,
              MovIdUbicacion,
              MovFecha,
              UbiExistenciaActual;
        END IF;

       /* Actualiza la existencia de acuerdo al tipo de movimiento...*/
       IF (CantidadAplicar > 0) THEN
           EXECUTE IMMEDIATE
              'UPDATE inv_producto_existencia SET ingresos = ingresos + ?
                 WHERE id_producto_empresa = ?
                 AND   id_ubicacion        =  ?
                 AND   fecha = ?'
           USING MovCantidad, MovIdProductoEmpresa, MovIdUbicacion, MovFecha;
        ELSE
           EXECUTE IMMEDIATE
              'UPDATE inv_producto_existencia SET salidas = salidas + ?
                 WHERE id_producto_empresa = ?
                 AND   id_ubicacion        =  ?
                 AND   fecha = ?'
           USING MovCantidad, MovIdProductoEmpresa, MovIdUbicacion, MovFecha;
        END IF;
  
     END IF;
     
     /* Estampar el movimiento con indicadores de Costo/Existencia */
     SET LOG MESSAGE TO CAST(NuevoCosto AS VARCHAR);

     EXECUTE IMMEDIATE 'UPDATE inv_movimiento_det
           SET   existencia = ?,
                 existencia_sucursal = ?,
                 existencia_bodega = ?,
                 existencia_ubicacion = ?,
                 costo_unitario = ?,
                 nueva_existencia = ?,
                 nueva_exist_suc = ?,
                 nueva_exist_bod = ?,
                 nueva_exist_ubi = ?,
                 nuevo_costo = ?
           WHERE id_movimiento_det = ?'

           USING PrdExistenciaActual,
                 SucExistenciaActual,
                 BodExistenciaActual,
                 UbiExistenciaActual,
                 PrdCostoActual,
                 PrdExistenciaActual + (MovCantidad * PrdFctInventario),
                 SucExistenciaActual + (MovCantidad * PrdFctInventario),
                 BodExistenciaActual + (MovCantidad * PrdFctInventario),
                 UbiExistenciaActual + (MovCantidad * PrdFctInventario),
                 NuevoCosto,
                 pIdMovimientoDet;
                                
     
     /*
      A continuacion se determina si el movimiento origen del presente movimiento esta relacionado a traves de una
      jerarquia. Si se establece que la relacion existe y afecta saldo, se debe actualizr el campo aplicado y saldo en el movimiento origen.
     *
     /*Modificar saldo en movimiento Origen*/
     --=====================================================
     /*Movimiento Origen*/
     EXECUTE IMMEDIATE 'SELECT a.id_movimiento_tipo INTO ?
                       FROM inv_movimiento a
                            inner join
                            inv_movimiento_det b
                            on
                            a.id_movimiento = b.id_movimiento
                       where b.id_movimiento_det = ? '
     USING MovIdMovimientoTipoAplica, MovIdMovimientoDetAplica;

     EXECUTE IMMEDIATE 'SELECT   COALESCE(afecta_saldo, ''N'') INTO ?
                        FROM     inv_movimiento_jerarquia
                        WHERE    id_movimiento_tipo_destino   = ? and
                                 id_movimiento_tipo_origen = ?'
     USING JerAfectaSaldo, MovIdMovimientoTipo, MovIdMovimientoTipoAplica;

     /*Si el movimiento proviene de otro movimiento, sumar la cantidad a lo aplicado */
     IF (JerAfectaSaldo = 'S') THEN

        EXECUTE IMMEDIATE 'UPDATE inv_movimiento_det
                           SET aplicado = aplicado + ?
                           WHERE id_movimiento_det = ? AND actualizar_costo = ''N'''
        USING MovCantidad, MovIdMovimientoDetAplica;
     END IF;

     --=====================================================
                    

  END IF;
END
DESCRIPTION 'Actualiza el costo y las existencias de acuerdo al movimiento realizado'
VERSION 1.00!

Mario


I'm not an SQL expert so I could well be wrong but I can see nothing in the code you've posted. I also don't know enough about what's happening within the code to really comment. For example is the value of pIdMovimientoDet the same on each iteration. There are (I think) also four external routines being called which could have some impact.

Does the slowdown occur only when Existe is 1 which is what I'd guess from the code?

The only other thing I can suggest at this stage is to profile the individual statements to see if any of them are the problem causer.


Roy Lambert [Team Elevate]

Mario Enríquez,

The only thing unusual I see is you are updating the same table "inv_movimiento_det" that the record was just inserted to. I've had some problems with earlier versions of EDB when I tried to update the *same record* in After Insert trigger that was just inserted. Apparently the table was still inside a transaction when the after insert transaction was executed. I don't know if Tim has fixed it or if there is a workaround.

You may want to do a remote trace and set log messages to make sure all of the executes are completing properly. Also take a look at configuration.logevents to see if there are any errors.

Barry

Roy, thank you for taking the time to look at the code, your observations are all correct.

The parameter pIdMovimientoDet is different for each iteration and the procedure  does correctly what it is suppose do, even though it could be faster,our priority at this time is to make it stable and not hang de server.

Regards,
Mario

Roy Lambert wrote:

Mario


I'm not an SQL expert so I could well be wrong but I can see nothing in the code you've posted. I also don't know enough about what's happening within the code to really comment. For example is the value of pIdMovimientoDet the same on each iteration. There are (I think) also four external routines being called which could have some impact.

Does the slowdown occur only when Existe is 1 which is what I'd guess from the code?

The only other thing I can suggest at this stage is to profile the individual statements to see if any of them are the problem causer.


Roy Lambert [Team Elevate]

Barry


Well spotted - I hadn't thought of that.

Roy Lambert

ps

Have you sent me your email address yet - if so its been snaffled by the anti-spam fairies.

Barry, thank you for taking the time to read the script.

I took then advice from Roy and commented out the calls to the functions and the leak appears to be gone.

I'll be doing some more testing and let you know.

Regards,
Mario

Barry wrote:

Mario Enríquez,

The only thing unusual I see is you are updating the same table "inv_movimiento_det" that the record was just inserted to. I've had some problems with earlier versions of EDB when I tried to update the *same record* in After Insert trigger that was just inserted. Apparently the table was still inside a transaction when the after insert transaction was executed. I don't know if Tim has fixed it or if there is a workaround.

You may want to do a remote trace and set log messages to make sure all of the executes are completing properly. Also take a look at configuration.logevents to see if there are any errors.

Barry

Mario,

Are you sure the problem is a resource leak and not just the server waiting for table locks because of transactions piling up? Don't forget transactions are done in memory.

Barry

Barry

>Are you sure the problem is a resource leak and not just the server waiting for table locks because of transactions piling up? Don't forget transactions are done in memory.

From the description I'd guess its something (say a stringlist) not being cleared between calls so things slow down because there's more to do.

I haven't done any experimenting but if it was caused by transactions I'd expect either a deadlock or transaction rollback on error.

Roy Lambert