Grimpi IT Blog

febrero 24, 2009

Lo nuevo de SQL Server 2008: MERGE

Filed under: SQL Server, SQL Server 2008, T-SQL — Etiquetas: — grimpi @ 11:22 pm

Otra de las características interesantes que Microsoft incorporó en SQL Server 2008 y que al igual que la sentencia GROUPING SETS, ya existía en otros motores de bases de datos, es la clausula MERGE (de la cual habíamos hablado brevemente antes).
Esta cláusula nos va a permitir definir lógica de combinación para operaciones atómicas de inserción, borradas y actualización de datos.
La idea es comparar 2 conjuntos de datos y detectar las diferencias de datos entre las 2 tablas y en función de eso, ejecutar alguna operación de actualización sobre la tabla.
La clausula MERGE nos sirve básicamente para 2 cosas:

1) Sincronizar los datos de 2 tablas. Supongamos que tenemos 2 bases distintas (Producción y Desarrollo por ejemplo) y queremos sincronizar los datos de una tabla para que queden exactamente iguales. Lo que antes hubiese implicado
algunas sentencias mezcladas con INNER JOIN y NOT EXISTS, ahora es posible resumirlo en una operación atómica mucho
más sencilla y eficiente.

2) La otra razón por la cual podríamos usar MERGE, es cuando tenemos nuevos datos que queremos almacenar en una tabla y no sabemos si la primary key de la tabla ya existe o no, por lo tanto, no sabemos si hacer un UPDATE o un INSERT en la tabla. El famoso IF EXISTS… (tema que ya habíamos visto acá y que es un poquito más complicado de lo que parece a simple vista).

Veamos un ejemplo:

MERGE dbo.Tabla1 AS Target
USING (SELECT ID,Campo1,Campo2,Campo3 FROM dbo.Tabla2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN

UPDATE
SET Target.Campo1 = Source.Campo1, Target.Campo2 = Source.Campo2

WHEN NOT MATCHED BY TARGET THEN

INSERT (ID,Campo1,Campo2,Campo3)
VALUES (Source.ID,Source.Campo1,Source.Campo2, Source.Campo3)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

Realmente creo que es una de las cosas más interesantes que incorporó la nueva versión de SQL Server y es increíble que algo tan útil y tan práctico como esto, no hubiese estado antes.
Es importante entender que la ventaja del MERGE no es una simple cuestión sintáctica que nos permite escribir un par de líneas menos de código. La ventaja real y más importante está en que permite hacer sincronizar 1 tabla en función de una consulta, de manera atómica. Esto significa que si una operación de delete/insert/update falla, se hace un rollback de todo el conjunto de datos y más importante aún, se garantiza en escenarios de alta concurrencia, donde puede haber otros procesos escribiendo en la misma tabla en el mismo instante, no existan incoherencias.

Capturar salida:
Algo fantástico que existe a partir de SQL Server 2005, es la clausula OUTPUT que permite capturar todo lo que sucedió dentro de una operación INSERT/DELETE/UPDATE. En SQL Server 2008 el uso conjunto de MERGE + OUTPUT nos sirve saber que registros fueron modificados y que acción se hizo sobre ese registro (INSERT, UPDATE o DELETE).
La nueva función $action indica que operación se realizó, mientras que los atributos deleted e inserted guardan la información sobre el registro afectado (de la misma manera que funcionan con los triggers).

Ejemplo:

MERGE dbo.Tabla1 AS Target
USING (SELECT ID,Campo1,Campo2,Campo3 FROM dbo.Tabla2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN

UPDATE SET Target.Campo1 = Source.Campo1, Target.Campo2 = Source.Campo2

WHEN NOT MATCHED BY TARGET THEN

INSERT (ID,Campo1,Campo2,Campo3)
VALUES (Source.ID,Source.Campo1,Source.Campo2, Source.Campo3)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $action, deleted.*, inserted.*;

Resultado:

Avanzando en las profundidades del MERGE:
Si vemos el plan de ejecución, podemos encontrar cosas muy interesantes. En primer lugar, aparece un nuevo operador lógico en el plan, que se llama “Clustered Index Merge” y es nuevo de SQL Server 2008. Este operador en función de un conjunto de datos, realiza un delete, insert o update a una tabla usando su respectivo índice clustered. Si la tabla no tuviese un índice clustered, entonces se usaría el también nuevo operador “Table Merge”. Esta es una de las razones por la cual MERGE no es una simple encapsulación que simplifica cosas que ya podíamos hacer antes, sino que al ser una operación interna del motor de SQL Server, es más eficiente que cualquier otra alternativa existente en las versiones previas de SQL Server.
Otro punto interesante es que podemos ver es que cuando existen índices y las 2 tablas tienen similares volúmenes de datos, el operador MERGE JOIN resulta el método más eficiente en estas operaciones, ya ambas tablas son escaneadas una sola vez y no hay necesidad de ordenar los datos. Es posible cambiar estableciendo otro join hint, pero en la mayoría de los casos, el MERGE JOIN es lo más óptimo.


Algunas recomendaciones para obtener mejor performance con esta sentencia:

  • Crear un índice clustered sobre las columnas relacionadas en el JOIN, en la tabla destino.
  • Crear un índice único sobre las columnas relacionadas en el JOIN, en la tabla de origen (en caso que hubiese).

Esto garantiza al motor que no tiene que ejecutar ninguna validación adicional ni efectuar ningún sort extra.

Links:
http://technet.microsoft.com/en-us/library/cc879317.aspx
http://technet.microsoft.com/en-us/library/bb522522.aspx

noviembre 29, 2008

Lo nuevo de SQL Server 2008: Grouping Sets

Filed under: SQL Server, SQL Server 2008, T-SQL — Etiquetas: , — grimpi @ 4:33 pm

Una característica piola que tiene la nueva versión de SQL Server, es el nuevo operador GROUPING SETS, que ya existía en otros motores como Oracle hace tiempo (es un estándar ANSI 2006) y permite combinar consultas de agrupación distintas en una sola consulta. El operador GROUPING SETS es una extensión de la cláusula estándar GROUP BY.
Cuando no se requieren todas las agrupaciones posibles que se generan utilizando un operador ROLLUP o CUBE (que ya existían en SQL Server 2005), se debe utilizar GROUPING SETS para especificar sólo las agrupaciones que se deseen. O sea, gracias a GROUPING SETS obtenemos los niveles de agrupación deseados y además nos devuelve el subtotal para cada subconjunto de agrupación.
Podríamos decir que GROUPING SET es mas abarcativo y genérico que ROLLUP o CUBE. Generalmente consultas que usen este tipo de operadores están relacionadas con el análisis de datos, reporting y todo lo relacionado con el mundo BI.
Este nuevo operador no permite hacer nada nuevo que antes no se pudiese, solo simplifica y optimiza determinadas consultas, lo ya de por si, es un factor importante.
Veamos un ejemplo de donde utilizarlo:

Supongamos que tenemos la siguiente tabla de Clientes con datos ya cargados:

CREATE TABLE [dbo].[Client]
(

[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Zone] [varchar](50) NULL,
[Year] [int] NULL,
[Precio] [numeric](12, 4) NULL,

)

Y ahora supongamos que queremos traer la suma del campo Precio, agrupada por los Campos Country y Zone, luego solo por Zone y luego por el total y todo en una sola misma sentencia de tal manera que el resultado de la consulta fuese el siguiente:


La primera opción que uno piensa, es hacer una consulta usando UNION ALL con tres consultas diferentes, una por cada agrupación:

SELECT SUM(Value) AS Total, Zone, Country FROM dbo.Client
GROUP BY Zone,Country

UNION ALL
SELECT SUM(Value), NULL, Country FROM dbo.Client
GROUP BY Country

UNION ALL
SELECT SUM(Value), NULL, NULL FROM dbo.Client
ORDER BY Zone, Country

Sin embargo, podemos escribir una consulta equivalente y acá entra GROUPING SETS de SQL Server 2008 para simplificarnos la vida:

SELECT SUM(value) AS Total, Zone, Country FROM dbo.Client
GROUP BY
GROUPING SETS
(
(Zone, Country),
(Country),
()
)
ORDER BY GROUPING(Zone), GROUPING(Country)

Ahora como podrán ver en este último ejemplo, además de usar el operador GROUPING SETS, hago uso de la función GROUPING en el ORDER BY, para ordenar el resultado por Zona y Country.
Esta función (que ya existía en SQL Server 2005), nos indica si una expresión de columna especificada en una lista GROUP BY es agregada o no. GROUPING devuelve 1 para agregado y 0 para no agregado, en el conjunto de resultados.

Performance:

La ventaja del uso GROUPING SETS no está solo dada por simplificar sintácticamente las consultas, sino también en cuestiones de performance.
En las pruebas que hice para monitorear el uso de recursos (SET STATISTICS IO ON) con 90000 registros en la tabla Client estos fueron los resultados:

Usando UNION ALL:
Table ‘Client’. Scan count 3, logical reads 1728, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Usando GROUPING SETS:
Table ‘Client’. Scan count 1, logical reads 576, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver, GROUPING SETS hace menor uso de recursos de I/O. La razón de la mejor performance, se debe a que usando la nueva característica de SQL Server, el motor necesita leer menos paginas de datos, ya que hace el cálculo de agregación de más alto nivel sobre las agregaciones de menor nivel (usando como ejemplo nuestra consulta, el nivel de menor de agregación seria Zone y Country, luego solo Country y por último a partir del resultado de la prime).
Dejo la tarea al lector para que compare los planes de ejecución y pueda apreciar las diferencias en las operaciones que realiza el motor entre una consulta y otra.

Links:
Recomiendo leer el articulo de MSDN de equivalencias de GROUPING SETS.

septiembre 28, 2008

sql_expression_dependencies y Filtered Index en SQL Server 2008

Filed under: Metada, SQL Server, SQL Server 2008 — Etiquetas: , — grimpi @ 2:06 am

SQL Server incorpora montón de nuevas novedades, muy interesantes.
Una de ellas, son los Índices Filtrados, (Filtered Index), que permite indexar pero solo para determinado rango de valores dada una expresión. No es intención del post entrar en detalles de esto. Si quieren información al respecto, recomiendo ver este link.

Ahora, como hago para saber los objetos que están siendo referenciados en la expresión del filtro del índice? SQL Server 2008 incorpora una nueva vista de sistema, que se llama sys.sql_expression_dependencies y que contiene una fila para cada dependencia por nombre en una entidad definida por el usuario en la base de datos actual.

Dada esta vista, saber por ejemplo todas las columnas usadas dentro de expresiones en un índice filtrado, resulta sumamente fácil:

SELECT I.name AS IndexName, OBJECT_NAME(I.object_id) AS TableName, C.name
FROM sys.sql_expression_dependencies SED
INNER JOIN sys.indexes I ON I.object_id = SED.referencing_id AND I.index_id = SED.referencing_minor_id
INNER JOIN sys.columns C ON C.object_id = SED.referenced_id AND C.column_id = SED.referenced_minor_id

Para más información sobre esta Vista, ver este link.

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