SQL Server SARG

---

En la mayor parte de los proyectos de consultoría en los que he trabajado, me he encontrado que los predicados de las sentencias están mal escritos, o dicho de otra forma no son óptimos.

¿Cuál es el predicado de una sentencia?, es aquel que limita las filas que va a devolver la sentencia.   Para entender mejor este concepto vamos a analizar unos queries sobre la base de datos AdventureWorks.

SELECT ProductId, Name, ListPrice
FROM Production.Product
WHERE Color = 'White'

En este query vamos a obtener los productos que son de color blanco.    Si revisamos un poco su plan de ejecución (Ctrl + L) y colocamos el mouse encima de la operación "Clustered Index Scan" vamos a ver algo como esto:

Si te fijas, el predicado es la parte del WHERE.

Ahora vamos a analizar otro query, uno que nos devuelva todos los productos que pertenezcan a la categoría 2 (Components).

SELECT P.ProductID, P.Name, P.ListPrice
FROM Production.Product P
INNER JOIN Production.ProductSubcategory PS
ON P.ProductSubcategoryID = PS.ProductSubcategoryID
WHERE PS.ProductCategoryID = 2

Vamos a obtener su plan de ejecución y si colocamos el mouse encima de la operación "Clustered Index Scan" sobre el objeto ProductSubcategory.PK_ProductSubcategoryID veremos algo como esto:

 

El predicado de nueva cuenta es la parte del WHERE.

Una vez que hemos entendido este punto, es importante notar que un predicado mal escrito puede llevarnos a no utilizar los índices que se hayan creado para la optimización de las consultas, es decir que nuestros predicados no sean SARG (Search Argument).

¿Cómo reconocer cuando un predicado contiene non-SARG?, es sencillo, cuando una operación se lleva a cabo sobre las columnas de nuestras tablas, ese predicado no será óptimo.

Por ejemplo, supongamos que queremos obtener todos los productos a los que realizando un 10% de descuento vayan a tener un precio menor a 100 y mayor a 0.    El query normal que escribiríamos sería el siguiente:

SELECT ProductId, Name, ListPrice
FROM Production.Product
WHERE ListPrice * 0.9 < 100
AND ListPrice > 0

Si revisamos su plan de ejecución (costo 0.0127757) y colocamos el mouse encima de la operación "Clustered Index Scan" veremos que el siguiente predicado:

Notemos que el predicado está llevando a cabo una conversión de la columna Production.Product.ListPrice a un tipo de dato NUMERIC(19, 4).

Si es un query que se va a estar utilizando mucho entonces lo lógico sería crear un índice que nos permita obtener la información de forma mucho más rápida:

CREATE NONCLUSTERED INDEX ixPProduct_ListPrice
ON Production.Product (ListPrice)
INCLUDE (ProductId, Name)

En este query sencillo SQL Server podrá utilizar el índice pero nuestra sentencia no está totalmente optimizada.    Veamos de nuevo el plan de ejecución (costo 0.0053521) y observemos el predicado:

Ahora la operación que se está realizando es un "Index Seek" que es mucho mejor que un "Clustered Index Scan", pero la conversión que se está realizando sobre la columna Production.Product.ListPrice le está afectando a nuestro query.    Si recordamos nuestras clases de matemáticas veremos que es lo mismo:

ListPrice * 0.9 < 100

que

ListPrice < (100 / 0.9)

La diferencia en SQL radicará que la operación ya no se va a realizar sobre la columna, sino le vamos a dar un valor fijo (111.11).

Modifiquemos nuestro query para que quede de la siguiente manera:

SELECT ProductId, Name, ListPrice
FROM Production.Product
WHERE ListPrice < 111.11
AND ListPrice > 0

Si vemos el plan de ejecución (costo 0.0051023) y revisamos el predicado, veremos que la operación ya no se está realizando sobre cada fila de nuestro índice, mejora que se ve reflejada en el costo del plan de ejecución.

La mejora no es brutal, pero si estuviéramos hablando de grandes cantidades de información, seguro sería significativa dado que dejaríamos de utilizar recursos del servidor para atender esta sentencia.

---

Ahora veamos un ejemplo más interesante, supongamos que nos están pidiendo los productos que hayan sido vendidos en la primera semana del año 2003.    Con la función DATEPART podemos obtener el número de semana de una fecha pasándole como primer parámetro WEEK, y con la función YEAR podemos obtener el año de una fecha.    Nuestro query pudiera ser escrito de la siguiente manera:

SELECT P.ProductID, P.Name, SOD.UnitPrice
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD
ON P.ProductID = SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE DATEPART(WEEK, SOH.OrderDate) = 1
AND YEAR(SOH.OrderDate) = 2003

Si vemos su plan de ejecución podremos saber que el costo es de 1.38725 y que se está haciendo un barrido de la tabla Sales.SalesOrderHeader.

Vamos a crear un índice en la columna OrderDate de la tabla Sales.SalesOrderHeader para ayudar a que nuestra consulta sea más rápida:

CREATE NONCLUSTERED INDEX ixSSalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader(OrderDate)

Si volvemos a obtener el plan de ejecución de nuestro query (costo 0.91095) veremos que la operación del barrido de la tabla Sales.SalesOrderHeader ha cambiado por un barrido del índice que acabamos de crear.

Una operación Scan sobre un índice no es algo que queramos para nuestros planes de ejecución dado que no se estaría explotando al máximo la funcionalidad de los índices.

No se está realizando un uso óptimo del índice debido a que nuestro criterio de búsqueda no es SARG, estamos llevando a cabo una operación fila a fila sobre la columna OrderDate de nuestra tabla.    ¿Cómo podemos corregirlo?, usando las fechas específicas que cubran el rango de la siguiente manera:

SELECT P.ProductID, P.Name, SOD.UnitPrice
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD
ON P.ProductID = SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE OrderDate >= '20030101'
AND OrderDate <= '20030104'

Si volvemos a revisar el plan de ejecución (costo 0.362978) veremos que el barrido del índice ha cambiado por una búsqueda, es decir un Index Seek, mejora que se ha visto reflejada en el costo de nuestra sentencia:

---

Como conclusión, cuando no utilizamos SARG no estaremos utilizando las estructuras optimizadas para nuestras consultas o bien le estaremos dando trabajo de más a SQL para poder despachar el resultado.    Hay que evitar en lo posible las operaciones sobre las columnas de la base de datos durante las búsquedas.

Espero esta entrada te sea de mucha utilidad y ayude a que mejores tus consultas.

Validando email en SQL

---

En ocasiones necesitamos verificar que un correo electrónico tenga un formato válido, es decir una bandeja y su dominio respectivos divididos por una arroba.

En esta entrada veremos una forma sumamente simple pero que servirá como punta de lanza para que utilices el potencial que tienen los ensablados del .NET Framework para integrarlos en SQL Server.

Hay que decir y dejar muy claro que no es la panacea para las cosas complicadas de SQL, es simplemente un punto más, una herramienta más que tenemos a la mano para poder utilizar y facilitar nuestro trabajo.

Hacer la función que valide el formato del correo electrónico se podría describir en un proceso muy simple:

1. Crear la función como estática en .NET.
2. Crear un objeto ASSEMBLY en nuestra base de datos.
3. Crear la función que será mapeada hacia un elemento almacenado en el ASSEMBLY.

Creando función en .NET

Vamos a crear un proyecto de tipo Class Library en Visual Studio y eliminaremos el archivo Class1.cs que trae por defecto la plantilla.

Vamos agregar una clase que alojará nuestra función estática que realizará la validación del correo electrónico, el código quedaría de la siguiente manera:

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

namespace EmailValidator
{
    public class Validators
    {
        [SqlFunction]
        public static SqlBoolean ValidateEmail(SqlString email)
        {
            return SqlBoolean.Parse(Regex.IsMatch(email.Value, @"^\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$").ToString());
        }
    }
}

El atributo SqlFunction que está calificando a la función ValidateEmail le hace saber a SQL Server que ahí hay una función que puede utilizarse.

Debemos manejar tipos de dato de SQL Server, es por ello que la función devuelve un valor de tipo SqlBoolean y recibe una cadena del tipo SqlString los cuales están definidos en el espacio de nombres System.Data.SqlTypes.

La clase RegEx está definida en el espacio de nombres System.Text.RegularExpressions y su función IsMatch devuelve un valor booleano indicando que su primer parámetro cumple con el patrón definido en el segundo parámetro.

Antes de continuar al siguiente paso de esta entrada, deberemos anotar la ruta completa para llegar a nuestra función EmailValidator.Validators.ValidateEmail.

Vamos a compilar nuestro proyecto y deberá generar el DLL con nuestra función incluida.

Debemos validar el framework bajo el que está siendo construido nuestro DLL, si va a ser utilizado en SQL Server 2008 deberá estar compilado en el framework 2.0, en caso que vayamos a utilizarlo en SQL Server 2012 puede estar compilado en el framework 4.0

Crear ASSEMBLY en base de datos

Vamos a obtener la ruta completa para llegar al DLL que acabamos de generar y la utilizaremos para importarlo en nuestra base de datos.

Vamos a abrir un nuevo query que utilice la base de datos de usuario de tu elección, en mi caso tengo una base de datos llamada ExpertsExchange.

USE ExpertsExchange
GO
CREATE ASSEMBLY Validators
FROM 'D:\Personal\Blogger\EmailValidator\EmailValidator\bin\Debug\EmailValidator.dll'
WITH PERMISSION_SET = SAFE

Nuestro assembly se va a llamar Validators y tendrá un conjunto de permisos SAFE, ¿qué significa esto?

SAFE (valor por defecto) es el nivel más recomendado dado que no permite que la función o funciones incluidas en el assembly tengan acceso al sistema de archivos, a la red, a las variables de entorno o al registro de windows.

EXTERNAL_ACCESS le permite al assembly utilizar recursos externos como son archivos, red, variables de entorno y registro de windows.

UNSAFE le permite al assembly utilizar recursos externos tal como lo hace EXTERNAL_ACCESS y aparte le permite al assembly ejecutar código no administrado (código no hecho en .NET)

Crear función mapeada

Ahora vamos a crear una función escalar que reciba como parámetro el correo electrónico que se quiere validar y devuelva un BIT que indique si es válido o no.

La parte interesante es que sólo declararemos la firma de la función, el cuerpo del código ya está definido en nuestro assembly.

USE ExpertsExchange
GO
CREATE FUNCTION udfValidateEmail(
    @Email NVARCHAR(255)
)
RETURNS BIT
AS EXTERNAL NAME Validators.[EmailValidator.Validators].ValidateEmail

Notemos que la firma es como la de cualquier función que crearíamos en SQL Server, la diferencia radica en el código que está después de la especificación del tipo de resultado de nuestra función.

Aun cuando un correo electrónico no lleva caracteres extraños, es imprescindible utilizar NVARCHAR dado que las cadenas que maneja .NET son UNICODE y por lo tanto SQL Server envía NVARCHAR a .NET para que éstas sean manejadas.

¿Cómo saber qué poner en external name?, es fácil:

SQLAssemblyName.[FullNamespace.Class].NETFunctionName

---

Ahora vamos a probar nuestra función con algunos correos electrónicos tanto válidos como no válidos:

USE ExpertsExchange
GO
SELECT dbo.udfValidateEmail(N'valid@mail.com')
SELECT dbo.udfValidateEmail(N'invalid@mail..com')
SELECT dbo.udfValidateEmail(N'invalid@mail')
SELECT dbo.udfValidateEmail(N'valid@mail.com.mx')

Si recibes un mensaje de error indicando que el .NET Framework está deshabilitado es porque SQL Server no acepta la ejecución de código CLR externo por defecto.    Vamos a habilitarlo con el siguiente código:

sp_configure 'clr enabled', 1
GO
RECONFIGURE

Vuelve a probar la función y verás que el primer y cuarto correos son válidos y el segundo y tercero no lo son.

Espero te haya resultado de utilidad esta entrada, la integración de funciones CLR a SQL Server es un tema apasionante debido a la gran diversidad de soluciones que se pueden implementar via .NET, pero ten mucho cuidado, esta no es la solución a todos nuestros problemas, necesitamos hacer pruebas que demuestren que el uso de recursos y la velocidad de respuesta son las deseadas.

Emails a tabla

---

En esta entrada quiero mostrar una forma para recibir una lista de correos electrónicos divididos por coma o por punto y coma y separarlos en filas independientes en una tabla, una vez desarrollado el algoritmo crearemos la función que podremos utilizar de forma muy simple en cualquier base de datos.

Para entender plenamente el ejercicio creo muy importante el explicar brevemente cómo operan las diferentes funciones para manejo de cadenas que utilizaremos en el código.

REPLACE(string, stringToFind, stringToReplace)

Esta función reemplaza todas las apariciones de una cadena en específico por la cadena que nosotros queramos.     Por ejemplo, supongamos que queremos sustituir todas las letras "a" con acento por "á", el código sería el siguiente:

SELECT REPLACE('Me salieron ámpulas por subirme al árbol', 'á', 'á')

CHARINDEX(stringToFind, string[, startIndex])

Esta función busca la primera posición de izquierda a derecha en la que encuentre la cadena stringToFind dentro de string.    El tercer parámetro opcional indica el índice a partir del cual queremos comenzar la búsqueda de la cadena stringToFind.    Por ejemplo, queremos obtener el índice de la primera ",":

SELECT CHARINDEX(',', 'Primero, segundo y tercero')

La función devolverá un valor 0 cuando no haya encontrado la cadena que se estaba buscando.

RTRIM(string) y LTRIM(string)

La función RTRIM elimina todos los espacios en blanco que encuentre a la derecha de la cadena string, es decir, todos los espacios en blanco de relleno que pueda tener la cadena, en inglés encontramos que a esto se le llama trailing spaces.

La función LTRIM elimina todos los espacios en blanco que encuentre al inicio de la cadena string.

La combinación de ambas funciones RTRIM(LTRIM(string)) tiene un resultado como el de la función Trim de un objeto String de .NET

Supongamos que queremos eliminar todos los espacios a la izquierda y a la derecha de una palabra:

SELECT RTRIM(LTRIM('    con espacios antes y después    '))

SUBSTRING(string, startIndex, length)

Esta función extrae length caracteres de la cadena string a partir del caracter startIndex.    Por ejemplo, queremos extraer la palabra "penca" de la siguiente frase:

SELECT SUBSTRING('Grabé en la penca de un maguey', 13, 5)

La palabra "penca" comienza en el caracter número 13 y tiene una longitud de 5.

Es importante notar que en SQL Server las cadenas comienzan con el índice 1, no como en .NET donde comienzan en el índice 0.

---

Bueno, una vez que tenemos estas funciones en mente comencemos con la programación de nuestra función.

Vamos a comenzar por declarar una variable donde se encuentren los correos electrónicos que recibiriemos como parámetro en la función:

DECLARE @Emails VARCHAR(2000) = 'email1@domain1.com, email2@domain2.com; email3@domain3.com '

Como podrás notar el primer y segundo correo están divididos por una coma mientras que el segundo y tercer correo están divididos por un punto y coma.    También es importante ver que hay un espacio en blanco al final de la cadena.

Esta serie de cosas las puse así para tomar en cuenta posibles errores por parte del usuario al momento de hacerle llegar el parámetro a la función.

Vamos a cambiar los ";" por "," y así esté homogéneo el divisor de correos:

SET @Emails = REPLACE(@Emails, ';', ',')

Vamos a necesitar un par de variables, una que lleve la posición actual de análisis de la cadena de correos y otra que tenga la posición donde se encuentra la ",":

DECLARE @CurrentPos INT = 1
DECLARE @CommaPos INT = CHARINDEX(',', @Emails)

Vamos a crear una tabla temporal para ir guardando los correos, esta tabla será declarada como parte de la firma de nuestra función:

DECLARE @Result TABLE (
    Email VARCHAR(255)
)

El algoritmo es sencillo, mientras siga habiendo "," hay más correos por analizar, debido a ello estableceremos un ciclo WHILE:

WHILE @CommaPos > 0
BEGIN
END

Cuando queramos extraer el correo electrónico, deberemos sacar del caracter @CurrentPos hasta el índice donde se encuentra la "," pero sin incluir la ",".    También recordemos que tenemos que quitar cualquier espacio en blanco al inicio o al final del correo:

INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))

Debemos poner especial atención al tercer parámetro, en .NET generalmente le colocaríamos un -1 pero como en SQL Server la posición del primer caracter es 1, no es necesario realizar ese pequeño ajuste.

Vamos a actualizar nuestras variables de navegación @CurrentPos y @CommaPos:

SET @CurrentPos = @CommaPos + 1
SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)

Notemos que en este caso estamos utilizando el tercer parámetro de la función CHARINDEX, esto se debe a que la búsqueda de la "," queremos que se realice después de la que ya habíamos encontrado.

Nuestro WHILE quedaría de la siguiente manera:

WHILE @CommaPos > 0
BEGIN
    INSERT INTO @Result (Email)
    VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))

    SET @CurrentPos = @CommaPos + 1
    SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
END

Al salir del WHILE estaremos en condiciones de obtener el último correo con el siguiente código:

INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))

Nuestro código final debe quedar de la siguiente manera:

DECLARE @Emails VARCHAR(2000) = 'email1@domain1.com, email2@domain2.com; email3@domain3.com '
SET @Emails = REPLACE(@Emails, ';', ',')

DECLARE @CurrentPos INT = 1
DECLARE @CommaPos INT = CHARINDEX(',', @Emails)

DECLARE @Result TABLE (
    Email VARCHAR(255)
)

WHILE @CommaPos > 0
BEGIN
    INSERT INTO @Result (Email)
    VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))

    SET @CurrentPos = @CommaPos + 1
    SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
END

INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))

SELECT * FROM @Result

Una vez que lo hayamos probado y verifiquemos que funciona tal como lo esperábamos, vamos a encapsularlo en una función para poder reutilizarlo:

CREATE FUNCTION udfSplitEmails(
    @Emails VARCHAR(2000)
)
RETURNS @Result TABLE(
    Email VARCHAR(255)
)
AS
BEGIN
    SET @Emails = REPLACE(@Emails, ';', ',')

    DECLARE @CurrentPos INT = 1
    DECLARE @CommaPos INT = CHARINDEX(',', @Emails)

    WHILE @CommaPos > 0
    BEGIN
        INSERT INTO @Result (Email)
        VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))

        SET @CurrentPos = @CommaPos + 1
        SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
    END

    INSERT INTO @Result (Email)
    VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))

    RETURN
END

Ahora se podrá utilizar de la siguiente manera:

SELECT * FROM udfSplitEmails('email1@domain1.com, email2@domain2.com; email3@domain3.com ')

Espero te haya resultado de utilidad esta entrada, es una función que se puede incluso integrar en la base de datos model para que forme parte del machote de una base de datos cuando sea creada en el servidor.