IN SQL Server – la cláusula: Otra curiosidad quizás no tan conocida

Como vimos hace poco en el siguiente artículo, es bueno utilizar la palabra clave IN para hacer pensar a los alumnos sobre el funcionamiento de SQL Server. No vamos a repetir el post anterior, porque obviamente ahí está para que pueda leerlo. Volvamos a la situación de la clase: usted está impartiendo un seminario y se marca como objetivo al inicio del seminario, sembrar las dudas en la audiencia para dos cosas: 1) que dejen de asumir cuestiones hasta antes de la clase irrefutables, y 2) que intenten aproximarse a cómo funciona SQL Server.

Lo que vamos a ver

Como sabe, al analizar el comportamiento de sus consultas, deberá guiarse de componentes comocidos como:

• Microsoft SQL Server Profiler
• Microsoft SQL Server Management Studio para 1) analizar planes de ejecución, 2) analizar estadisticas de Entrada/Salida (SET STATISTICS IO ON)
• Microsoft SQL Server Tuning Advisor

En la publicación anterior, vimos como un mismo plan de ejecución (aparentemente), para consultas diferentes implicaba un coste muy diferente a nivel de Entrada/Salida, pero que “aparentemente” no se veía reflejado en el plan de ejecución y las estimaciones mostradas en SQL Server Management Studio. Ahora, haremos lo contrario, veremos una consulta “tipo” para la que se calculan diferentes planes de ejecución y sin embargo a nivel de Entrada/Salida el coste es proporcional.

Los planes de ejecución y sus costes

Nota: No repitiremos el código de creación de la tabla Predicados porque lo puede obtener del post anterior.

Las consultas en cuestión son las siguientes:

set statistics io on
set statistics time on
go

select *
from predicados
where id in (1,2,3,4,5,6,7,8,9,10)
go

select *
from predicados
where id in (
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
    16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
    31,32,33,34,35,36,37,38,39,40,41,42,43,44,45
)
go

select *
from predicados
where id in (
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
    16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
    31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,
    46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,
    61,62,63,64,65,66,67,68,69,70,71,72,73,74,75
)

En principio, viendo las consultas, diríamos que todas las consultas resuelven con el mismo plan de ejecución; sin embargo, fíjese en la sorpresa que encontramos al revisar los planes de ejecución:

Como puede ver, las dos primeras consultas utilizan el mismo plan de ejecución, sin embargo, la tercera consulta, la que tiene 75 valores en la claúsula IN, hace un nested loop, para acceder a un conjunto de valores devueltos por el operador Constant Scan.

Desde el punto de vista de Entrada Salida, tenemos lo siguiente:

Table 'predicados'. Scan count 10, logical reads 20, physical reads 0 ...
Table 'predicados'. Scan count 45, logical reads 90, physical reads 0 ...
Table 'predicados'. Scan count 0, logical reads 163, physical reads 0 ...

Fíjese que la primera consulta hace 20 lecturas lógicas para 10 Scan, lo cual sería 2 lecturas lógicas por Scan. Respecto a la segunda consulta, hace 90 lecturas lógicas para 45 Scans, que también correspondería a 2 lecturas lógicas por Scan. Sin embargo, la tercera consulta realiza 163 lecturas lógicas para 0 Scans; algo así como si se recorriera un objeto de principio a fin. Comentarles que la tabla creada no tiene tantas páginas por lo que está haciendo otro tipo de cosa, que no vemos en el plan de ejecución, Analizémoslo:

Si revisa en detalle el plan de ejecución podrá ver lo siguiente:

El operador Constant Scan, devuelve 75 filas, que si entra en detalle del operador (F4), podrá ver que corresponde a una lista de todos los valores de la cláusula IN:

Lo cual nos aníma a concluir que el operador Nested Loop, hace una busqueda (Clustered Index Seek) de cada una de los valores de predicado – como puede ver en la siguiente figura  Expresion3 (Expr3) es la lista de valores que viene del Constant Scan:

Es decir, podemos concluir que el método de acceso a la tabla, en los tres casos es el mismo: dada una lista de valores, se utiliza el operador Clustered Index Seek para recuperar las filas que corresponden. En este caso, es decisión del SQL Server, pintar de una forma u de otra el plan de ejecución.

Quizás puede llevar a confusión ver que planes de ejecución diferentes utilizan los mismos métodos de acceso, pero forma parte de su trabajo conocer cómo implementa SQL Server estos accesos; Por cierto, recuerde como ya vimos que , sería deseable “convertir” ese operador IN a BETWEEN o >= y <= para mejorar la eficiencia de acceso; no siempre se puede hacer, y no siempre tendrás beneficios tan importantes, pero es algo que deberá medir en sus sistemas.

Conclusión

Como comentamos en la publicación anterior, la figura del Desarrollador de Bases de Datos (Database Developer) es una figura a tratar en Mayúsculas, y como diría Tom Peters, es imprescidible para la EXCELENCIA de tu software. Cualquier amago de evitar dicha figura en su organización tarde o temprano deberá reemplazarla por alguien, o como en muchos casos, será una figura “asumida” por miembros de la organización.