uso - update output sql server 2008

Reglas generales para simplificar declaraciones SQL

Question

Estoy buscando algunas "reglas de inferencia" (similares a establecer reglas de operación o reglas lógicas) que puedo usar para reducir una consulta SQL en complejidad o tamaño. ¿Existe algo así? Cualquier documento, alguna herramienta? Cualquier equivalencia que haya encontrado por su cuenta? De alguna manera es similar a la optimización de consultas, pero no en términos de rendimiento.

Para decirlo diferente: Tener una consulta (compleja) con JOINs, SUBSELECTs, UNIONs ¿es posible (o no) reducirla a una declaración SQL más simple y equivalente, que está produciendo el mismo resultado, mediante el uso de algunas reglas de transformación?

Por lo tanto, estoy buscando transformaciones equivalentes de sentencias de SQL, como el hecho de que la mayoría de los SUBSELECTOS pueden reescribirse como un JOIN.

Answer 1

Para decirlo diferente: Tener una consulta (compleja) con JOINs, SUBSELECTs, UNIONs ¿es posible (o no) reducirla a una declaración SQL más simple y equivalente, que está produciendo el mismo resultado, mediante el uso de algunas reglas de transformación?

Eso es exactamente lo que hacen los optimizadores para ganarse la vida (no es que yo diga que siempre lo hacen bien).

Como SQL es un lenguaje basado en conjuntos, generalmente hay más de una forma de transformar una consulta en otra.

Como esta consulta:

SELECT  *
FROM    mytable
WHERE   col1 > @value1 OR col2 < @value2

se puede transformar en esto:

SELECT  *
FROM    mytable
WHERE   col1 > @value1
UNION
SELECT  *
FROM    mytable
WHERE   col2 < @value2

o esto:

SELECT  mo.*
FROM    (
        SELECT  id
        FROM    mytable
        WHERE   col1 > @value1
        UNION
        SELECT  id
        FROM    mytable
        WHERE   col2 < @value2
        ) mi
JOIN    mytable mo
ON      mo.id = mi.id

, que se ven más feos pero pueden producir mejores planes de ejecución.

Una de las cosas más comunes para hacer es reemplazar esta consulta:

SELECT  *
FROM    mytable
WHERE   col IN
        (
        SELECT  othercol
        FROM    othertable
        )

Con este:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   o.othercol = mo.col
        )

En algunos RDBMS (como PostgreSQL ), DISTINCT y GROUP BY utilizan los diferentes planes de ejecución, por lo que a veces es mejor reemplazar uno con el otro:

SELECT  mo.grouper,
        (
        SELECT  SUM(col)
        FROM    mytable mi
        WHERE   mi.grouper = mo.grouper
        )
FROM    (
        SELECT  DISTINCT grouper
        FROM    mytable
        ) mo

vs.

SELECT  mo.grouper, SUM(col)
FROM    mytable
GROUP BY
        mo.grouper

En PostgreSQL , DISTINCT ordena y hash GROUP BY .

MySQL carece de FULL OUTER JOIN , por lo que puede reescribirse como sigue:

SELECT  t1.col1, t2.col2
FROM    table1 t1
LEFT OUTER JOIN
        table2 t2
ON      t1.id = t2.id

vs.

SELECT  t1.col1, t2.col2
FROM    table1 t1
LEFT JOIN
        table2 t2
ON      t1.id = t2.id
UNION ALL
SELECT  NULL, t2.col2
FROM    table1 t1
RIGHT JOIN
        table2 t2
ON      t1.id = t2.id
WHERE   t1.id IS NULL

, pero vea este artículo en mi blog sobre cómo hacer esto de manera más eficiente en MySQL :

• Emulando FULL OUTER JOIN en MySQL

Esta consulta jerárquica en Oracle :

SELECT  DISTINCT(animal_id) AS animal_id
FROM    animal
START WITH
        animal_id = :id
CONNECT BY
        PRIOR animal_id IN (father, mother)
ORDER BY
        animal_id

se puede transformar a esto:

SELECT  DISTINCT(animal_id) AS animal_id
FROM    (
        SELECT  0 AS gender, animal_id, father AS parent
        FROM    animal
        UNION ALL
        SELECT  1, animal_id, mother
        FROM    animal
        )
START WITH
        animal_id = :id
CONNECT BY
        parent = PRIOR animal_id
ORDER BY
        animal_id

, el último siendo más eficiente.

Vea este artículo en mi blog para los detalles del plan de ejecución:

• Consulta de genealogía en ambos padres

Para encontrar todos los rangos que se superponen con el rango dado, puede usar la siguiente consulta:

SELECT  *
FROM    ranges
WHERE   end_date >= @start
        AND start_date <= @end

, pero en SQL Server esta consulta más compleja produce los mismos resultados más rápidamente:

SELECT  *
FROM    ranges
WHERE   (start_date > @start AND start_date <= @end)
        OR (@start BETWEEN start_date AND end_date)

, y créanlo o no, tengo un artículo en mi blog sobre esto también:

• Rangos superpuestos: SQL Server

SQL Server también carece de una forma eficiente de hacer agregados acumulativos, por lo que esta consulta:

SELECT  mi.id, SUM(mo.value) AS running_sum
FROM    mytable mi
JOIN    mytable mo
ON      mo.id <= mi.id
GROUP BY
        mi.id

puede ser reescrito más eficientemente usando, Señor ayúdame, cursores (me escuchaste bien: cursors , more efficiently y SQL Server en una oración).

Vea este artículo en mi blog sobre cómo hacerlo:

• Tiempos de aplanamiento: SQL Server

Existe un cierto tipo de consulta comúnmente encontrada en aplicaciones financieras que busca la tasa efectiva para una moneda, como esta en Oracle :

SELECT  TO_CHAR(SUM(xac_amount * rte_rate), ''FM999G999G999G999G999G999D999999'')
FROM    t_transaction x
JOIN    t_rate r
ON      (rte_currency, rte_date) IN
        (
        SELECT  xac_currency, MAX(rte_date)
        FROM    t_rate
        WHERE   rte_currency = xac_currency
                AND rte_date <= xac_date
        )

Esta consulta puede ser reescrita en gran medida para utilizar una condición de igualdad que permite un HASH JOIN lugar de NESTED LOOPS :

WITH v_rate AS
        (
        SELECT  cur_id AS eff_currency, dte_date AS eff_date, rte_rate AS eff_rate
        FROM    (
                SELECT  cur_id, dte_date,
                        (
                        SELECT  MAX(rte_date)
                        FROM    t_rate ri
                        WHERE   rte_currency = cur_id
                                AND rte_date <= dte_date
                        ) AS rte_effdate
                FROM    (
                        SELECT  (
                                SELECT  MAX(rte_date)
                                FROM    t_rate
                                ) - level + 1 AS dte_date
                        FROM    dual
                        CONNECT BY
                                level <=
                                (
                                SELECT  MAX(rte_date) - MIN(rte_date)
                                FROM    t_rate
                                )
                        ) v_date,
                        (
                        SELECT  1 AS cur_id
                        FROM    dual
                        UNION ALL
                        SELECT  2 AS cur_id
                        FROM    dual
                        ) v_currency
                ) v_eff
        LEFT JOIN
                t_rate
        ON      rte_currency = cur_id
                AND rte_date = rte_effdate
        )
SELECT  TO_CHAR(SUM(xac_amount * eff_rate), ''FM999G999G999G999G999G999D999999'')
FROM    (
        SELECT  xac_currency, TRUNC(xac_date) AS xac_date, SUM(xac_amount) AS xac_amount, COUNT(*) AS cnt
        FROM    t_transaction x
        GROUP BY
                xac_currency, TRUNC(xac_date)
        )
JOIN    v_rate
ON      eff_currency = xac_currency
        AND eff_date = xac_date

A pesar de ser voluminoso como un infierno, la última consulta es 6 veces más rápida.

La idea principal aquí es reemplazar <= con = , lo que requiere construir una tabla de calendario en memoria. para JOIN a.

• Conversión de monedas

Answer 2

Aquí hay algunos de trabajar con Oracle 8 y 9 (por supuesto, a veces hacer lo contrario podría hacer la consulta más simple o más rápida):

Los paréntesis se pueden eliminar si no se usan para anular la precedencia del operador. Un ejemplo simple es cuando todos los operadores booleanos en su cláusula where son los mismos: where ((a or b) or c) es equivalente a where a or b or c .

Una subconsulta puede a menudo (si no siempre) fusionarse con la consulta principal para simplificarla. En mi experiencia, esto a menudo mejora considerablemente el rendimiento:

select foo.a,
       bar.a
  from foomatic  foo,
       bartastic bar
 where foo.id = bar.id and
       bar.id = (
         select ban.id
           from bantabulous ban
          where ban.bandana = 42
       )
;

es equivalente a

select foo.a,
       bar.a
  from foomatic    foo,
       bartastic   bar,
       bantabulous ban
 where foo.id = bar.id and
       bar.id = ban.id and
       ban.bandana = 42
;

El uso de uniones ANSI separa una gran cantidad de lógica de "código mono" de las partes realmente interesantes de la cláusula where: la consulta anterior es equivalente a

select foo.a,
       bar.a
  from foomatic    foo
  join bartastic   bar on bar.id = foo.id
  join bantabulous ban on ban.id = bar.id
 where ban.bandana = 42
;

Si desea verificar la existencia de una fila, no use count (*) , en su lugar utilice rownum = 1 o coloque la consulta en una cláusula where exists para buscar solo una fila en lugar de todas.

Answer 3

Aunque la simplificación puede no ser igual a la optimización, la simplificación puede ser importante al escribir un código SQL legible, lo que a su vez es crítico para poder verificar la corrección conceptual de su código SQL (no la corrección sintáctica, que su entorno de desarrollo debería verificar). Me parece que en un mundo ideal, escribiríamos el código SQL más simple y legible, y luego el optimizador reescribirá ese código SQL para que esté en la forma que sea (quizás más detallada) funcionaría más rápido.

He descubierto que pensar en sentencias de SQL basadas en lógica de conjuntos es muy útil, especialmente si necesito combinar cláusulas where o descubrir una negación compleja de una cláusula where. Yo uso las leyes del álgebra booleana en este caso.

Los más importantes para simplificar una cláusula Where son probablemente las Leyes de DeMorgan (tenga en cuenta que "·" es "AND" y "+" es "O"):

• NO (x · y) = NO x + NO y
• NO (x + y) = NO x · NO y

Esto se traduce en SQL para:

NOT (expr1 AND expr2) -> NOT expr1 OR NOT expr2
NOT (expr1 OR expr2) -> NOT expr1 AND NOT expr2

Estas leyes pueden ser muy útiles para simplificar las cláusulas where con muchas partes AND y OR anidadas.

También es útil recordar que la declaración field1 IN (value1, value2, ...) es equivalente a field1 = value1 OR field1 = value2 OR ... Esto le permite negar el IN () una de dos maneras:

NOT field1 IN (value1, value2)  -- for longer lists
NOT field1 = value1 AND NOT field1 = value2  -- for shorter lists

También se puede pensar en una subconsulta de esta manera. Por ejemplo, esta cláusula negated where:

NOT (table1.field1 = value1 AND EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

puede ser reescrito como:

NOT table1.field1 = value1 OR NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

Estas leyes no le dicen cómo transformar una consulta SQL usando una subconsulta en una utilizando una combinación, pero la lógica booleana puede ayudarlo a comprender los tipos de combinación y lo que debería devolver su consulta. Por ejemplo, con las tablas A y B , una INNER JOIN es como A AND B , una LEFT OUTER JOIN es como (A AND NOT B) OR (A AND B) que simplifica a A OR (A AND B) , y un FULL OUTER JOIN es A OR (A AND B) OR B que se simplifica a A OR B

Answer 4

Dada la naturaleza de SQL, debe ser consciente de las implicaciones de rendimiento de cualquier refactorización. La refactorización de las aplicaciones SQL es un buen recurso en la refactorización con un gran énfasis en el rendimiento (consulte el Capítulo 5).

Answer 5

Me gusta que todos en un equipo sigan un conjunto de estándares para que el código sea legible, fácil de mantener, comprensible, lavable, etc. :)

• todos usan el mismo alias
• sin cursores no hay bucles
• ¿Por qué pensar en IN cuando puedes EXISTE?
• SANGRAR
• Consistencia en el estilo de codificación

hay más cosas aquí ¿Cuáles son algunos de sus estándares de base de datos más útiles?

Answer 6

Me gusta reemplazar todo tipo de subselección por consulta de unión.

Este es obvio:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
          SELECT  *
          FROM    othertable o
          WHERE   o.othercol = mo.col
        )

por

SELECT  mo.*
FROM    mytable mo inner join othertable o on o.othercol = mo.col

Y este está bajo estimación:

SELECT  *
FROM    mytable mo
WHERE   NOT EXISTS
        (
          SELECT  *
          FROM    othertable o
          WHERE   o.othercol = mo.col
        )

por

SELECT  mo.*
FROM    mytable mo left outer join othertable o on o.othercol = mo.col
WHERE   o.othercol is null

Podría ayudar al SGBD a elegir el buen plan de ejecución en una gran solicitud.

Answer 7

Mi enfoque es aprender la teoría relacional en general y el álgebra relacional en particular. A continuación, aprenda a detectar los constructos utilizados en SQL para implementar operadores del álgebra relacional (por ejemplo, cuantificación universal, también conocida como división) y cálculo (por ejemplo, cuantificación existencial). El problema es que SQL tiene características que no se encuentran en el modelo relacional, por ejemplo, valores nulos, que probablemente sean mejor refactorizados de todos modos. Lectura recomendada: SQL y teoría relacional: cómo escribir código SQL preciso por fecha CJ .

En este sentido, no estoy convencido de que "el hecho de que la mayoría de los SUBSELECTOS puedan reescribirse como un JOIN" represente una simplificación.

Tome esta consulta por ejemplo:

SELECT c 
  FROM T1 
 WHERE c NOT IN ( SELECT c FROM T2 );

Reescribir usando JOIN

SELECT DISTINCT T1.c 
  FROM T1 NATURAL LEFT OUTER JOIN T2 
 WHERE T2.c IS NULL;

¡La unión es más detallada!

Alternativamente, reconozca que el constructo está implementando un anti-enlace en la proyección de p ej. Pseudo algrbra

T1 { c } antijoin T2 { c }

Simplificación usando operadores relacionales:

SELECT c FROM T1 EXCEPT SELECT c FROM T2;

Answer 8

• Supongo que lo más obvio es buscar cualquier cursor que pueda reemplazarse con una operación basada en SQL ''Set''.
• Siguiente en mi lista, es buscar cualquier sub-consultas correlacionadas que se pueden volver a escribir como una consulta no correlacionada
• En procedimientos almacenados largos, divida instrucciones SQL separadas en sus propios procedimientos almacenados. De esa forma, obtendrán su propio plan de consulta en caché.
• Busque transacciones que puedan acortar su alcance. Regularmente encuentro declaraciones dentro de una transacción que pueden estar fuera de manera segura.
• Los sub-selectores a menudo se pueden reescribir como combinaciones directas (los optimizadores modernos son buenos para detectar los simples)

Como mencionó @Quassnoi, el Optimizador a menudo hace un buen trabajo. Una forma de ayudarlo es garantizar que los índices y las estadísticas estén actualizados y que existan índices adecuados para la carga de trabajo de su consulta.