Mejorando el rendimiento de los DataSets

Usualmente cuando necesitamos trabajar con datos de una manera desconectada, nos valemos de los DataSets, llenados por un DataAdapter. Podemos seguir las siguientes recomendaciones para mejorar el rendimiento o performance de un DataSet.

La serialización de los DataSet fue mucho mejor implementada en el .NET Framework 1.1; sin embargo, conduce a cuellos de botella. Para evitar esos inconvenientes, tenemos varias alternativas:

Utilizar alias de columnas. Los datos serializados contienen los nombres de las columnas de manera que lo podemos utilizar para minimizar la serialización. De igual manera, si no vamos a necesitar los datos que se vayan modificando, se puede llamar al método AcceptChanges antes de serializar el DataSet para limpiar el buffer interno. Esto debido a que el DataSet mantiene historia de los datos originales.

Para las búsquedas, utilicemos mejor las claves primarias y Rows.Find; en lugar de DataTable.Select. La sentencia DataTable.Select no utiliza índices. Ya si nos toca efectuar búsquedas repetitivas sobre campos no indexados, es preferible utilizar un dataview, ya que se crea un índice automáticamente, lo que ayudará a mejorar el rendimiento de la aplicación.

En el caso anterior, si nos vamos por dicha estrategia, debemos utilizar el constructor del DataView que recibe como parámetros el Sort, RowFilter y RowStateFilter; de manera que nos aseguramos que el índice se crea una sola vez. En caso que creemos el dataset y luego vamos seteando las propiedades, el índice se crea por lo menos dos veces.

Otros tips para manejo de índices en SQL Server

Por lo general abusamos de la creación de los índices en el SQL Server. Para poder tener mejor manejo de los índices, tenemos algunos tips adicionales.

Es mejor crear los índices basados en el uso que se le van a dar. Muchas veces resulta muy tentador crear una tabla y su índice a renglón seguido, por lo general con el secuencial de por medio. O tal vez al momento que vemos una consulta muy lenta, le creamos un índice y punto. NO. Los índices pueden afectar negativamente negativamente a las operaciones de escritura. Lo mejor siempre aquí es tener conocimiento del sistema que estamos trabajando, la carga que va a tener, las consultas más conflictivas. Crear índices no es un arte, es fruto de la experiencia con el sistema.

Los índices “clustered” deben mantenerse pequeños. Esto debido a que los índices “non-clustered” almacenan la clave del índice clustered para ubicar las filas.

En los índices “clustered”, consideremos los rangos de datos. Si frecuentemente utilizamos comparativos de rangos en nuestras consultas, mediante operadores between o < ó >, sería bueno tener un índice clustered sobre el campo consultado. Es más, podríamos decir que todas las tablas deberían tener su índice clustered a menos que hayamos demostrado que afecta al performance.

Índices compuestos. Cuando creamos un índice compuesto –es decir, con varias columnas-, únicamente la primera almacena las estadísticas. Esto quiere decir que dicha columna deberá ser la más restrictiva. Si a pesar de ello el índice no es completamente selectivo, el motor no lo utilizará. Si alguna sentencia WHERE no utiliza todas las columnas del índice compuesto, es posible que el motor no utilice el índice.

Eliminar índices no utilizados. Como ya indiqué, las operaciones de escritura son afectadas por los índices. Tener índices de más, ocasionará que el sistema no responda de manera efectiva.

Index Tuning Wizard. Es una buena manera de analizar los índices, pero a mi en lo particular me gusta más basarme en la experiencia propia.

Common Table Expressions

Una de las ventajas que nos trae SQL Server 2005 es el uso de expresiones de tablas comunes, o common table expressions. Tratando de interpretarlo, es como un conjunto de datos resultante temporal. Es muy útil en momento de reemplazar subqueries o tablas derivadas.

En el siguiente ejemplo, vemos una CTE llamada mid, que calcula la mediana de un conjunto de datos.

WITH mid AS
(SELECT ((MAX(value) - MIN(value)) / 2)
AS midval FROM invoices)
SELECT
CASE WHEN value > mid.midval THEN 0
ELSE 1 END AS half, invoices.*
FROM invoices, mid ORDER BY half

Aquí podremos ver entonces de qué se componen los CTE. Empiezan con una cláusula WITH, y la expresión que vamos a tener la colocamos entre paréntesis. En caso que tengamos múltiples CTE's, las separamos por comas. Luego colocamos la sentencia SELECT.

La gran ventaja de esto es que si es utilizada más de una vez, nos provee muchos menos lecturas que un subquery. Por ejemplo:

WITH low AS (SELECT ((max(amount)) / 3)
AS v FROM invoices),
high AS (SELECT (2 * max(amount) / 3) AS v FROM invoices)
select id, amount, amount - low.v
FROM invoices, low, high
WHERE invoices.amount > low.v
AND invoices.amount <= high.v

SELECT id, amount,
amount - (SELECT (max(amount) / 3) FROM invoices)
FROM invoices where
amount > (SELECT (max(amount) / 3) FROM invoices) and
amount < (SELECT (2 * max(amount) / 3) FROM invoices)

Si comparamos los planes de ejecución, notaremos una mejora gracias a la CTE.

De la misma manera, nos ahorran grandes procedimientos. Por ejemplo:

WITH low AS (SELECT ((MAX(amount)) / 3)
AS v FROM invoices),
high AS (SELECT (2 * MAX(amount) / 3)
AS v FROM invoices)
SELECT id, amount, amount - low.v
FROM invoices, low, high
WHERE invoices.amount > low.v
AND invoices.amount <= high.v

En lugar de

declare @high int
declare @low int
SELECT @high = max(amount) FROM invoices
SELECT @low = min(amount) FROM invoices
SELECT id, amount, amount - @low FROM invoices

El resultado final será el mismo, pero la desventaja es que no puede ser utilizada en una vista; cosa que el CTE si puede hacer. En palabras algo más técnicas, la CTE se vuelve declarativa, mientras que el query equivalente es un procedimiento.

Densidad de los índices

Usualmente cuando creamos índices, pensamos siempre en aquellos que satisfacen las condiciones de la cláusula WHERE en las consultas más utilizadas. Esto no es una manera incorrecta de pensar, de hecho, es lo mejor que se puede hacer. Pero no es todo.

A parte de crear el índice de esa manera, debemos de pensar en un índice que sea altamente selectivo. ¿Qué es eso? Un índice altamente selectivo tiene gran cantidad de valores distintos. Por ejemplo, un índice en una tabla de clientes, sobre un campo “flag”, tendrá solamente 2 valores distintos (sí/no). Sin embargo, un índice por el número de identificación, puede tener muchísimos. En el primer caso, es muy probable que el motor del SQL Server no lo utilice, dado que no es muy selectivo.

Afortunadamente, se tiene el comando DBCC SHOW_STATISTICS que permitirá entender la selectividad de un índice. Como salida de este comando, tenemos una columna Density, la misma que se calcula dividiendo 1 para el número de valores distintos. De esta manera, un índice único tendrá una densidad de 1/número de filas. Así, una tabla de 1000 filas tendrá una densidad de 0.001. En el caso anterior, el índice sobre la columna “flag”, tendrá una densidad de 0.5. Entonces, mientras más pequeño sea el número, mayor será la selectividad.

El mejor número será el que devuelve la columna All Density.

Más información en: Statistics Used by the Query Optimizer in SQL Server 2000.