How can I get rid of this UNION?

Question

How can I get rid of this UNION?

Navigation

#1 by (1 votes)

0

The truth is that for a long time, in a system that I have, there are three tables that are used to save settlements: At the request of the user, these tables must be duplicated in their final version and their temporal version, for which there are also the following tables:

The behavior is as follows: they work and the temporary liquidations that they generate are eclipsed to the final ones, when saving, they will replace those that overshadow and if they are not saved they will simply be discarded. For the following there are these views:

Timeless Liquidations:

  SELECT 
        dl.id_detalle_liquidacion AS id_detalle_liquidacion,
        c.cod AS cod_concepto,
        c.columna AS columna,
        c.descripcion AS concepto,
        c.no_remunerativo AS no_remunerativo,
        c.no_imponible_9 AS no_imponible_9,
        dl.valor AS valor,
        dl.cantidad AS cantidad,
        dl.comentario AS comentario,
        li.id_liquidacion AS id_liquidacion,
        li.id_empleado AS id_empleado,
        pl.id_periodo_liquidado AS id_periodo_liquidado,
        pl.id_empresa AS id_empresa,
        pl.quincena AS quincena,
        pl.anio AS anio,
        pl.mes AS mes,
        pl.id_sobre AS id_sobre
    FROM
        (((Periodos_liquidados pl
        JOIN Liquidaciones li)
        JOIN Detalles_liquidacion dl)
        JOIN Conceptos c)
    WHERE
        ((pl.id_periodo_liquidado = li.id_periodo_liquidado)
            AND (li.id_liquidacion = dl.id_liquidacion)
            AND (c.id_concepto = dl.id_concepto))

Another view that is the same, but with the liquidation tables saved called Liquidaciones_guardadas.

Liquidaciones_visibles, which shows the liquidations saved not eclipsed and temporary ones:

SELECT 
        Liquidaciones_temporales.id_detalle_liquidacion AS id_detalle_liquidacion,
        Liquidaciones_temporales.cod_concepto AS cod_concepto,
        Liquidaciones_temporales.columna AS columna,
        Liquidaciones_temporales.concepto AS concepto,
        Liquidaciones_temporales.valor AS valor,
        Liquidaciones_temporales.cantidad AS cantidad,
        Liquidaciones_temporales.comentario AS comentario,
        Liquidaciones_temporales.id_liquidacion AS id_liquidacion,
        Liquidaciones_temporales.id_empleado AS id_empleado,
        Liquidaciones_temporales.id_periodo_liquidado AS id_periodo_liquidado,
        Liquidaciones_temporales.id_empresa AS id_empresa,
        Liquidaciones_temporales.quincena AS quincena,
        Liquidaciones_temporales.anio AS anio,
        Liquidaciones_temporales.mes AS mes,
        Liquidaciones_temporales.no_remunerativo AS no_remunerativo,
        Liquidaciones_temporales.no_imponible_9 AS no_imponible_9,
        Liquidaciones_temporales.id_sobre AS id_sobre,
        0 AS guardada
    FROM
        Liquidaciones_temporales 
    UNION ALL SELECT 
        lg.id_detalle_liquidacion AS id_detalle_liquidacion,
        lg.cod_concepto AS cod_concepto,
        lg.columna AS columna,
        lg.concepto AS concepto,
        lg.valor AS valor,
        lg.cantidad AS cantidad,
        lg.comentario AS comentario,
        lg.id_liquidacion AS id_liquidacion,
        lg.id_empleado AS id_empleado,
        lg.id_periodo_liquidado AS id_periodo_liquidado,
        lg.id_empresa AS id_empresa,
        lg.quincena AS quincena,
        lg.anio AS anio,
        lg.mes AS mes,
        lg.no_remunerativo AS no_remunerativo,
        lg.no_imponible_9 AS no_imponible_9,
        lg.id_sobre AS id_sobre,
        1 AS guardada
    FROM
        Liquidaciones_guardadas lg
    WHERE
        (NOT (EXISTS( SELECT 
                1 AS Not_used
            FROM
                Liquidaciones_temporales lt
            WHERE
                ((lg.id_sobre = lt.id_sobre)
                    AND (lg.mes = lt.mes)
                    AND (lg.anio = lt.anio)
                    AND (lg.quincena = lt.quincena)
                    AND (lg.id_empresa = lt.id_empresa)
                    AND (lg.id_empleado = lt.id_empleado)))))

There is also a more called Liquidaciones_guardadas_visibles view that shows only the liquidations saved that are not eclipsed (this is what appears in the second part of the UNION of the previous view but in its own view) that you create to test.

Now, both the views of settled liquidations and temporary settlements are fast, they resolve in the following times:
- Temporary settlements 0.0031
- Liquidaciones_guardadas 0.0029
- Liquidaciones_guardadas_visibles 0.004
- Liquidations_visibles 3.817

As you can see the time in which the latter is calculated is by far superior to the others, I know that this is because the clause UNION is especially heavy because it creates temporary tables and rebuilds its indices to generate the results of the view . Now this for the users is not a big complication, it does not bother them to wait 4 seconds to obtain the result of a query and when I consult this in stored procedures it can be optimized by operating the tables separately. It is curious to know if it is possible to solve this same view without the use of a UNION , or use it in a more performant way.

Note : It occurred to me that by deforming the tables and storing these 6 in a single table with a criterion that separates saved, unsaved and eclipsed could be optimized but the The question I have is keeping this structure.

mysql sql mariadb

asked by Sacha 09.03.2018 в 16:45

source

1 answer

clean a select in jquery Change color notification bar android studio

score 1 · Accepted Answer

At the end analyzing the problem, I reached the conclusion that if I reduced the tables to join then the query was done more quickly, so that I create a stored procedure to fill a temporary table, like this:

CREATE PROCEDURE 'crear_tabla_liquidaciones_visibles'(IN id_empresa INT, IN anio INT, IN mes INT, IN quincena INT)
BEGIN

    DROP TEMPORARY TABLE IF EXISTS tmp_liquidaciones_visibles;

    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_liquidaciones_visibles ENGINE=MEMORY
    SELECT lt.id_detalle_liquidacion AS id_detalle_liquidacion,
           lt.cod_concepto AS cod_concepto,
           lt.columna AS columna,
           lt.concepto AS concepto,
           lt.valor AS valor,
           lt.cantidad AS cantidad,
           lt.comentario AS comentario,
           lt.id_liquidacion AS id_liquidacion,
           lt.id_empleado AS id_empleado,
           lt.id_periodo_liquidado AS id_periodo_liquidado,
           lt.id_empresa AS id_empresa,
           lt.quincena AS quincena,
           lt.anio AS anio,
           lt.mes AS mes,
           lt.no_remunerativo AS no_remunerativo,
           lt.no_imponible_9 AS no_imponible_9,
           lt.id_sobre AS id_sobre,
           0 AS guardada
      FROM Liquidaciones_temporales lt
     WHERE lt.anio = anio
       AND (lt.mes = mes OR mes = -1)
       AND (lt.quincena = quincena OR quincena = -1)
       AND lt.id_empresa = id_empresa;

    INSERT INTO tmp_liquidaciones_visibles
    SELECT lg.id_detalle_liquidacion AS id_detalle_liquidacion,
           lg.cod_concepto AS cod_concepto,
           lg.columna AS columna,
           lg.concepto AS concepto,
           lg.valor AS valor,
           lg.cantidad AS cantidad,
           lg.comentario AS comentario,
           lg.id_liquidacion AS id_liquidacion,
           lg.id_empleado AS id_empleado,
           lg.id_periodo_liquidado AS id_periodo_liquidado,
           lg.id_empresa AS id_empresa,
           lg.quincena AS quincena,
           lg.anio AS anio,
           lg.mes AS mes,
           lg.no_remunerativo AS no_remunerativo,
           lg.no_imponible_9 AS no_imponible_9,
           lg.id_sobre AS id_sobre,
           1 AS guardada
      FROM Liquidaciones_guardadas lg
     WHERE lg.anio = anio
       AND (lg.mes = mes OR mes = -1)
       AND (lg.quincena = quincena OR quincena= -1)
       AND lg.id_empresa = id_empresa
       AND NOT EXISTS( SELECT 1
                         FROM Liquidaciones_temporales lt2
                        WHERE lt2.anio= anio
                          AND (lt2.mes= mes OR mes = -1)
                          AND (lt2.quincena= quincena OR quincena = -1)
                          AND lt2.id_empresa= id_empresa
                          AND lt2.id_sobre = lg.id_sobre
                          AND lt2.mes = lg.mes
                          AND lt2.anio = lg.anio
                          AND lt2.quincena = lg.quincena
                          AND lt2.id_empresa = lg.id_empresa
                          AND lt2.id_empleado = lg.id_empleado);
END

The issue is in what are the wheres parameterized to the views, so that only the sets are operated after being filtered, which is much faster than joining the tables and then filtering them.
In conclusion: I think the best way to solve it would be if you could parameterize the views, but mysql does not allow it beyond some workaround as seen in this link .