post icon

Clausula IN vs BETWEEN AND [SQL Tunning]

Para comprender éste articulo recomiendo que haga una lectura introductoria sobre la interpretación de los planes de ejecución en SQL Server.

Lo que demostraremos será la ventaja en rendimiento del BETWEEN AND sobre la cláusula IN, pero sólo en el contexto de valores numericos correlativos, ya que si es de otro tipo de dato o no son correlativos no aplicará este concepto.

Para explicar vamos a crear un ejemplo sencillo que demostrará lo expuesto, para ello vamos a valernos de la base de datos AdventureWorks2008R2, aquí donde conseguir e instalar.

En una nueva ventana de consultas vamos a habilitar dos cosas, primero que se muestre el plan de ejecución real con CTRL + M o desde el menú “Consulta –> Incluir plan de ejecución real”. Lo segundo que haremos es que nos muestre las estadisticas de la ejecución con este script (éstas se verán en la pestaña de MENSAJE)


set statistics io on
set statistics time on

Supongamos que necesitamos obtener los datos de las transacciones historicas de produccion comprendidos entre el 100000 y 100004 podriamos hacerlo de dos maneras, en la siguiente query trabajaremos sobre el campo que tiene un índice clustered


select * from Production.TransactionHistory where TransactionID in (100000, 100001, 100002, 100003, 100004);
select * from Production.TransactionHistory where TransactionID between 100000 and 100004;

Fijemonos que nos devuelve el plan de ejecución de ambas querys

Observamos varias cosas aquí, lo primero y llamativo es que se desmitifica al eterna discusión si utilizar BETWEEN AND  es más rápido o no que >= and <=, si se fijan bien en el segundo plan de ejecución SQL Server convirtió el BETWEEN  en operadores, quedando demostrado que ambos hacen exactamente lo mismo.

Otra cosa llamativa que observamos es que dentro de este plan de ejecución ambos tienen el mismo costo relativo, y costo de operación siendo el mismo tipo de operación en ambos casos también (Búsqueda de ínide clúster), basado en el articulo de interpretación que había escrito, hasta ahora diriamos que ambos obtienen el mismo rendimiento y que podriamos utilizar indistintamente éstas querys, de alli viene el título de éste articulo, “La ventaja peligrosa del Clusterd Index”, si inspeccionamos un poco más en detalle que está sucediendo veremos donde está lo peligroso aquí, comencemos viendo los mensajes estadisticos

El texto dice esto:


Tiempo de análisis y compilación de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.
Tiempo de análisis y compilación de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 2 ms.

(5 filas afectadas)
Tabla 'TransactionHistory'. Recuento de exámenes 5, lecturas lógicas 15, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

(1 filas afectadas)

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 67 ms.
Tiempo de análisis y compilación de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

(5 filas afectadas)
Tabla 'TransactionHistory'. Recuento de exámenes 1, lecturas lógicas 3, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

(1 filas afectadas)

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 166 ms.
Tiempo de análisis y compilación de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

Aquí vemos como la clausula IN generó 5 escaneos y 15 lecturas lógicas de página en cambio el rango sólo 1 escaneo y 3 lecturas lógicas. Si agregan más valores al IN, aumentará proporcionalmente los escaneos y lecturas lógicas, empeorando la situación cuanto más valores tiene nuestra lista.

Para sacar toda duda podemos seleccionar en nuestro plan de ejecución las búsquedas y vemos en su propiedad (F4) que la primera genera 5 predicados, una por cada valor y la segunda solamente 1 predicado

 

Comentarios desde Facebook:

  1. avatar
    GeekZero PARAGUAY Google Chrome Windows
    27 agosto 2014 at 17:00 #

    Depende del contexto, tipo de datos, valores, si son dinamicos o estaticos, si pueden reemplazarse por un join, cada caso requiere una implementación distinta.

  2. avatar
    christian ramirez PERU Internet Explorer Windows
    27 agosto 2014 at 16:49 #

    eso significa que IN empeora el rendimiento de las consultas, pero que pasa si solo necesito buscar 3 datos especificos? seria necesario usar el IN?

Responder