Blog Archive

SQL-Server

 
Eladio Rincón

Alguna curiosidad sobre la palabra clave (keyword) IN en SQL Server

2011-07-21 13:37:00 por Eladio Rincón

Introducción

En los seminarios de SQL Server que impartimos periodicamente, ayudamos a entender a los alumnos cómo SQL Server utiliza los planes de ejecución; una parte fundamental es que el alumno comprenda la representación lógica de la consulta que está pidiendo a SQL Server; una vez entendido el procesamiento lógico de la consulta, mostramos cómo traduce SQL Server esa representación lógica en acceso (físico/logico) para obtener los datos que se necesitan para resolver la consulta. Para romper el hielo con los alumnos, solemos empezar con un ejemplo como el siguiente:

Dada la siguiente tabla llamada predicados:

 

use Adventureworks 
go
drop table predicados;
create table predicados
(id int identity, d date, s char(10)
constraint predicados_pk primary key (id))

 

Con más del 100.000 filas:

 

insert predicados 
output inserted.*
values
  ('20081010', 'aaa'), ('20081011', 'abb')
, ('20081012', 'acc') ,('20081013', 'add')
, ('20081012', 'baa') ,('20081013', 'bdd')
, ('20081012', 'bcc') ,('20081013', 'bdd')
go

insert predicados
output inserted.*
select top 100000 p.d, p.s
from predicados p
cross join master.dbo.spt_values c

 

El problema

Preguntamos a los alumnos a ver cual de las siguientes consultas resulta más eficiente:

 

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

select *
from predicados
where id between 1 and 10
go

select *
from predicados
where id >= 1 and id <= 10

 

Generalmente los alumnos suelen responder que la segunda y la tercera, aunque como profesor bandido, suelo recalcar a los alumnos que los valores de la primera consulta son muy selectivos porque se tratan de valores enteros, es decir, entre el 1 y el 2 no hay valores porque el tipo de datos es int. En ese momento la duda surge y consigo que algunos cambien de opinión.

El tema es que los alumnos que cambian de opinión hacia mi razonamiento, lo hacen porque se centran el el procesamiento lógico, y por mi mala influencia, se olvidan del procesamiento físico de la consulta; es decir, qué hace SQL Server para resolver ese predicado IN.

Antes de seguir, indicar que el plan de ejecución para la consulta 2 y 3 es exactamente el mismo porque SQL Server, traduce el predicado BETWEEN  en >= y <=.

Veamos los planes de ejecución de las consultas 1 y 2.

En primer lugar, habilitamos estadisticas de páginas lógicas leidas y tiempo invertido con el siguiente código (más información del comando http://msdn.microsoft.com/es-es/library/ms184361.aspx):

 

set statistics io on
set statistics time on

 

Para ver el plan de ejecución real de las consultas, debes habilitarlo en la opción Include Query Execution Plan, de la opción en el menú Query, también sirve la combinación de teclas Control + M.

 

La Solución

El plan de ejecución resultante es el siguiente:

Donde notamos lo siguiente:

  • SQL Server utiliza el mismo operador para acceder a los datos (Clustered Index Seek) (en color Azul).
  • El coste de ambas consultas, según el plan de ejecución es el mismo (50% en color rojo).
  • la consulta con predicado BETWEEN la ha transformado en >= y <= como indicamos anteriormente.

 

sin embargo, a nivel de operaciones en Entrada/Salida, vemos lo siguiente:

Donde vemos que la primera consulta ha realizado 10 scans con un total de 20 lecturas lógicas de página.

Y la segunda consulta ha realizado 1 scan con 2 lecturas lógicas de página.

Por lo que el “tufillo” de que las diferencias mostradas en SQL Server Management Studio parecen erroneas.

Analicemos más en detalle el primer plan de ejecución, para ello, vaya al plan de ejcución, póngase sobre el operador Clustered Index Seek, y pulse F4. Con F4 verá las propiedades de ese operador; antes de seguir adelante, como ejercicio revise un poco las propiedades del operador, y comparelas con las de la segunda consulta para ver si encuentra la diferencia. Tras unos minutos, espero que vuelva a este post, para ver la solución Smile

 

Si expande la propiedad Seek Predicates, verá lo siguiente:

Fíjese que SQL Server hace un seek para cada una de las condiciones del operador IN.

Ahora puede entender de donde vienen los 10 Scans de las lecturas lógicas: para cada una de las condiciones de la consulta primera hace una búsqueda de cada valor, es decir, primero busca las filas coincidentes para el valor 1, luego para el 2, y así sucesivamente. Puede probar usted mismo a cambiar y número de argumentos y verá como el número de Scans aumenta o decrece en función de lo que haya hecho.

 

Conclusión

Es fundamental que como desarrollador de bases de datos, conozca el funcionamiento interno de SQL Server para que las consultas que usted codifica sean eficientes en SQL Server. Lamentablemente, la figura de Desarrollador de Bases de Datos (DBD) es una figura en extinción en estos días en los que se tiende a entender la base de datos como un simple repositorio de información. Mientras las empresas no adquieran conciencia de la importancia de la base de datos, los desarrollos serán pseudo-eficientes y además de los problemas habituales que suelen encontrarse en los desarrollos, se encontrarán con problemas de eficiciencia de acceso a los datos.

Generalmente, con aumentar la escalabilidad del servidor suele resultar: incrementar los recursos asignados a la máquina (disco, memoria, CPU), pero hay otros muchos casos en los que las posibilidades de escalabilidad llegaron a sus límites y se necesitan aplicar soluciones alternativas.

Así que desde aquí sirva este artículo como llamada a los decisores en las empresas a que cuiden un poco sus arquitecturas y le den mucha importancia a la base de datos: fíjese que sin base de datos es imposible que funcione su aplicación, así que considerela como un recurso crítico!

Comments

#re: Alguna curiosidad sobre la palabra clave (keyword) IN en SQL Server
Friday, November 25, 2011 - 08:44 PM by Alexander
Excelente-----
Leave a Comment
(*) Title:
(*) Name:
Your URL:
(*) Comments:
Follow us on: