datos - ¿Se evalúa el cortocircuito de la cláusula WHERE de SQL?

having sql

Question

Por ejemplo:

SELECT * 
FROM Table t 
WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key) 

Si @key IS NULL se evalúa como verdadero, ¿ @key NO ES NULO Y @key = t.Key evaluado?

Si no, ¿por qué no?

Si es así, ¿está garantizado? ¿Es parte de ANSI SQL o es una base de datos específica?

Si es específico de la base de datos, SqlServer? ¿Oráculo? MySQL?

Referencia: Evaluación de cortocircuito

Answer 1

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

6.3.3.3 Orden de evaluación de reglas

[...]

Cuando la precedencia no está determinada por los Formatos o por paréntesis, la evaluación efectiva de las expresiones generalmente se realiza de izquierda a derecha. Sin embargo, dependerá de la implementación si las expresiones se evalúan realmente de izquierda a derecha, particularmente cuando los operandos u operadores pueden provocar la elevación de condiciones o si los resultados de las expresiones se pueden determinar sin evaluar completamente todas las partes de la expresión.

Answer 2

Acabo de tropezar con esta pregunta, y ya había encontrado esta entrada de blog: http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/

El servidor SQL es libre de optimizar una consulta en cualquier lugar que le parezca adecuado, por lo que en el ejemplo dado en la publicación del blog, no puede confiar en un cortocircuito.

Sin embargo, aparentemente se ha documentado que un CASE se evalúa en el orden escrito; verifique los comentarios de esa publicación en el blog.

Answer 3

Aquí hay una demostración para demostrar que MySQL realiza un cortocircuito en la cláusula WHERE :

http://rextester.com/GVE4880

Esto ejecuta las siguientes consultas:

SELECT myint FROM mytable WHERE myint >= 3 OR myslowfunction(''query #1'', myint) = 1;
SELECT myint FROM mytable WHERE myslowfunction(''query #2'', myint) = 1 OR myint >= 3;

La única diferencia entre estos es el orden de los operandos en la condición OR.

myslowfunction duerme deliberadamente por un segundo y tiene el efecto secundario de agregar una entrada a una tabla de registro cada vez que se ejecuta. Estos son los resultados de lo que se registra al ejecutar las dos consultas anteriores:

myslowfunction called for query #1 with value 1
myslowfunction called for query #1 with value 2
myslowfunction called for query #2 with value 1
myslowfunction called for query #2 with value 2
myslowfunction called for query #2 with value 3
myslowfunction called for query #2 with value 4

Lo anterior muestra que una función lenta se ejecuta más veces cuando aparece en el lado izquierdo de una condición O cuando el otro operando no siempre es verdadero (debido a un cortocircuito).

Answer 4

Creo que este es uno de los casos en que lo escribiría como si no hubiera un cortocircuito, por tres razones.

1. Porque para MSSQL, no se resuelve mirando BOL en el lugar obvio, por lo que para mí, eso lo hace canónicamente ambiguo.

2. porque al menos entonces sé que mi código funcionará. Y lo más importante, también lo harán aquellos que me busquen, así que no los voy a preocupar por la misma pregunta una y otra vez.

3. Escribo con suficiente frecuencia para varios productos DBMS, y no quiero tener que recordar las diferencias si puedo solucionarlos fácilmente.

Answer 5

De lo anterior, el cortocircuito no está realmente disponible.

Si lo necesita, sugiero una declaración de caso:

Where Case when Expr1 then Expr2 else Expr3 end = desiredResult

Expr1 siempre se evalúa, pero solo uno de Expr2 y Expr3 se evaluará por fila.

Answer 6

Debajo de una prueba rápida y sucia en SQL Server 2008 R2:

SELECT *
FROM table
WHERE 1=0
AND (function call to complex operation)

Esto vuelve inmediatamente sin registros. Tipo de comportamiento de cortocircuito estaba presente.

Luego probé esto:

SELECT *
FROM table
WHERE (a field from table) < 0
AND (function call to complex operation)

sabiendo que ningún registro satisfaría esta condición:

(a field from table) < 0

Esto llevó varios segundos, lo que indicaba que el comportamiento del cortocircuito ya no existía y que la operación compleja se estaba evaluando para cada registro.

Espero que esto ayude chicos.

Answer 7

Debe tener en cuenta cómo funcionan las bases de datos. Dada una consulta parametrizada, el db crea un plan de ejecución basado en esa consulta sin los valores para los parámetros. Esta consulta se usa cada vez que se ejecuta la consulta, independientemente de cuáles sean los valores reales proporcionados. Si la consulta de cortocircuitos con ciertos valores no tendrá importancia para el plan de ejecución.

Answer 8

Es obvio que el servidor MS Sql admite la teoría de cortocircuitos, para mejorar el rendimiento al evitar las comprobaciones innecesarias.

Ejemplo de apoyo:

SELECT ''TEST''
WHERE 1 = ''A''

SELECT ''TEST''
WHERE 1 = 1 OR 1 = ''A''

Aquí, el primer ejemplo daría como resultado el error "La conversión falló al convertir el valor varchar ''A'' al tipo de datos int ''.

Mientras que el segundo se ejecuta fácilmente ya que la condición 1 = 1 se evaluó a TRUE y, por lo tanto, la segunda condición no se ejecuta en absoluto.

Además

SELECT ''TEST''
WHERE 1 = 0 OR 1 = ''A''

aquí la primera condición se evaluaría como falsa y, por lo tanto, el DBMS elegiría la segunda condición y nuevamente obtendrá el error de conversión como en el ejemplo anterior.

NOTA: ESCRIBIÉ LA CONDICIÓN ERRÓNEA SOLO PARA CONOCER EL TIEMPO. LA CONDICIÓN SE EJECUTÓ O CORTOCIRCUITO SI LA PREGUNTA RESULTA EN ERROR SIGNIFICA LA CONDICIÓN EJECUTADA, CORTOCIRCUITADA DE OTRA MANERA.

EXPLICACIÓN SIMPLE

Considerar,

WHERE 1 = 1 OR 2 = 2

como la primera condición se evalúa como VERDADERO , no tiene sentido evaluar la segunda condición porque su evaluación en cualquier valor no afectaría el resultado, por lo que es una buena oportunidad para que el Servidor SQL guarde el tiempo de ejecución de la consulta omitiendo una verificación o evaluación de condición innecesaria .

en el caso de "O" si la primera condición se evalúa como VERDADERO, toda la cadena conectada por "O" se consideraría evaluada como verdadera sin evaluar otras.

condition1 OR condition2 OR ..... OR conditionN

si la condición1 se evalúa como verdadera, descanse todas las condiciones hasta que se omita la condiciónN. En palabras generalizadas al momento de la determinación del primer VERDADERO , todas las demás condiciones vinculadas por OR se omitirán.

Considera la segunda condición

WHERE 1 = 0 AND 1 = 1

como la primera condición se evalúa como FALSA, no tiene sentido evaluar la segunda condición porque su evaluación en cualquier valor no afectaría en absoluto el resultado, así que nuevamente es una buena oportunidad para que el servidor SQL guarde el tiempo de ejecución de la consulta omitiendo una verificación o evaluación de condición innecesaria .

en el caso de "Y" si la primera condición se evalúa como FALSO, toda la cadena conectada con "Y" se considerará como FALSA sin evaluar otras.

condition1 AND condition2 AND ..... conditionN

si la condición1 se evalúa como FALSA , descanse todas las condiciones hasta que la condiciónN sea ​​omitida. En palabras generalizadas al momento de la determinación del primer FALSO , todas las demás condiciones vinculadas por AND se omitirán.

POR LO TANTO, UN PROGRAMADOR WISE DEBE SIEMPRE PROGRAMAR LA CADENA DE CONDICIONES DE FORMA QUE, MENOS COSTOSA O LA CONDICIÓN MÁS ELIMINATORIA SE EVALUE PRIMERO, O DISPONER DE LA CONDICIÓN DE FORMA QUE PUEDA APROVECHAR AL MÁXIMO EL CORTOCIRCUITO

Answer 9

Esto toma 4 segundos adicionales en el analizador de consultas, por lo que puedo ver si ni siquiera está en corto ...

SET @ADate = NULL

IF (@ADate IS NOT NULL)
BEGIN
    INSERT INTO #ABla VALUES (1)
        (SELECT bla from a huge view)
END

¡Sería bueno tener una manera garantizada!

Answer 10

La principal característica de la evaluación de cortocircuitos es que deja de evaluar la expresión tan pronto como se puede determinar el resultado. Eso significa que el resto de la expresión se puede ignorar porque el resultado será el mismo independientemente de si se evalúa o no.

Los operadores booleanos binarios son comutativos, lo que significa:

a AND b == b AND a
a OR  b == b OR  a
a XOR b == b XOR a

entonces no hay garantía en el orden de evaluación. El orden de evaluación será determinado por el optimizador de consultas.

En los lenguajes con objetos, puede haber situaciones en las que puede escribir expresiones booleanas que solo se pueden evaluar con la evaluación de cortocircuitos. La construcción del código de muestra se usa a menudo en dichos idiomas (C #, Delphi, VB). Por ejemplo:

if(someString == null | someString.Length == 0 )
  printf("no text in someString");

Este ejemplo de C # causará una excepción si someString == null porque se evaluará por completo. En la evaluación de cortocircuito, funcionará todo el tiempo.

SQL opera solo en variables escalares (sin objetos) que no pueden ser sin inicializar, por lo que no hay forma de escribir una expresión booleana que no se pueda evaluar. Si tiene algún valor NULL, cualquier comparación dará como resultado falso.

Eso significa que en SQL no se puede escribir una expresión que se evalúa de forma diferente según el uso de cortocircuitos o una evaluación completa.

Si la implementación de SQL usa la evaluación de cortocircuito, solo se espera que acelere la ejecución de la consulta.

Answer 11

No creo que se garantice el cortocircuito en SQL Server (2005). SQL Server ejecuta su consulta a través de su algoritmo de optimización que tiene en cuenta muchas cosas (índices, estadísticas, tamaño de tabla, recursos, etc.) para llegar a un plan de ejecución eficaz. Después de esta evaluación, no puede decir con certeza si su lógica de cortocircuito está garantizada.

Me encontré con la misma pregunta hace algún tiempo y mi investigación realmente no me dio una respuesta definitiva. Puede escribir una pequeña consulta para darle una prueba de que funciona, pero ¿puede estar seguro de que a medida que la carga en su base de datos aumenta, las tablas crecen y las cosas se optimizan y cambian en la base de datos, esa conclusión sostener. No pude, y por lo tanto, me equivoqué por el lado de la precaución y usé CASE en la cláusula WHERE para asegurar el cortocircuito.

Answer 12

No sé sobre circo corto, pero lo escribiría como una declaración if-else

if (@key is null)
begin

     SELECT * 
     FROM Table t 

end
else
begin

     SELECT * 
     FROM Table t 
     WHERE t.Key=@key

end

también, las variables siempre deben estar en el lado derecho de la ecuación. esto lo hace sargable.

http://en.wikipedia.org/wiki/Sargable

Answer 13

Normalmente uso esto para los parámetros opcionales. ¿Es esto lo mismo que cortocircuitar?

SELECT  [blah]
FROM    Emp
WHERE  ((@EmpID = -1) OR (@EmpID = EmpID))

Esto me da la opción de pasar -1 o lo que sea para dar cuenta de la verificación opcional de un atributo. A veces esto implica unirse a varias tablas, o preferiblemente una vista.

Muy útil, no del todo seguro del trabajo extra que le da al motor de db.

Answer 14

Para SQL Server, creo que depende de la versión, pero mi experiencia con SQL Server 2000 es que todavía evalúa @key = t.Key incluso cuando @key es nulo. En otras palabras, no hace un cortocircuito eficiente al evaluar la cláusula WHERE.

He visto personas que recomiendan una estructura como su ejemplo como una forma de hacer una consulta flexible donde el usuario puede ingresar o no ingresar varios criterios. Mi observación es que Key todavía está involucrado en el plan de consulta cuando @key es nulo y si Key está indexado, entonces no usa el índice de manera eficiente.

Este tipo de consulta flexible con diferentes criterios es probablemente un caso en el que el SQL creado dinámicamente es realmente la mejor manera de hacerlo. Si @key es nulo, simplemente no lo incluirá en la consulta.