How to accelerate very heavy MYSQL procedure based on loop (Mala praxis)

Question

How to accelerate very heavy MYSQL procedure based on loop (Mala praxis)

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

I have performed a procedure in a loop, but I find it quite precarious, which leads to a process too lenient to be MYSQL, about 15 minutes. The procedure is as follows

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;

The procedure what it does is an update of the structure in the database, where the payments of the tickets can be carried in a separate table, besides the barcodes of the products are also registered in another separate table. The operation of it is more optimal and it seems much better for that reason the change, the problem is that the update, the process takes too much as I said. The following code refers to the structure of the database Codecode table

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;

Table products

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;

Table tickets

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 will do some optimizations, and creation of indices, in the update, but that issue is not important at this time. The following tables are created for the purpose of "updating the database". As I mentioned earlier, the update, more than anything, includes optimizing queries to the future database. That's 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 find out how the procedure works, It would simply be executed once. But the idea is to be faster, and obviously it works but very slow. Thank you very much

mysql

asked by Bernardo Harreguy 28.08.2017 в 13:02

source

2 answers

0

In my tests simply adding an index to the field idproducto of the table codigobarras I reduce from 28 seconds to only 3 the time of the query:

ALTER TABLE codigobarras ADD INDEX (idproducto);

You are using a search for that field in each iteration of the loop, but when you do not use it as an index the search must go through all the records in that table:

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

I'm working on converting loops into cursors to optimize the iteration when there are many unused identifiers (for now I have just over a second of execution time with the first part converted to a cursor).

answered by 28.08.2017 в 16:34

How to print a JSON string within an input with PHP? Improve the results of a select with LIKE

score 2 · Accepted Answer

The main optimization that would do, is actually the rewriting of the entire code by statements of INSERT direct, without going through cycles and unnecessary checks that is often extremely expensive in terms of performance. I will try to conceptually reduce each of the INSERT , please check if the reasoning I do is correct.

First of all we want to insert in codigobarras those products of productos that are NOT eliminated ( eliminados = 0 ) and that obviously do not exist in codigobarras . This you could solve it 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 next operation would be to insert in precios those productos that did not exist in price list 0 and that are not eliminated ( 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 me the most doubts. I am puzzled by the control you make between ticket.total and the sum of contabilidad.transaccion , it makes me think that in your case the tickets has several payments and with each one you are looking to update contabilidad , the issue is that if it is like this, It does not close me that you insert directly the total of the ticket. So I see more logical that you try to insert in contabilidad every ticket new. This you could solve it 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 by the three INSERT+SELECT sentences will undoubtedly have a very positive impact on performance, but regardless of this, the optimization of the indexes, as you have already mentioned, is another point to review.