Procedure Slow update records

Question

How to improve the procedure to perform faster? I have 3 procedures, the first searches the balance before the second updates the balance and the third I use to do the processing where I call the two previous procedures. In short, when I insert a table record a timer in the service redo the balance processing by calling the procedure PROC_PROCESSAR_SALDO.

DROP PROCEDURE IF EXISTS `PROC_SALDO_ANTERIOR`;

DELIMITER $$

CREATE PROCEDURE `PROC_SALDO_ANTERIOR` (
    IN codigo_Empresa_par INT,
    IN codigo_Filial_par INT,
    IN codigo_Conta_par INT,
    IN sequencia_par BIGINT(20),
    IN data_Hora_par DATETIME,
    OUT sequencia_ret BIGINT(20),
    OUT data_Hora_ret DATETIME,
    OUT saldo_Atual_ret DECIMAL(20, 2)
)
BEGIN

    SELECT SEQUENCIA, DATAHORA, SALDO_ATUAL INTO sequencia_ret, data_Hora_ret, saldo_Atual_ret
    FROM CONTAS_CORRENTES 
    WHERE CODIGO_EMPRESA = codigo_Empresa_par AND
    CODIGO_FILIAL = codigo_Filial_par AND
    CODIGO_CONTA = codigo_Conta_par AND 
    ((DATAHORA =data_Hora_par AND SEQUENCIA < sequencia_par) OR (DATAHORA < data_Hora_par)) 
    ORDER BY DATAHORA DESC, SEQUENCIA DESC LIMIT 1;

    if sequencia_ret IS NULL THEN
        SET sequencia_ret := 0;
        SET data_Hora_ret := '0001/01/01 12:00:00';
        SET saldo_Atual_ret := 0;
    END IF;
END;

DELIMITER ;



DROP PROCEDURE IF EXISTS `PROC_ATUALIZAR_SALDO`;

DELIMITER $$



CREATE PROCEDURE `PROC_ATUALIZAR_SALDO` (
    IN codigo_Empresa_par INT,
    IN codigo_Filial_par INT,
    IN codigo_Conta_par INT,
    IN sequencia_par BIGINT(20),
    IN data_Hora_par DATETIME,
    IN saldo_Atual_par DECIMAL(20,2)
)
BEGIN
    DECLARE SEQUENCIA_NEW BIGINT(20) DEFAULT 0;
    DECLARE DEBITO_NEW DECIMAL(20,2) DEFAULT 0;
    DECLARE CREDITO_NEW DECIMAL(20,2) DEFAULT 0;
    DECLARE SALDO_ANTERIOR DECIMAL(20,2) DEFAULT 0;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur  CURSOR FOR SELECT A.SEQUENCIA, A.DEBITO, A.CREDITO
                            FROM CONTAS_CORRENTES A
                            WHERE A.CODIGO_EMPRESA = codigo_Empresa_par AND
                            A.CODIGO_FILIAL = codigo_Filial_par AND
                            A.CODIGO_CONTA = codigo_Conta_par AND 
                            ((A.DATAHORA = data_Hora_par AND 
                            A.SEQUENCIA > sequencia_par) OR
                            (A.DATAHORA > data_Hora_par))
                            ORDER BY A.DATAHORA ASC,
                            A.SEQUENCIA ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    START TRANSACTION;

    SET SALDO_ANTERIOR := saldo_Atual_par;   


    OPEN cur;
        ins_loop: LOOP
            FETCH cur INTO SEQUENCIA_NEW, DEBITO_NEW, CREDITO_NEW;
            IF done THEN
                LEAVE ins_loop;
            END IF;

            SET SALDO_ANTERIOR := SALDO_ANTERIOR - DEBITO_NEW + CREDITO_NEW;

            UPDATE CONTAS_CORRENTES SET SALDO_ATUAL = SALDO_ANTERIOR
            WHERE CODIGO_EMPRESA = codigo_Empresa_par AND
            CODIGO_FILIAL = codigo_Filial_par AND 
            CODIGO_CONTA = codigo_Conta_par AND 
            SEQUENCIA = SEQUENCIA_NEW;
        END LOOP;
    CLOSE cur;  

    COMMIT;

END $$

DELIMITER ;

DROP PROCEDURE IF EXISTS `PROC_PROCESSAR_SALDO`;

DELIMITER $$

CREATE PROCEDURE `PROC_PROCESSAR_SALDO` (
    IN codigo_Empresa_new INT,
    IN codigo_Filial_new INT,
    IN codigo_Conta_new INT,
    IN sequencia_new BIGINT(20),
    IN data_Hora_new DATETIME
)
BEGIN

    CALL PROC_SALDO_ANTERIOR(codigo_Empresa_new, codigo_Filial_new, codigo_Conta_new, sequencia_new, 
                             data_Hora_new, @sequencia_ret, @data_Hora_ret, @saldo_Atual_ret);

    CALL PROC_ATUALIZAR_SALDO(codigo_Empresa_new, codigo_Filial_new, codigo_Conta_new, @sequencia_ret, 
                              @data_Hora_ret, @saldo_Atual_ret);
END $$

DELIMITER ;

CALL PROC_PROCESSAR_SALDO(@CODIGO_EMPRESA, @CODIGO_FILIAL, @CODIGO_CONTA, @SEQUENCIA, @DATAHORA);

Answer 1

You have to check what makes the queries slow. You basically have two things to check:

• Do the individual queries use indexes?
• Does the cursor loop produce so many rows that (mabye an slightly unoptimized) query in the loop adds up leading to the slow performance

Check the individual queries with EXPLAIN for index use. You can also add select now() after in each query and execute the procedure and you should see which part consumes the time.