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.