Menu

Optimización de consultas Parte 1/2

noviembre 17, 2012 - SQL Server

Introducción

Cuando una consulta no se ejecuta en tiempos ‘decentes’ por lo regular lo primero que pensamos es en crear un índice en dicha consulta (en el WHERE), sin embargo esto que bien si funciona no siempre es necesario, muchas veces haciendo una optimización en el predicado de la consulta podemos lograr muy buenos resultados sin necesidad de recurrir a nuevos índices.

En esta primera entrega veremos como utilizar lógica booleana (si, esa que vimos cuando estábamos en la universidad), podemos optimizar nuestros querys.

Requisitos

Para el ejemplo utilice SQL Server 2008 R2, sin embargo por tratarse de lógica boolena es aplicable a cualquier base de datos.

Manos a la obra!

Por lo regular cuando hablamos de consultas la cosa se pone complicada cuando estamos filtrando variables, es decir la cantidad de condiciones que tenemos en el WHERE, en nuestro ejemplo vamos a tratar con productos que tienen un ModelID, se desea que se pueda filtrar por el numero de ModelID o si no se especifica alguno, debe traerlos todos. Para lograr esto tenemos básicamente dos perspectivas:

  1. Crear 3 procedimientos almacenados, 1 que es el ‘Padre’, que decidirá dependiendo de algún parámetro decidirá si ejecuta un SP para que traiga todos los registros u otro para que filtre por el titulo. A pesar de ser una alternativa válida, tiene 2 complicaciones: por un lado si no creamos el SP ‘Padre’ podemos sufrir de Parameter Sniffing o que básicamente 3 SP’s para traer información no parece ser lo mas optimo.
  2. Hacer la lógica en un solo SP con IF anidados o con consultas dinámicas. Esta perspectiva no solo no es optima sino que incurre en una mala práctica que es hacer Dynamic SQL.

Entonces si estas sin mis alternativas y ambas no son recomendables estoy muerto?. No, para eso el álgebra booleana esta al rescate!.

Primero echemos un vistazo a la tabla de Product en nuestra BD de AdventureWorks:

SELECT ProductID
, Name
, ProductModelID
FROM
[Production].[Product]

image

Analicemos la situación, básicamente necesitamos la siguiente estructura dentro de un query:

IF A THEN B

Podemos hacerla utilizando CASE en el WHERE, así:

DECLARE @ProductModelID INT

SET @ProductModelID = 6

SELECT ProductID
, Name
, ProductModelID
FROM
[Production].[Product]
WHERE
ProductModelID = CASE
WHEN @ProductModelID IS NOT NULL THEN
@ProductModelID
ELSE
ProductModelID
END

image

y si queremos que nos traiga todo pues no le mandamos nada en @ProductModelID, así:

DECLARE @ProductModelID INT

--SET @ProductModelID = 6

SELECT ProductID
, Name
, ProductModelID
FROM
[Production].[Product]
WHERE
ProductModelID = CASE
WHEN @ProductModelID IS NOT NULL THEN
@ProductModelID
ELSE
ProductModelID
END

image

Al parecer nuestra perspectiva funciona correctamente, sin embargo tiene un par problemas:

  1. Difícil de leer si se tiene múltiples columnas para el filtrado.
  2. Si se tienen NULL no trae estos registros.

Ahora si pensamos un poco para hacerlo mas legible y no usar CASE, pensamos en ISNULL o COALESCE, así:


DECLARE @ProductModelID INT

--SET @ProductModelID = 6

SELECT ProductID
, Name
, ProductModelID
FROM
[Production].[Product]
WHERE
ProductModelID = ISNULL(@ProductModelID, ProductModelID)

image

Básicamente es lo mismo que escribir el CASE, pero más limpio y seguimos con el problema de los NULL. Aquí es cuando entra en acción la lógica booleana:


DECLARE @ProductModelID INT

--SET @ProductModelID = 6

SELECT ProductID
, Name
, ProductModelID
FROM
[Production].[Product]
WHERE
(@ProductModelID IS NULL)
OR (ProductModelID = @ProductModelID)

image

Voilà!, todos los registro incluyendo NULL.

Conclusión

Si bien es un poco complicado de leer, hace el trabajo perfectamente. Utilizar CASE en los where no es la mejor manera de filtrar datos en nuestra consulta (Ojo que solo estoy hablando de filtrado de datos, el CASE tiene otros usos), la segunda perspectiva es correcta pero no se ajustaba nuestras necesidades (que incluyera los NULL), así que con la ayuda de un poco de lógica boolena podemos obtener resultados sorprendentes. Así pues que tengan esto en su billetera:

Para escribir IF a THEN b

(NOT a) OR b

Para escribir IF a THEN b ELSE c

((NOT a) OR b) AND (a OR c)

Recomiendo dejar esta información como comentario en el query para que otro desarrollador entienda que estamos haciendo.

Etiquetas: , ,

Un pensamiento sobre “Optimización de consultas Parte 1/2

  • Pingback: Optimización de consultas Parte 2/2 « EduardoOrtega.me()

  • Deja un comentario

    Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *