Rendimiento de XML en SQL Server 2005

Por Gustavo Larriera – MVP en SQL Server y Mentor de Solid Quality Learning.

En un proyecto reciente, estuve involucrado en una migración desde SQL Server 2000 hacia SQL Server 2005. El sistema, una aplicación Web crítica para una empresa con miles de empleados en cientos de oficinas en el mundo, hacía un uso intensivo de datos XML que se almacenaba en la base de datos. En la fase de decidir la migración hacia SQL Server 2000 surgieron varias preguntas:

La documentación de SQL Server 2005 y múltiples fuentes de información indicaban que el rendimiento de XML nativo en SQL Server 2005 iba a ser muy bueno. Especialmente si consideramos la posibilidad de que las columnas de tipo XML pueden estar indizadas y que los índices participan del plan de ejecución que el optimizador de consultas realiza.

Decidimos hacer pruebas de laboratorio para poder obtener las respuestas concretas que el cliente necesitaba. En los siguientes párrafos voy a detallar las pruebas realizadas y las conclusiones obtenidas.

XML en SQL Server 2005

El tipo de datos XML es un tipo nativo en SQL Server similar a otros tipos incorporados como INT o VARCHAR. Al igual que los otros tipos incorporados, se puede utilizar el tipo XML como un tipo de columna al crear una tabla, como un tipo de variable, un tipo de parámetro, un tipo de valor devuelto de función. XML es ciudadano de primera clase en el sistema de tipos de SQL Server.

Por ejemplo, podemos crear una tabla con una columna XML de la siguiente manera:

create table Clientes (idcliente int primary key, datoscliente xml);

XQuery es un lenguaje que permite realizar consultas en datos XML estructurados o semiestructurados. Para realizar una consulta en una instancia XML se usan los métodos del tipo de datos XML: query(), value(), exist(), entre otros. Por ejemplo, podemos declarar una variable de XML y consultarla mediante el método query():

declare @x xml; 
set @x = '<root><clientenro>111</clientenro></root>';  
select @x.query('/root/clientenro');

Los índices XML permiten indizar columnas de tipo XML. Hay dos tipos de índices XML: el índice primario o principal y el índice secundario. El primer índice de la columna debe ser el índice XML principal. Dependiendo del tipo de consulta, los índices secundarios pueden contribuir a mejorar el rendimiento. A su vez, con el índice XML principal, se admiten tres tipos de índices secundarios. Entre ellos, se incluyen PATH, VALUE y PROPERTY:

Por ejemplo, podemos crear un índice XML de esta manera:

create primary index ClientesPIx on Clientes  (datoscliente);

Para conocer todos los detalles de la implementación de XML en SQL Server 2005 recomiendo utilizar los Libros en Pantalla del producto, que pueden descargarse en forma autónoma en el sitio de Microsoft (no se requiere instalar el programa SQL Server).

La aplicación original: XML en SQL Server 2000

La aplicación original almacenaba datos XML en columnas de tipo TEXT de SQL Server 2000. Este tipo “no entiende” el formato XML que almacena, pues SQL Server 2000 lo interpreta como simple texto. Así tenemos que toda la semántica de datos XML es responsabilidad de la programación afuera de la base de datos. Adicionalmente los controles de que se almacene XML bien formado son responsabilidad de la aplicación.

Para buscar datos en los datos XML la aplicación realizaba consultas SQL basadas en expresiones LIKE. Aún cuando se pudieran indizar los campos TEXT, las consultas LIKE con patrones como ‘%texto%’ y funciones de manejo de cadenas de caracteres, no permiten generar un plan óptimo de ejecución.

Las búsquedas más frecuentes, obtener un valor dentro de un elemento XML, se implementaron mediante una función definida por el usuario en la base de datos.

Primera etapa: Migración de datos XML a SQL Server 2005

SQL Server 2005 brinda soporte extenso al procesamiento de datos XML; los datos XML se almacenan en columnas de tipo XML nativo, que pueden tener esquema predefinido, y se manipulan mediante XQuery. Los datos de tipo XML se almacenan como grandes objetos binarios con instancias de hasta 2 GB de tamaño. Estos datos XML pueden indizarse para optimizar las consultas basadas en XQuery.

Considerando las funcionalidades que brinda el tipo XML en SQL Server 2005 se optó por pasar los datos XML a columnas de tipo XML. Los pasos consistieron en:

Al terminar esta etapa, tuvimos una base en SQL Server 2005 donde todos los datos XML se almacenaron en columnas XML indizadas.

Segunda etapa: Reprogramación de consultas

Las consultas que se usaban en SQL Server 2000 se basaban en comparaciones realizadas usando el predicado LIKE y una función definida por el usuario ufn_GetXmlValue (colXml, strElemento) que retornaba el valor almacenado en el strElemento del dato XML. Por ejemplo, las siguientes son consultas realizadas en el sistema que usaba SQL Server 2000:

-- Q1: consulta usando LIKE en una columna TEXT
select clntGUID from test.clients 
where 
   coltxt like '%<customerName1>John Doe</customerName1>%'

-- Q2: consulta usando una UDF en una columna TEXT
select clntGUID from test.clients 
where 
   dbo.ufn_GetXmlValue( coltxt, 'customerName1') = 'John Doe'

Las consultas anteriores pueden reescribirse con XQuery de la siguiente manera (observar el uso del método ‘contains’ para buscar una cadena contenida, equivalente a LIKE ‘%expr%’):

-- Q3: consulta usando XQuery en una columna XML (busqueda exacta)
select clntGUID from test.clients 
where 
   colxml.value('(/hq/customerName1)[1]', 'varchar(200)') = 'John Doe'

-- Q4: consulta usando XQuery en una columna XML (susbtring) 
select clntGUID from test.clients 
where 
   colxml.value('contains(string((/hq/customerName1)[1]), "John Doe")', 'bit') = 1

La transformación de las consultas Q1 y Q2, en Q3 y Q4 respectivamente, puede hacerse con cierta comodidad hacienda búsqueda y sustitución de caracteres en el código fuente.

Evaluación de rendimiento

Para realizar las pruebas de rendimiento se instaló una copia de la base de datos en un servidor SQL Server 2005 de laboratorio. Se eligieron algunas de las tablas más intensamente consultadas por el sistema y se acotó el conjunto de filas a 100.000 en cada tabla. Se eligieron un par de consultas representativas de la aplicación:

Nota: Ranking y Nombre ambos son elementos almacenados en los datos XML.

Las consultas implementadas fueron:

Cada una de las consultas se realizó repetidas veces en una serie. De cada una de las cuatro series se descartaron los valores extremos (superiores e inferiores) y se realizaron promedios de los valores restantes que se obtuvieron. Cada serie se ejecutó a través de un procedimiento almacenado.

Durante la ejecución de cada serie se utilizó el SQL Profiler para capturar en una traza la siguiente información:

Las trazas de SQL profiler permitieron analizar el consumo de recursos de las consultas. No solamente interesaba saber cuáles eran las consultas con mejor tiempo de respuesta sino también que tuvieran un bajo consumo.

Resultados observados

La siguiente tabla resume los resultados promediales obtenidos:

En los siguientes gráficos observamos respectivamente el comportamiento de duraciones, consumo de CPU y cantidad de lecturas (la Serie 5 corresponde a una prueba testigo que no forma parte de este estudio):

Las principales conclusiones fueron:

Gustavo Larriera, MVP SQL Server

Mentor, Solid Quality Learning

Blog: http://www.solidqualitylearning.com/blogs/glarriera/

Blog en ingles: http://sqljunkies.com/weblog/gux/

Principio de la página