Grimpi IT Blog

abril 23, 2008

Tablas temporales versus Variables de Tabla

Filed under: Engine, SQL Server — Etiquetas: — grimpi @ 5:40 pm

Una duda bastante comun entre los desarrolladores que trabajan con SQL Server, es saber
distinguir entre una tabla temporal y una variable de tabla.
Ambas estructuras presentan varias diferencias importantes, y fueron hechas para resolver distintos problemas, aunque en muchas situaciones, su uso puede superponerse.
Veamos algunas características de las variables de tabla y sus diferencias con las tablas temporales:

1) Contra lo que muchos suponen, el contenido de una variable de tabla no siempre esta completamente en memoria. En caso de que se inserte una alta cantidad de registros a la variable, se almacenará en tempdb. En cambio una tabla temporal siempre se guarda en tempdb.
Además, cuando se crea una variable tabla, es como si se efectuara cualquier otra operación DDL, por lo tanto la información de la metada se guarda en las tablas de catalogo.
Veamos este código:

DECLARE @TablaTemporal TABLE (ID int)
SELECT * FROM tempdb.sys.tables

Si ejecutamos esto, vemos que en la vista de catalogo sys.tables, aparece el objeto #XXXX, que es la variable de tabla que creamos.

2) No hay rollback en variables de tabla. Las operaciones contra un objeto de este estilo generan menos bloqueos y hacen menor uso del log de transacciones.
En caso de que se haga un INSERT/UPDATE/DELETE contra la variable, dentro de una transacción, al hacer ROLLBACK, el contenido seguirá siendo el mismo.
La ventaja de esto, es que obviamente es más rápido y consume menos recursos. La desventaja es que si necesitamos que sea transaccional la operación, no lo podemos hacer con este tipo de estructuras.
Ejemplo:

DECLARE @TablaTemporal TABLE (ID int)
BEGIN TRANSACTION
INSERT INTO @TablaTemporal (ID) VALUES (1)
INSERT INTO @TablaTemporal (ID) VALUES (2)
INSERT INTO @TablaTemporal (ID) VALUES (3)

SELECT * FROM @TablaTemporal
ROLLBACK TRANSACTION

SELECT * FROM @TablaTemporal

SELECT * FROM tempdb.sys.indexes I INNER JOIN tempdb.sys.tables T
ON I.object_id = t.object_id

Como podemos ver, el resultado devuelto en el ultimo SELECT es el mismo que el devuelto dentro de la transacción, por lo tanto, no efectuó el ROLLBACK.

3) No se pueden agregar índices en variables de tabla. Esto es una gran desventaja a la hora de trabajar con un conjunto gran de datos. Sin embargo, existe una manera de tener índices. Para eso, hay que definir una PRIMARY o UNIQUE KEY cuando se crea la tabla, aunque claro, no siempre este workaround nos sirve. No es posible declarar un NON-CLUSTERED index en una variable de tabla.
Con una tabla temporal en cambio, se puede efectuar las mismas operaciones que una tabla común, incluyendo el poder agregar índices.
Ejemplo de como crear una variable de tabla con índices:

DECLARE @TablaTemporal TABLE (ID int, PRIMARY KEY ([ID]))

Si ejecutamos la consulta que esta mas arriba, vamos a ver el índice de la variable de tabla en las vista de catalogo de tempdb.

4) Otro ítem importantes es que un store procedure que haga uso de tablas temporales, puede que se recompile seguido (dependiendo de como varían las estadísticas), mientras que un store que haga uso de variables de tabla no va sufrir recompilaciones. Si el store es ejecutado muy seguido o es lo suficientemente largo para que se note el tiempo de recompilación, puede llegar a ver una diferencia de performance.
De todos modos, en SQL Server 2005 se mejoro la forma en que se precompilan los store procedures que usan tablas temporales, mejorando la performance…

5) Las variables de tabla no usan paralelismo (multiple threads) en su plan de ejecución. En tablas grandes o en alta concurrencia, esto impacta en la performance, haciéndolas mas lentas en comparación con las tablas temporales.

6) Las variables de tabla tampoco recolectan estadísticas. Esto puede ser bueno y malo a la vez. En caso de tener tablas chicas, las estadísticas no son tan necesarias, por lo cual, el no tener que recolectarlas hace las operaciones de escritura mas rápidas.
Pero por otro lado, al no tener estadísticas, el plan de ejecución que genera el SQL puede no ser el más óptimo.

7) El scope de una tabla temporal es la conexión a la base. Todos los objetos de la base, puede ver el contenido de esta tabla, mientras persista la misma conexión.
Mientras que el scope de una variable de tabla es local solo el store procedure en que se la llama, al igual que una variable común.

CONCLUSION:
No se puede decir que las tablas temporales son peores o mejores que las variables de tabla. Siempre hay que evaluar las 2 alternativas.
Sin embargo, podríamos afirmar que, en operaciones con muchos datos, las tablas temporales tienen más herramientas para ofrecer mejor performance, mientras que para procesos chicos, con pocos registros, las variable de tabla son mejores, al tener menos overhead.

Anuncios

abril 9, 2008

El patrón DAO

Filed under: .NET, Arquitectura, Patrones, SQL Server — grimpi @ 1:12 am

Yo no soy un fanático de implementar montón de patrones y capas a los sistemas. Hoy en día hay sistemas que para desarrollar una función, requieren escribir en hasta 7 archivos diferentes.
Muchas veces complica más las cosas de lo necesario.
Sin embargo, uno de los patrones en mi opinión “fundamentales” del desarrollo de software, es el DAO (Data Access Object).
El DAO maneja la conexión con la fuente de datos para obtener y almacenar datos.

El uso de este patrón ofrece varios beneficios para la persistencia de datos:
* Sirve para separar el acceso a datos de la lógica de negocio. Algo altamente recomendable en sistemas medianos o grandes, o que manejen lógica de negocio compleja.
* Encapsula la fuente de datos. Esto es especialmente beneficioso en sistemas con acceso a múltiples entradas.
* Oculta la API con la que se accede a los datos. Por ejemplo en .NET si usamos siempre OleDb y queremos cambiar al NHibernate.
* Centraliza Todos los Accesos a Datos en un Capa Independiente

Cuando trabajamos con DAO, trabajamos en un mundo donde desconectado, donde nuestros datos se deben persistir en objetos.
Por lo tanto, cuando nos piden realizar una operación, se abre la conexión a la base, se ejecuta el comando, si es una operación de lectura, se vuelca el contenido hacia una estructura de datos y se cierra la conexión.

Transacciones:
Un problema muy común cuando se trabaja con este patrón, es como usar las transacciones.
Se deben ejecutar todas las operaciones dentro de un método?
Puede un DAO invocar métodos en otro DAO? Quien y como se deben manejar las transacciones?
Bueno, todo esta problemática que define el límite de la transacción se llama “Transaction demarcation”.
Existen 2 modelos diferentes que aplican según la necesidad de la operación transaccional que se va a efectuar.
Uno hace al objeto DAO responsable de la transacción, el otro desplaza la transacción al objeto que esta llamando el método DAO.
El primer caso (y el más usual) se puede codificar de la siguiente manera en C#:

public class ProductoDAO
{
public void Eliminar()
{
SqlConnection connection = new SqlConnection(“CONEXION”));
conn.open();
using (SqlTransaction trans = new SqlTransaction(conn))
{
SqlCommand com = new SqlCommand(“DELETE FROM P1”, conn, trans);
com.Execute();
SqlCommand com2 = new SqlCommand(“DELETE FROM P2”, conn, trans);
com2.Execute();
SqlCommand com3 = new SqlCommand(“DELETE FROM P3”, conn, trans);
com3.Execute();
trans.Commit();
}

conn.close();
}

}

En este ejemplo, para eliminar todos los productos y tablas relacionadas, se llama al objeto que maneja las transacciones de ADO.NET.
Con lo cual es perfectamente lógico encapsular el borrado de todas las tablas en un mismo método DAO, ya que se está afectando solamente la entidad Producto.

Pero supongamos que queremos eliminar transaccionalmente además de todos los productos, todas las ventas y todas las agencias.
Seria correcto meter la lógica del borrado de Ventas y Agencias dentro del método EliminarProducto? Como funcionar, funcionaria obviamente, pero estaríamos mezclando operaciones de distintas entidades y peor aún, estaríamos agregando métodos poco reutilizables y confusos.
Entonces lo correcto en este caso sería meter la lógica de la transacción fuera del DAO.
Para poder implementar esto, es necesario usar Transacciones distribuidas que .NET se implementan con EnterpriseService (En Java existe JTA). En caso de que usemos SQL Server 2005, podemos simplificar su considerablemente.

public class Negocio

{
public static void EliminarTodo
{
using (TransactionScope scope = new TransactionScope())
{
VentasDAO.Eliminar();
AgenciasDAO.Eliminar();
ProductoDAO.Eliminar();
scope.Complete();
}
}
}

Pasaje entre capas:
Ahora bien, ya sabemos porque debemos usar DAO y que beneficios nos trae.
Pero como deben ser el pasaje de datos con esta capa? Qué tipo de estructura usar?
En .NET existen varias variantes:

1) Dataset (tipado o no tipado)
2) XML
3) Data Transfer Objects (o objetos de Entidad)

Dependiendo de las particularidades de la aplicación, una opción puede ser mejor que la otra.
Definitivamente en la mayoría de los casos, usar Dataset no es la mejor práctica. Estamos de acuerdo que tal vez sea la opción más “cómoda”, pero los dataset son objetos pesados y lentos (en comparación con un Datareader), tienen montón de información, propiedades y métodos que no necesitamos.
Pero lo peor no es eso, la principal desventaja es que fuerza a toda la aplicación a usar un objeto de una tecnología especifica.

La más común de todas, es usar los Data Transfer Objects (DTO), también conocido como Value Object (VO). Es el típico patrón asociado a DAO.
Que son los DTO? Bueno, no son más que simples objetos que solo tienen getters y setters, que sirven para transportar la información de una capa a otra. El usar este patrón, ganamos performance, ya que al ser objetos livianos ocupan menos memoria, además de que nos permite abstraernos de cualquier tecnología especifica.

Recomendaciones a la hora de implementar DAO:

1) Combinar con el patrón singleton:
Tiene sentido tener múltiples instancias de DAO en memoria?
En la mayoría de los casos no, por eso yo por lo menos, suelo declarar todas mis clases DAO como static.

2) Crear una interfaz y un factory:
Todos los DAO generalmente tienen métodos comunes a todos, como DeleteById, GetById, GetAll, etc.
Declarar una interfaz IDAO, nos permitiría hacer un factory de DAOs.

3) Evitar el uso de dataset:
Si, como dije antes, es cómodo usarlos, nos ahorran tiempo de desarrollo. Pero a un costo alto.

Links:
http://www.ibm.com/developerworks/java/library/j-dao/
http://www.miguelmatas.es/blog/2007/11/13/mejorando-nuestro-dao-y-dto/
http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.htm

abril 3, 2008

Soluciones para actualizar un registro si existe, sino insertar en SQL Server.

Filed under: SQL Server, T-SQL — grimpi @ 2:06 am

Muchas veces cuando trabajamos con ABMs o algún proceso de escritura en la base de datos, al actualizar los registros, debemos establecer si vamos a efectuar un INSERT o un UPDATE. O sea, tenemos que determinar si el registro existe o no, para saber que operación se va a efectuar en la base de datos.

Generalmente se suele encapsular toda esta lógica dentro de un SP, algo que considero una muy buena practica, ya que nos desentendemos del lado de la aplicación, si se va a efectuar una operación de inserción o de modificación.

Primera solución:

Ahora bien, dentro del Store Procedure, lo solemos hacer para determinar la operación, es el famoso IF EXISTS.
Ejemplo:

IF EXISTS(SELECT ID FROM TABLA WHERE ID = @ID)
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)
ELSE
UPDATE
TABLA SET Campo1 = @Valor WHERE ID = @ID

No es un mal enfoque, es muy claro. Sin embargo esta solución tiene dos inconvenientes:
1) Estamos pagando el costo de ejecutar un Query. Por mas que la consulta este indexada, tiene un costo.
2) No es 100% segura. En entornos muy demandantes, con alta concurrencia, puede darse el caso de que justo luego de ejecutar el IF EXISTS, otro proceso inserte en la tabla un registro con la misma PK y no tendríamos forma de darnos cuenta, generando un error de duplicate key.
Por lo tanto, esta opción que es la más común, tiene serios inconvenientes.

Segunda solución:

Una segunda opción podría ser esta:

UPDATE TABLA SET Campo1 = @Valor WHERE ID = @ID
IF @@ROWCOUNT = 0
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)

En caso, se eliminaría el tener que ejecutar una query con el EXISTS. Aunque en caso de que no exista el registro, se ejecuta el UPDATE innecesariamente.
Si la mayoría de las operaciones van a ser del tipo INSERT, en realidad no se ganaría performance, pero por el contrario, si la mayoría de las operaciones seria del tipo UPDATE, podría llegar a ser mas performante.
De todas maneras esta solución sigue teniendo el problema de que otro proceso podría insertar un registro con la misma PK en la tabla y no tendríamos forma de darnos cuenta.

Tercera solución:

En el segundo caso ganamos un poco de performance (no siempre), pero seguimos con el mismo problema de concurrencia.
Pero ahora veamos este ejemplo de código:

BEGIN TRY
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)
END TRY
BEGIN CATCH
UPDATE TABLA SET Campo1 = @Valor WHERE ID = @ID
END CATCH

A nivel perfomance, es similar a las otras soluciones, pero si tenemos muchas más operaciones de inserción que de actualización, vamos a ganar velocidad.
Sin embargo, en este caso no tendríamos el inconveniente de concurrencia que sucede en los 2 casos anteriores!!. Lo cual lo hace ideal para situaciones de alta demanda.

Cuarta solución (Solo en SQL Server 2008):

SQL Server 2008, incorpora el comando MERGE (que ya teníamos en Oracle y otros motores), que sirve para resolver de una manera muy eficiente, exactamente este problema.

MERGE TABLA
USING (SELECT @ID AS ID) AS SRC ON SRC.ID = TABLA.ID
WHEN MATCHED THEN
UPDATE SET Campo1 = @Valor
WHEN NOT MATCHED THEN
INSERT (Campo1,ID) VALUES (@Valor,@ID)

Con este método, también solucionamos el problema de concurrencia, y además evitar tener que ejecutar consultas innecesarias. Por lo cual, podríamos decir que es la optima solución resolver este problema, aunque lamentablemente debemos esperar hasta mitad de año, cuando Microsoft libere SQL Server 2008.

Conclusión:

Vimos como un problema en apariencia tonto y trivial, puede causar serios problemas de performance y peor aun, crear errores de concurrencia y comportamientos no deseados.
Por las pruebas que hicimos en un entorno de TEST, la diferencia de performance que hicimos no son demasiadas. Pero en situaciones de alta concurrencia, las 2 primeras soluciones son definitivamente incorrectas.

Recomiendo ver estos links, que explican como funcionan los lockeos, en cada una de las distintas soluciones:

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx
http://weblogs.sqlteam.com/mladenp/archive/2007/08/03/60277.aspx

Y estas soluciones alternativas al mismo problema, tal vez sean un poco más complejas, pero en algunos escenarios pueden ser útiles:

http://www.samsaffron.com/blog/archive/2007/04/04/14.aspx
http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

Crea un blog o un sitio web gratuitos con WordPress.com.