¿Qué es SARGABLE?

Es muy normal, encontrar códigos TSQL, que hacen uso de la funciones, para encontrar filas que encajen con un predicado.

Esto puede ser contrario a las búsquedas eficientes usando índices, porque para el motor, al final, el uso de funciones (aunque sean propias del motor), equivale a una caja negra. Y por tanto para resolverla, tiene que leer todas las filas, arrojando como resultado, una búsqueda «poco eficiente».

Esto se resume en un concepto llamado Sargable, que proviene de la unión de tres términos. Búsqueda, argumento y capaz. Search, Argument, Able.

Imagen de Gerd Altmann en Pixabay

Como siempre primero un escenario.

-- # Code 1.0 
Create Table dbo.Busquedas 
(
    id int primary key, 
    firstName nvarchar(50) COLLATE LATIN1_GENERAL_CS_AI
);

Llenamos la tabla con unos registros de prueba.

-- # Code 1.1
Insert into dbo.Busquedas
(id, firstName)
VALUES
(1, 'carlos'),
(2, 'Carlos'),
(3, 'caRlos'),
(4, 'carlos'),
(5, 'carLos'),
(6, 'carlos'),
(7, 'carloS'),
(8, 'carlOs'),
(9, 'CARLOS'),
(10, 'cArles'),
(11,'carlos1');

Se ha definido la columna firstName con una intercalación que distingue mayúsculas de minúsculas.

-- # Code 1.2

SELECT T.COLLATION_NAME
 FROM INFORMATION_SCHEMA.COLUMNS T
WHERE T.TABLE_NAME = N'busquedas'
AND T.COLUMN_NAME = N'firstName';

Si observas la consulta, le preguntamos a la vista del sistema, para nuestra tabla y columna, cual es el collation_name. Su respuesta, (en el caso de este ejemplo, pero puede ser cualquier otro), en la parte que nos importa son los 5 últimos caracteres.

• CI: Case Insensitive – Insensible a las mayúsculas – minúsculas
• CS: Case Sensitive – Sensible
• AI: Accent Insensitive – Insensible a los acentos
• AS: Accent Sensitive – Sensible

Esta no es el objeto del problema, pero nos ayuda a ver además del concepto SARGABLE, el hecho de que las bases de datos, como se comenta, si distinguen perfectamente las mayúsculas de las minúsculas 😉

-- # Code 2.0

Select * 
   From dbo.Busquedas
Where firstName = N'carlos';

Además vamos a crear un índice para acelerar las búsquedas en la columna.

-- # Code 2.1

Create NONCLUSTERED Index IX_Busquedas_FirstName 
ON dbo.Busquedas
(
    firstName
);

Volvemos a ejecutar la búsqueda y observamos el plan de ejecución para saber como lo ha realizado. (En Management Studio, menú consulta – > Incluir plan de ejecución Ctrl+M)

-- # Code 2.2

Select * 
   From dbo.Busquedas
Where firstName = N'carlos';

El plan, nos indica que ha usado el índice, que sólo ha necesitado leer 3 filas, en una búsqueda eficiente.

Este podría ser un punto de partida inicial, para observar el concepto, de una manera fácil.

Problema

Nos han pedido, que me traiga a todos los carlos independientemente de si están en mayúsculas o minúsculas.

-- # Code 3.1 Primer intento

Select * 
    From dbo.Busquedas
Where Lower(firstName) = N'carlos';

Hasta aquí se comporta como esperábamos. Usando la función lower, resuelto. Pero…..

Ahora el plan nos dice que ha hecho un scan del índice, y se ha leído 11 filas (toda la tabla). Esto ya no es tan eficiente. Siendo 11 filas, pues como que me da igual, pero y si fueran 11 mil millones.

Para completar un poco más el escenario y verlo un poco en su magnitud, vamos a añadir una columna, y 1 millón de filas.

-- # Code 4.1

Alter table dbo.busquedas
Add dir varchar(12);

Con la columna creada, vamos a usar un script de generación de filas, para hacer 1 millón y rellenar las dos columnas, con «algo».

-- # Code 4.2

WITH R1 AS (
	SELECT t.n FROM (VALUES(1),(1))T(n)
),R2 AS (SELECT B.N FROM R1 CROSS JOIN R1 AS B
),R3 AS (SELECT B.N FROM R2 CROSS JOIN R2 AS B
),R4 AS (SELECT B.N FROM R3 CROSS JOIN R3 AS B
),R5 AS (SELECT B.N FROM R4 CROSS JOIN R4 AS B
),R6 AS (SELECT B.N FROM R5 CROSS JOIN R5 AS B
), LMT AS (SELECT TOP(1000000) N FROM R6
), NUMS AS 
(
-- Como ya teníamos 11 filas, numeramos pero por 
-- encima de la 11 para no tener conflictos
SELECT (ROW_NUMBER() OVER(ORDER BY N) + 11) RN
FROM LMT
)
Insert into dbo.Busquedas -- insertamos
(id, firstName)
SELECT NUMS.RN, CAST(NUMS.RN AS VARCHAR(8))
FROM NUMS;

-- Además ponemos en la columna dir lo mismo que en firstname

Update dbo.Busquedas 
Set dir = firstName;

Bien, ahora ya tenemos 1 millón 11 filas. Volvemos a realizar la select correspondiente al código 3.1

-- # Code 4.3 Segundo intento

Select * 
    From dbo.Busquedas
Where Lower(firstName) = N'carlos';

Todo parece muy bien, busca en el indice el firstName, y la nueva columna en la tabla. Por eso aparece el nodo Búsqueda de claves en Clustered. Perooooo…….El operador usado es un Scan y además las propiedades del mismo dicen:

Leer 1.000.011 filas, para recuperar 9, puede no ser del todo eficiente. ¿Y porqué? Sencillamente la respuesta es clara.

El problema no es del índice, sino de la búsqueda.

El uso de funciones (la gran mayoría), impide hacer la búsqueda eficiente.

Ante la búsqueda del nombre sin la función, el operador es mucho más eficiente.

-- # Code 4.4

Select * 
   From dbo.Busquedas
Where firstName = N'carlos';

Así usa muy eficientemente el índice, (Index Seek).

Esta es una breve explicación del concepto.

Muchas veces no queda más remedio, pero hay que tener en cuenta siempre, que el uso de funciones en un predicado, en la parte izquierda puede ser ineficiente.

Ocurre lo mismo en un join o en having. No uses funciones, siempre que puedas evitarlo.

Si puedes, evítalo.

Uso de LIKE

Con like puede ocurrir algo similar.

Si usamos like con comodines, por la parte derecha el resultado es:

--  # Code 5.1

Select * 
From dbo.Busquedas
Where firstName like 'car%';

Una búsqueda eficiente en el índice.

Si buscamos cualquier firstName que termine en «los», lo que quiere decir que ponemos el comodín por la izquierda.

--  # Code 5.2

Select * 
From dbo.Busquedas
Where firstName like '%los';

Hemos perdido la eficiencia del índice. Volvemos a leer todas las filas.

Ejemplos No Sargables

• function(columna) = …..
• columna + …. = …..
• columna= @var or @var is null
• columna like ‘%….%’
• columna = case when ….