Home / Questions / 97870
Accepted
Bernardo Harreguy
Asked: 2020-08-29 03:02:09 +0800 CST 2020-08-29 03:02:09 +0800 CST

How to speed up very heavy loop-based MYSQL procedure (Bad praxis)

  • 772

I have done a procedure in a loop, but it seems pretty precarious to me, which leads to a process too slow for MYSQL, around 15 minutes. the procedure is the next

DELIMITER \
    CREATE DEFINER=`root`@`localhost` PROCEDURE `actualizarbasededatos`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
    BEGIN
    DECLARE x INT(11); 
    SET x = (SELECT id FROM productos ORDER BY id ASC LIMIT 1);
    REPEAT
    IF ifnull((SELECT codigobarras FROM productos WHERE x=id AND eliminado = 0), ")) != " AND ifnull((SELECT codigobarras FROM codigobarras WHERE idproducto = x),") = ifnull((SELECT codigobarras FROM productos WHERE x=id AND eliminado = 0), "))  THEN
    INSERT INTO codigobarras (codigobarras, idproducto) SELECT codigobarras, id FROM productos WHERE id = x;
    END IF;
    IF (IFNULL((SELECT id FROM precios WHERE x=idproducto AND numerodepreciodelista = 0 limit 1), -1)) = -1 AND (IFNULL((SELECT id FROM productos WHERE x=id AND eliminado = 0 limit 1), -1)) != -1 THEN
    INSERT INTO precios (precio, precio2, idproducto, numerodepreciodelista) SELECT precio, precio2, id, 0 FROM productos WHERE id=x;
    END IF;
    SET x = x + 1;
    UNTIL x > (select id from productos order by id desc limit 1) END REPEAT;
    SET x = (SELECT id FROM tickets ORDER BY id ASC LIMIT 1);
    REPEAT
    IF (select total from tickets WHERE id = x) != (ifnull((SELECT SUM(transaccion) FROM contabilidad WHERE    tipodetransaccion ='pago' and ticketid = x),0)) THEN    
    IF (ifnull((SELECT SUM(transaccion) FROM contabilidad WHERE tipodetransaccion ='pago' and ticketid = x),0)) = 0 THEN    
    INSERT INTO contabilidad (tipodetransaccion, ticketid, transaccion, formadepago) VALUES ('pago', x, (SELECT total FROM tickets WHERE id = x), 'Efectivo');
    END IF;
    END IF;
    SET x = x + 1;
    UNTIL x > (select id from tickets order by id desc limit 1)  END REPEAT;
    END
\ DELIMITER;

What the procedure performs is an update of the structure in the database, where the payments of the tickets can be carried out in an independent table, in addition to the barcodes of the products are also registered in another separate table. The operation of it is more optimal and it seems much better to me that's why the change, the problem is that the update, the process takes too long as I already said. The following code refers to the structure of the Barcode Table database.

DROP TABLE IF EXISTS `codigobarras`;
CREATE TABLE IF NOT EXISTS `codigobarras` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idproducto` int(11) NOT NULL DEFAULT '0',
  `codigobarras` text NOT NULL,
  `idrubro` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3891 DEFAULT CHARSET=utf8;

Products table

DROP TABLE IF EXISTS `productos`;
CREATE TABLE IF NOT EXISTS `productos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `promocionid` int(11) NOT NULL DEFAULT '0',
  `nombre` text NOT NULL,
  `tipodeproducto` int(11) NOT NULL DEFAULT '0',
  `cantidadbultocerrado` decimal(10,3) NOT NULL DEFAULT '0.000',
  `cantidadbultoabierto` decimal(10,3) NOT NULL DEFAULT '0.000',
  `especial` tinyint(4) NOT NULL DEFAULT '0',
  `ganancia` decimal(10,2) NOT NULL DEFAULT '0.00',
  `impuesto` decimal(3,2) NOT NULL DEFAULT '0.00',
  `ultimamodificacion` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ingresosbrutos` decimal(3,2) NOT NULL DEFAULT '0.00',
  `categoria` char(5) NOT NULL,
  `proveedor` varchar(5) NOT NULL,
  `cantidad` decimal(11,2) NOT NULL DEFAULT '0.00',
  `stockminimo` int(11) NOT NULL DEFAULT '4',
  `precio` decimal(10,2) NOT NULL DEFAULT '0.00',
  `costo` decimal(10,2) NOT NULL DEFAULT '0.00',
  `precio2` decimal(10,2) NOT NULL DEFAULT '0.00',
  `pesoprecio` decimal(10,2) NOT NULL DEFAULT '1.00',
  `fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `codigobarras` varchar(50) NOT NULL DEFAULT '',
  `imagen` blob,
  `eliminado` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9031 DEFAULT CHARSET=utf8;

Ticket table

DROP TABLE IF EXISTS `tickets`;
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `anulacion` int(11) NOT NULL DEFAULT '0',
  `pagadocon` decimal(11,2) NOT NULL DEFAULT '0.00',
  `total` decimal(11,2) NOT NULL DEFAULT '0.00',
  `formadepago` int(11) NOT NULL DEFAULT '0',
  `descuento` decimal(5,2) NOT NULL DEFAULT '0.00',
  `cuentacorriente` int(11) NOT NULL DEFAULT '0',
  `empleado` int(10) NOT NULL DEFAULT '0',
  `rubro` int(10) NOT NULL DEFAULT '0',
  `financiamiento` int(5) NOT NULL DEFAULT '0',
  `recargodeformadepago` decimal(11,2) NOT NULL DEFAULT '0.00',
  `recargofinanciamiento` decimal(11,2) NOT NULL DEFAULT '0.00',
  `cuotas` int(2) NOT NULL DEFAULT '0',
  `terminal` text,
  `deuda` decimal(11,2) NOT NULL DEFAULT '0.00',
  `retiro` int(11) NOT NULL DEFAULT '0',
  `pago` decimal(11,2) NOT NULL DEFAULT '0.00',
  `senacliente` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=latin1;

So far the old tables, I'll do some optimizations, and indexing, in the upgrade, but that's not important at the moment. The following tables are created for "database upgrade" purposes. As I mentioned before, the update more than anything involves optimizing future database queries. That is why the separation of data, to be able to create indexes.

accounting table

CREATE TABLE `contabilidad` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `ticketid` INT(11) NOT NULL DEFAULT '0',
    `cuentacorriente` INT(11) NOT NULL DEFAULT '0',
    `empleado` INT(11) NOT NULL DEFAULT '0',
    `fecha` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `interesfinanciero` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `interesformadepago` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `cuotas` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `transaccion` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `anulado` TINYINT(1) NOT NULL DEFAULT '0',
    `idgasto` INT(11) NOT NULL DEFAULT '0',
    `valordecuota` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `valorfinal` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `tipodetransaccion` TEXT NOT NULL,
    `formadepago` TEXT NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=131
;

Price Table

CREATE TABLE `precios` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `porcentaje` DECIMAL(4,2) NOT NULL DEFAULT '0.00',
    `precio` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `precio2` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `idproducto` INT(11) NOT NULL DEFAULT '0',
    `numerodepreciodelista` INT(10) NOT NULL DEFAULT '0',
    `ganancia` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `costo` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `impuesto` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `ingresosbrutos` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`),
    INDEX `idproducto` (`idproducto`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5563
;

Here with all the structures you can see how the procedure works, it would simply be executed once. But the idea is to make it faster, and obviously it works but very slowly. From already thank you very much

mysql

2 Answers

  1. Best Answer

    The main optimization that I would do is actually the rewriting of all the code by INSERTdirect statements, without going through cycles and unnecessary verifications, which is usually extremely costly in terms of performance. I am going to try to conceptually reduce each of the INSERT, please check if the reasoning I am doing is correct.

    First of all we want to insert in codigobarrasthose products productosthat are NOT deleted ( eliminados = 0) and that obviously do not exist in codigobarras. You could solve this like this:

    INSERT INTO codigobarras (codigobarras, idproducto) 
SELECT  p.codigobarras, 
        p.id 
    FROM productos p
    LEFT JOIN codigobarras c
        ON c.idproducto = p.id
    WHERE c.idproducto IS NULL
          AND p.eliminado = 0;

    The following operation would be to insert in preciosthose productosthat did not exist in price list 0 and that are not deleted ( eliminados = 0):

    INSERT INTO precios (precio, precio2, idproducto, numerodepreciodelista)
SELECT  p.precio, 
        p.precio2, 
        p.id, 
        0
    FROM productos p
    LEFT JOIN precios l
        ON l.idproducto = p.id
        AND l.numerodepreciodelista = 0
    WHERE   l.idproducto IS NULL
            AND p.eliminado = 0;

    The last operation is the one that generated the most doubts. The control you do between ticket.total and the sum of is disconcerting contabilidad.transaccionme, it makes me think that in your case you ticketshave several payments and with each one you seek to update contabilidad, the issue is that if it is so, it does not close me that you insert the totalticket directly. From what I see more logical that you try to insert in contabilidadeach ticketnew. You could solve this like this:

    INSERT INTO contabilidad (tipodetransaccion, ticketid, transaccion, formadepago)
SELECT 'pago', 
    t.id, 
    t.total,
    'Efectivo'
    FROM tickets t
    LEFT JOIN  contabilidad c
        ON c.ticketid = t.id
        AND c.tipodetransaccion ='pago' 
    WHERE c.ticketid IS NULL

    Replacing your cycles with the three statements INSERT+SELECTwill undoubtedly have a very positive impact on performance, but regardless of this, the optimization of the indexes, as already mentioned, is another point to review.

    • 2

  2. In my tests, simply adding an index to idproductothe table field codigobarrasreduced the query time from 28 seconds to only 3:

    ALTER TABLE codigobarras ADD INDEX (idproducto);

    You are using a search for said field in each iteration of the loop, but by not using it as an index, the search must go through all the records of said table:

    SELECT codigobarras FROM productos WHERE x=id AND eliminado = 0

    I'm working on converting loops to cursors to optimize iteration when there are many unused ids (so far I'm just over a second into runtime with the first part converted to cursor).

    • 0