Transacciones: todo un dolor de cabeza

Este artículo aplica a SQL Server 2000.

Si de verdad te interesa mejorar enormemente el rendimiento de tu aplicación, es importante que te enfoques en manejar eficientemente las transacciones. Las transacciones bloquean recursos de manera que pueden bloquear otras transacciones. Algunos trucos efectivos al momento de manejar transacciones los voy a listar a continuación, esperando que les sean lo más útil posible.

Evitar transacciones de larga duración. Dado que las transacciones efectúan bloqueos, lo más barato es mantener las transacciones lo más cortas posibles. Igual, se puede iniciar transacciones en la capa de aplicación. Una técnica bastante aceptada –y que me encanta utilizar- es realizar todas las validaciones previas, antes de iniciar la transacción. Quizá debas volverlas a verificar durante la transacción, pero la ventaja es que se puede evitar que por una de esas condiciones tengas que hacer un rollback en media transacción.

Nunca, jamás, utilizar transacciones que requieran de ingresos del usuario. Nada más que agregar.

Los datos más utilizados deberán ser accedidos al final de la transacción. Es mejor que todas las operaciones de lectura, sean colocadas al inicio de la transacción; las escrituras al final; y los recursos más accedidos al último. ¿Adivinaste? La idea es que los bloqueos son más cortos en los recursos más accedidos. Adiós a los bloqueos largos.

Acceder a los recursos en el mismo orden. Los deadlocks son las cosas mas molestas en la base de datos. Para evitarlos, debemos de tratar de utilizar los recursos en el mismo orden, en todo nuestro sistema. Si no lo hacemos corremos alto riesgo de enfrentarnos a escenarios de deadlock bastante frecuentes. Conseguir un sistema sin deadlocks es una tarea casi imposible; pero el objetivo deberá ser tratar de llevarlos al mínimo, reduciendo el tiempo en el que los bloqueos son efectuados. Esto incluso podríamos tratarlo en el futuro. Hay bastante que aprender acá.

Utilizar los hints de aislamiento (isolation). Si la lógica del negocio lo permite, podríamos llevar el nivel de aislamiento más bajo. Lo más común es utilizar el hint WITH NOLOCK en las sentencias select.

Las transacciones explícitas deberán de hacer commit o rollback. Se debe tratar que exista el manejo de errores adecuado, de manera que se hagan el commit o el rollback necesario. Sino jamás se cerrará la transacción. Si se tienen sospechas de alguna transacción abierta, se puede utilizar la sentencia DBCC OPENTRAN, que devolverá las transacciones abiertas.

Sobre la administración de excepciones (2)

¿Sabías que puedes monitorear la cantidad de excepciones que tu aplicación está lanzando? Se puede hacer de dos maneras sencillas:

- Monitoreando periódicamente el log de excepciones, siempre que se haya implementado código para manejo de excepciones (similar a lo indicado en la entrada anterior).
- Con el Performance Monitor de Windows, se puede ver el valor del contador # of Exceps Thrown / sec que se encuentra en el objeto .NET CLR Exceptions. Digamos que para indicar un manejo adecuado, este valor deberá ser menor que el 5% de tus solicitudes por segundo.

Otra manera de evitar que las excepciones afecten al rendimiento de una aplicación, es hacerse cargo de aquellos recursos que son tomados y que al ocurrir una excepción podrían eventualmente quedar en memoria. Para esto pues utilizamos la cláusula finally. Con esto no aseguraremos que los recursos son liberados una vez ocurrida la excepción. De la siguiente manera:

try
{
conn.Open();
…
}
finally
{
If (null !=conn) conn.Close();
}

Y lo más importante, escribir código que evite las excepciones. Por ejemplo.

Verificar valores nulos. En caso de ser posible que un objeto tenga el valor null, habría que asegurarse que no es nulo, en lugar de lanzar una excepción. Esto comúnmente sucede cuando se tratan de consultar ítems del session state, view state, application state, u objetos del cache. Por ejemplo, NO conviene utilizar el siguiente código para acceder información a un objeto de sesión:

try{
loginid = Session[“loginid”].ToString();
}
catch (Exception ex){
Response.Redirect(“login.aspx”,false);
}

En su lugar, bien podríamos utilizar

if (Session[“loginid”]!=null)
loginid = Session[“loginid”].ToString();
else
Response.Redirect(“login.aspx”,false);

No utilizar las excepciones para controlar lógica. Las excepciones son eso – excepciones. Una falla en la conexión a una base de datos es una excepción. Un usuario que se equivoca en digitar el password es una mera condición que necesita ser manejada. Por ejemplo, el siguiente código:

public void Login(string UserName, string Password) {}

El siguiente código se utiliza para llamar al login.

try
{
Login(userName, password);
}
catch (InvalidUserNameException ex)
{…}
catch (InvalidPasswordException ex)
{…}

Es mucho mejor crear un enumerado de los posibles valores y cambiar el método login para que retorne la enumeración; algo así:

public enum LoginResult
{
Success, InvalidUserName, InvalidPassword, AccountLockedOut
}
public LoginResult Login(string UserName, string Password) {}

Y utilizar el siguiente código para llamar a Login:

LoginResult result = Login(userName, password);
switch(result)
{
case Success: …
case InvalidUserName: …
case InvalidPassword: …
}


Suprimir las llamadas internas a Response.End. Los métodos Server.Transfer, Response.Redirect y Response.End, levantan excepciones. Cada uno de ellos llama internamente al método Response.End. La llamada a Response.End, provoca una excepción ThreadAbortException. Si utilizas Response.Redirect, mejor utiliza el método recargado y pasa false al segundo parámetro para evitar la llamada interna a Response.End. Más información en: PRB: ThreadAbortException Occurs If You Use Response.End, Response.Redirect, or Server.Transfer.

No atrapar excepciones que no estamos manejando. Si el código no puede manejar una excepción, utiliza un bloque try/finally para asegurar que se cierran los recursos, aún si la excepción ocurre o no. No atrapes la excepción si no se intenta recuperar; antes bien, permítele propagarse para un manejador adecuado que pueda “lidiar” con la excepción encontrada.

Sobre la administración de excepciones

(Este artículo aplica para Visual Studio 2003).

Las excepciones son muy costosas. Al conocer las causas de la excepción, y escribir código a prueba de las mismas o que las maneje de manera eficiente, se puede incrementar drásticamente el rendimiento (performance) y escalabilidad de tu aplicación. Cuando se diseña e implementa manejo de excepciones, hay que analizar varios factores para asegurar un rendimiento óptimo. Así, por ejemplo, podríamos tener:

Implementar un administrador de errores en el Global.asax

El primer paso para administrar las excepciones es implementar un administrador global de errores en el archivo Global.asax o en el code-behind del mismo. Esto permite atrapar todas las excepciones que no hayan sido manejadas en la aplicación. Dentro del administrador por lo menos deberíamos preocuparnos de registrar la página que ocasiona el error, el información de la pila (call stack) y el nombre de la excepción y el mensaje que arroja.

Esto es muy sencillo de programar, en el evento Application_Error, de la siguiente manera:

public void Application_Error(object s, EventArgs ev)
{
StringBuilder message = new StringBuilder();
If (Server != null) {
Exception e;
For (e = Server.GetLastError(); e != null; e = e.InnerException)
{
Message.AppendFormat(“{0}: {1}{2}”,
e.GetType().FullName,
e.Message,
e.StackTrace);
}
// Registrar en el log la información
}
}

(La traducción a VB.NET se las dejo a ustedes).

Más información:

MSDN: Rich Custom Error Handling with ASP.NET
HOW TO: Create Custom Error Reporting Pages in ASP.NET by Using Visual C# .NET

Continuará…

Como reducir round trips

En la entrada anterior veíamos que una de las causas comunes de degradación del performance es el uso de muchos round trips en las páginas ASP.NET. Utilizando los siguientes tips, podríamos reducir su uso –y abuso-.

En medida de lo posible, agrupemos las sentencias SQL. Fallas en este sentido crean viajes adicionales –e innecesarios- a la base de datos. Pueden agruparse sentencias SQL separándolas con un punto y coma (;) o utilizando un stored procedure que ya lo haga. Los resultados se los puede ir leyendo con un objeto DataReader, con el método NextResult.

Para evitar round trips, se puede utilizar “connection pooling”. Al reutilizar las conexiones desde un pool, se evita los round trips que son necesarios debido al establecimiento de la conexión como tal y la autenticación. En esto podríamos ahondar un poco más en el futuro.

No retornar datos innecesarios. No me cansaré de repetirlo. Es absurdo. Si necesitamos un dato sencillo, podríamos utilizar el método ExecuteScalar. De igual manera se puede utilizar la sentencia ExecuteNonQuery, si queremos ejecutar sentencias de definición de datos (DDL), como la sentencia CREATE TABLE. Esto ayuda también a reducir el costo de crear el conjunto de resultados.

Se puede utilizar el caché (caching) para mantener los datos cerca del cliente en lugar de ir a la base en cada round trip.

Hay que tener cuidado de los round trips implícitos. Estos se producen en las operaciones que extraen metadata de la base de datos. Por ejemplo, deberíamos evitar el método DeriveParameters si ya conocemos información sobre los parámetros. Es mucho mejor –y eficiente- llenar la colección de parámetros explícitamente. La siguiente línea de ejemplo muestra una llamada que causa un round trip explícito.

SqlCommandBuilder.DeriveParameters(cmd)

Problemas de rendimiento y escalabilidad (¿No se les hacen conocidos?)

La siguiente es una lista de los problemas más comunes (inclusive más comunes de lo que pareciesen) que afectan enormemente al rendimiento y escalabilidad de la capa de datos en una aplicación.

-Queries ineficientes. Los queries que procesan y retornan más filas o columnas de lo necesario desperdician recursos del sistema que bien pueden ser aprovechados para servir otras peticiones. Peor aún si estos queries no toman ventajas de los índices existentes.

-Traer mucha información. Mucha información o data en los resultados, la mayoría de las ocasiones, es producida por consultas ineficientes. Un query con sentencia Select * usualmente provoca este problema. También vale la pena analizar la sentencia WHERE en las consultas para verificar que no se retornan muchas filas. Siempre se debe tratar de hacer la sentencia WHERE lo más específico posible.

-Indices ineficientes o no existentes. Los queries se dañan o degradan ante la inexistencia de índices dado que un “barrido” de la tabla (full table scan) debe ser ejecutado. Así, a medida que los datos van creciendo (como en todo sistema), las tablas pueden resultar fragmentadas. Aquí lo recomendables es tener un plan de reconstrucción de los índices, para evitar este escenario.

-“Round trips” innecesarios. No hay mucho que agregar. Un “round trip” siempre afecta al rendimiento o performance de la aplicación. Está pegado además al tráfico de la red y carga del servidor. Hay que tratar de mantener a los “round trips” lo mínimo posible.

-Muchas conexiones abiertas. Las conexiones son de por sí un recurso costoso, que debe ser compartido entre varios procesos utilizando “connection pooling”. Abrir una conexión por cada proceso limita terriblemente la escalabilidad. Evitemos tener conexiones abiertas y cadenas de conexión variantes.

-Liberación de recursos. Un problema común también resulta en la falta de liberación de recursos, lo que previene que sean reutilizados. Si no se cierran las conexiones antes que la conexión caiga fuera del alcance de la misma, no se libera mientras el objeto no se elimina de la memoria. Esto puede provocar presión sobre los recursos, lo que obviamente nos conduce a un problema.

-Abuso de transacciones. Si se escoge el tipo incorrecto de transacción, se puede añadir mucha latencia a cada operación. De igual manera, si mantenemos las transacciones abiertas por periodos largos de tiempo, resultará contraproducente. Si bien es cierto las transacciones son necesarias para asegurar la integridad de los datos, necesitas asegurarte que el tipo correcto de transacción está siendo utilizado.

-Mucha normalización. Esto puede provocar muchos joins entre las tablas. Obviamente afectará al rendimiento.

¿Cuántos tienes?

Tips para optimización de queries en SQL Server (2)

Otro tip para optimización de una consulta en SQL Server es evitar el uso de funciones explícitas o implícitas en el predicado de la cláusula WHERE. Las columnas en la cláusula WHERE son vistas como expresiones en lugar de columnas. De hecho, las columnas no son utilizadas en el plan de ejecución del optimizador. Un problema común es que las columnas cuyo tipo de dato es datetime. Si tienes una columna de dicho tipo en una cláusula WHERE, y necesitas convertirla o usar como una función de datos, trata de forzar a interpretarla como una expresión de caracteres.

El siguiente query, con una función en la columna datetime causa un barrido de tabla en la base de datos Northwind, inclusive con un índice en la columna OrderDate:

select OrderID from Northwind.dbo.Orders WHERE DATEADD(day, 15, OrderDate) = '07/23/1996'

Sin embargo, si “arreglamos” un poco el query, moviendo la función al otro lado del WHERE, el índice podrá ser utilizado en la columna datetime. De la siguiente manera:

select OrderID from Northwind.dbo.Orders WHERE OrderDate = dateadd(day, -15, '07/23/1996')

A continuación el plan de ejecución gráfico de ambas consultas. En el primer caso, el ícono corresponde a un barrido del índice (Index Scan), mientras que en el segundo corresponde a una búsqueda (Index Seek).



Una vez más, los números hablan por sí solos.

Las conversiones implícitas también pueden ser causa de “barridos”, dado a discordancia en los tipos de datos. Mucho ojo con los tipos de dato varchar y nvarchar, así como con los nchar y nvarchar, dado que pueden ejecutar una conversión implícita. Por ejemplo (la columna CustomerId es del tipo nchar):

declare @CustId char(5)
set @CustId = 'FOLKO'
select CompanyName from Northwind.dbo.Customers where CustomerId = @CustId

Arroja el siguiente plan de ejecución:



No es mucha la diferencia en performance, pero siempre es buena práctica manejar cuidadosamente los mismos tipos de dato.

Tips para optimización de queries en SQL Server (1)

Más que conocer trucos y sintaxis, escribir un buen query (consulta) en SQL Server exige un ejercicio constante en ser capaz de crear queries relacionales “elegantes”. Por lo general estos últimos dan mucho mejores resultados –claro, siempre que la base esté también correctamente relacionada, lo que podríamos ver luego-.

Pues aquí trataremos dos tips indispensables al momento de escribir consultas o queries en SQL Server. Dejaremos otros tips para el futuro, o si tienen alguno, coméntenlo sin problemas.

Consultar únicamente las filas y columnas requeridas

Puede sonar bastante obvio, pero a la vez es una estrategia bastante buena al momento de elaborar queries. Es muy común que alguna consulta devuelva o muchas columnas o muchas filas. Si estamos retornando muchas columnas, seguramente nuestro query está abusando del “SELECT * FROM …”. Pues noticia: estas columnas de la sentencia SELECT también son tomadas en cuenta por el motor de la base de datos (concretamente el optimizador), cuando evalua el índice a utilizar en la consulta y armar el plan de ejecución de la misma. A parte de retornar muchas veces datos irrelevantes, también puede forzar barridos de índices (index scan), en lugar de las restricciones que puedan surgir debido a la cláusula WHERE. Esto debido a que el costo de ir al índice agrupado (clustered index) para devolver el resto de datos de la fila después de utilizar un índice no agrupado (nonclustered index) para limitar los resultados, puede resultar más elevado que “barrer” el índice agrupado.

A continuación un ejemplo.

select * from northwind.dbo.Orders where OrderDate < '01/01/1996'
select Orderid from northwind.dbo.Orders where OrderDate < '01/01/1996'

Estos dos queries tienen los siguientes planes de ejecución:



En el primer caso, a pesar de existir un índice en la tabla por el campo OrderDate, notamos que hace un “barrido” del índice agrupado, para poder mostrarlos demás datos. En el segundo caso, como únicamente tiene que mostrar el campo OrderId, y dado que OrderId forma parte del índice agrupado, únicamente realiza una búsqueda sobre el índice OrderDate. Los números hablan por sí solos.

Evitar operadores costosos, como el NOT LIKE.

Algunos operadores, tanto en los joins como en los predicados del WHERE, tienden a producir operaciones muy costosas. El operador LIKE con un valor encerrado entre los comodines (“%valor%”) casi siempre producen un barrido de la tabla (ni siquiera del índice). Si lo utilizamos con únicamente un lado del comodín, podrá hacer uso del índice, dado que el índice es parte de un árbol B+ (para aquellos que pensaban que estructura de datos en la U no iba a servir, ¡toma!), y el índice es consultado haciendo comparaciones de izquierda a derecha.

Por Dios, ni se les ocurra utilizar operadores negativos (<>, NOT LIKE) dado que casi nunca el optimizador los resuelve eficientemente. Escriban las consultas de cualquier otra manera. Si por último, están tratando de verificar existencia, utilicen mejor el IF EXISTS o IF NOT EXISTS. Si ya están en la obligación de hacer un barrido, se puede detener el barrido en la primera ocurrencia.

Recuerden siempre primero medir sus resultados actuales y luego comparar contra el optimizado. Créanme que es un mundo de diferencia.

Sobre el origen de Best Application Performance

Best Application Performance es un blog –o bitácora- dedicado a la mejora del rendimiento de aplicaciones. Aquí trataremos diversos tópicos alrededor de cómo podemos hacer para mejorar en general el rendimiento –o performance- de una aplicación.

Desde ya están invitados a proponer temas, así como recurrir a los comentarios de algún tópico específico para poder revisarlos en conjunto y encontrar esas soluciones a veces tan esquivas.

Quien esto escribe es Guillermo Sornoza Ortega. Ingeniero de Sistemas, 29 años al momento de escribir esto. También hago comentarios sobre política y sociedad ecuatoriana en El Ecuador de Hoy, así como de cuando en cuando publico artículos en Bloggus.

Mis especialidades son el desarrollo de aplicaciones Web con ASP.NET; administración, diseño e implementación de base de datos, en particular SQL Server. De igual manera he trabajado y tengo amplia experiencia en desarrollo de DataWarehouse; así como desarrollo para dispositivos móviles y de escritorio. De igual manera poseo experiencia en administración de proyectos, utilizando metodologías formales de desarrollo.

Espero que el blog les sea lo más útil posible.