La versión de SQL Server 2008 trajo consigo muchísimas novedades. De entre todas las que podríamos considerar enfocadas al desarrollo, he creído interesante hablar de dos de ellas, que combinadas consiguen un resultado bastante más que interesante en la mayoría de situaciones. En este artículo, veremos como plantear una solución óptima de modificación de datos utilizando las características “TVP” (parámetros de tabla) y la sentencia MERGE. Cada una de las dos, por si solas son interesantes para proporcionar solución a determinados problemas, pero juntas nos dan la posibilidad de escribir un código que además de sencillo y limpio, es realmente eficiente desde el punto de vista de la escalabilidad.
En la realización de este artículo he contado con:
La solución es compatible con SQL Server 2008
– Powershell 2.0 sobre Windows Server 2008 R2
La razón de utilizar Powershell no es otra que la de dar la opción al lector de realizar rápidamente las pruebas que crea convenientes sin disponer de código compilado ni un compilador cerca. Se sobreentiende que el lenguaje de programación de la aplicación cliente es indiferente.
– Sql Server Native Client 2008 o superior.
Puesto que vamos a hacer uso de características propias de SQL Server 2008, si nuestra instancia de SQL Server no se encuentra en local, debemos cerciorarnos que disponemos de los drivers de conexión a SQL Server instalados en el equipo que contiene el código de aplicación cliente.
Problemática a resolver
El tipo de problemas que pretendemos resolver con estas características es ampliamente conocido: Mi aplicación quiere propagar “n” modificaciones a mi base de datos.
Un caso típico de esta situación lo podemos ver resumido así:
- Aplicación posee estructura en memoria con la información a procesar
Por ejemplo, internamente existe un DataTable con “n” filas gestionadas por nuestra aplicación - Usuario modifica la información (usa la aplicación y cambia ciertos valores)
Esto implicaría que por ejemplo, el código de nuestra aplicación modificará nuestro DataTable en memoria. Todavía no propagamos datos. - Después de realizar cualquier acción más, el usuario pulsa sobre el botón “Guardar”
Esto implicaría que todos los cambios realizados al DataTable sean propagados a nuestra base de datos.
Como vemos, el problema a resolver es muy común.
Planteamiento de la solución
Lo habitual en estos casos es que nuestra aplicación, mande “n” peticiones de actualización a nuestra base de datos. Tantas peticiones de actualización, como filas en nuestro DataTable tengamos. Esto quiere decir, que para cada una de esas llamadas, SQL Server va a tener que realizar un procesamiento, que en el mejor de los casos será el siguiente (simplificando):
- Capa de acceso a datos de aplicación detecta que nuevas filas deben insertarse
- Capa de acceso a datos de aplicación envía “n” operaciones “insert”
- Capa de acceso a datos de aplicación detecta qué filas han sido actualizadas
- Capa de acceso a datos de aplicación envía “n” operaciones “update”
- Capa de acceso a datos de aplicación detecta qué filas han sido eliminadas
- Capa de acceso a datos de aplicación envía “n” operaciones “delete”
Por su parte, SQL Server va a tratar a todas y cada una de esas operaciones, de forma independiente. Esto quiere decir que por ejemplo cuando lanza una actualización o un borrado, al no disponer del resto de filas a actualizar y borrar, no podrá plantearse una estrategia de bloqueos óptima. Quiero decir con esto, que cada operación es independiente del resto y por lo tanto SQL Server las tratará como tal.
¿Qué son los TVP o parámetros de tabla?
Los parámetros de tabla ó TVP (Table-valued parameters) son un nuevo tipo de parámetro que apareció en SQL Server 2008. Estos parámetros se declaran como tipos de datos tabla definidos por el usuario y puedes utilizarlos para enviar múltiples filas desde una instrucción T-SQL, procedimiento o función sin necesidad de crear una tabla temporal o realizar cualquier estrategia diferente.
Debido a su naturaleza, los escenarios típicos de uso en los que tienen aplicación directa son:
- Actualización en lotes del servidor
- Parámetros en lote para usar en consultas
- Pasar una tabla entre rutinas
- Migración de otros motores de bases de datos
Esto quiere decir que están pensados para resolver soluciones que impliquen enviar gran cantidad de datos al servidor y para facilitar un procesamiento lógico en el mismo, previo a una actualización de datos persistente. Justo lo que buscamos en nuestro caso puesto que gracias a esta característica, vamos a poder enviar datos tabulares entre ambos extremos (cliente y servidor), lo cual nos va a facilitar el trabajo por conjuntos de resultados ya que en lugar de procesar fila a fila, vamos a poder realizar soluciones orientadas a conjuntos de filas (una única petición de actualización por tanto):
El uso de los parámetros de tabla lo podemos resumir en:
- Empaquetado de la lógica de negocio
- Ejecutar lógica de negocio en el servidor
- Mejor modelo de programación
- Operaciones basadas en conjuntos
- Transporte de datos eficiente
- Se pueden enviar datos ordenados por clave única para optimizar el procesamiento
- Reducción de viajes de ida y vuelta al servidor
- Pueden enviarse múltiples lotes para conseguir un buen balance concurrencia/rendimiento
Esto es recomendable analizarlo puesto que dependiendo del tipo de operación a realizar, en ocasiones es mejor dividir el procesamiento y en lugar de una única llamada con 100.000 filas, conviene realizar 10 de 10.000 por ejemplo. Cada escenario es diferente, pero en la siguiente tabla (que proviene de los libros en pantalla de SQL Server) se puede ver una comparativa para situarnos.
- Pueden enviarse múltiples lotes para conseguir un buen balance concurrencia/rendimiento
- Tipado de datos fuerte
- Tipo de datos seguro: Intercambiado con el servidor
- Información de tipos compleja, fácil de utilizar y sin impactos de rendimiento
¿Qué es la cláusula MERGE?
La cláusula Merge, no es solo una cláusula DML normal, podemos verla como una “super-cláusula” DML. Gracias a ella, se pueden realizar en una única instrucción: inserción, borrado y actualización de un conjunto de datos. El propósito para el que vamos a querer utilizar esta cláusula será entonces para optimizar las cargas de datos provenientes de nuestra capa de acceso a datos de la aplicación.
Sin entrar en detalles, la cláusula MERGE podemos seccionarla en 5 partes con la que construir nuestra “super-cláusula”:
|
MERGE |
Especifica el objetivo a actualizar |
|
USING |
Especifica la fuente de información |
|
ON |
Especifica la unión entre la fuente y el destino |
|
WHEN |
Especifica las acciones a acometer mediante reglas MATCH (INSERT, UPDATE, DELETE) |
|
OUTPUT |
Cláusula opcional, que devuelve valores de información nuevos y antiguos usando los objetos “inserted“ y “deleted“ |
Las ventajas más interesantes de la cláusula MERGE para nuestro propósito son:
- Permite actualizar nuestros datos conociendo el bloque completo a modificar. Gracias a esta información, será capaz de generar planes de ejecución basados en el lote completo puesto que tiene información de qué rango de filas van a ser modificados de golpe.
- Nos permitirá especificar el nivel de detalle que queramos en la propia cláusula.
De esta forma, podremos evitar actualizar información que no haya sido modificada por ejemplo, sin hacer uso de bifurcadores de código T-SQL ni estructuras similares.
- Podremos devolver a la aplicación cliente si lo deseamos, el conjunto de acciones que finalmente se ha realizado, así como el valor anterior y posterior a la modificación. (ver palabra reservada $action en la descripción de la cláusula MERGE)
Ejemplo práctico
Como ejemplo práctico, vamos a resolver la problemática de obtener los eventos de inicio y apagado de un equipo. Este escenario nos viene bien para ilustrar la solución, puesto que los datos de inicio-fin varían poco, y podemos mandarlos siempre de nuevo al servidor y ahí tratar solo con la información nueva. Recordemos que es un ejemplo de demostración, obviamente cada problemática puede ser diferente pero la esencia es:
- Aplicación cliente posee información a actualizar al servidor
- A priori no sabemos qué información se actualizará ni como
- Deseamos aprovechar las nuevas capacidades de SQL Server 2008 para conseguir un proceso mas escalable
En este caso particular, en principio, podríamos pensar que podemos gestionar nosotros mismos los cambios. Obviamente esto es así y para ello deberíamos conocer el último evento que existe almacenado y solo enviar los nuevos…pero eso no es lo que queremos ahora mismo mostrar en este ejemplo. En este ejemplo, aprovecharemos TVP y MERGE para enviar todos los eventos de nuevo y gestionarlos en el procedimiento almacenado para descartar los ya existentes. Estamos por tanto, viendo el escenario que comentábamos al principio: “la aplicación modifica DataTable y envía todo”.
Evidentemente, si solo enviamos los cambios, pues la ganancia será mayor.
El código del lado del servidor SQL Server
Lo primero será por tanto crear la tabla donde almacenaremos la información. Esta será muy sencilla:
– insert_date: Esto nos vendrá bien para saber cuándo se insertó esta fila. En principio irrelevante, pero quizás algún día resulte útil
– server_name: Nombre del equipo
– event_type: Tipo de evento: capturaremos inicio y fin
– event_time: Hora del evento
- create table dbo.WindowsStartAndStopEvents
- ( insert_date smalldatetime not null default(getdate()),
- server_name varchar(255) not null,
- event_type varchar(255) not null,
- event_time datetime2 not null)
- go
Ahora, crearemos un índice agrupado, que cubrirá las futuras consultas que queremos hacer (no llegaremos a esa parte en este artículo, pero está bien crearlos):
- create clustered index ci_WindowsStartAndStopEvents on dbo.WindowsStartAndStopEvents(server_name,event_type)
- go
Preparándonos para el procedimiento almacenado que recibirá por parámetro un tipo tabla, lo siguiente que deberemos hacer es crearnos dicho tipo de datos tabla que queremos que nuestro futuro procedimiento almacenado reciba. En este caso, vamos a crear un tipo tabla con las mismas columnas que nuestra tabla destino previamente creada (sobra decir que la columna insert_date no es necesario asignarla puesto que su valor se asigna en el momento de la inserción gracias a que hemos especificado un valor por defecto).
- CREATE TYPEdbo.WINDOWS_EVENT_TYPE as table (
- server_name varchar(255) not null,
- event_type varchar(255) not null,
- event_time datetime2 not null
- )
- GO
En este momento, podemos ver que dicho tipo de datos ha sido creado y podremos declarar variables con él, como si se tratara de un tipo de datos definido por usuario más:
- declare @ejemplo dbo.WINDOWS_EVENT_TYPE
- insert into @ejemplo values (‘server_name’,‘event_type’,‘20110101’)
- select * from @ejemplo
Ahora, el código de nuestro procedimiento almacenado, utilizará como parámetro dicho tipo de datos tabla, que obviamente nuestra aplicación enviará relleno:
- create procedure dbo.insert_WindowsStartAndStopEvents @windows_event_table WINDOWS_EVENT_TYPE READONLY
- as
- begin
- MERGE dbo.WindowsStartAndStopEvents as target
- USING @windows_event_table as source
- ON target.server_name = source.server_name
- and target.event_type = source.event_type
- and target.event_time = source.event_time
- WHEN NOT MATCHED THEN
- INSERT (server_name,event_type,event_time)
- values (source.server_name,source.event_type,source.event_time)
- ;
- end
- go
Si recordamos el problema que queremos solucionar, sabemos que estamos enviando eventos que no varían, junto con eventos nuevos. En ese escenario concreto, solo nos interesa conocer qué filas existen en origen (lo que nuestra aplicación envía) que no existen en destino (la tabla llamada dbo.WindowsStartAndStopEvents). Por lo tanto, solo debemos crear una sección WHEN NOT MATCHED en la que indiquemos que la acción será insertar las filas del origen en el destino.
Por otro lado, en este escenario no nos interesa cubrir acciones de modificación de filas ni eliminación, porque sabemos de antemano el tipo de información que enviaremos (solo incremental). Cada problema es diferente, pero se solucionará adaptando la sentencia MERGE a cada caso particular. Otra solución podría pasar por crear un código tipo “INSERT … FROM SELECT”, pero lo interesante es ver que en este caso simplemente adaptando la sentencia MERGE, podemos dar solución a cualquier escenario.
El código del lado cliente
Como se puede leer en la introducción del artículo,, la razón de crear código powershell no es otra que el lector pueda realizar las pruebas en un entorno interpretado, sin necesidad de compilar código .NET. En cualquier caso, se han utilizado objetos ADO.NET desde powershell, para que la metodología sea similar (mismos métodos y objetos) a la de utilizar cualquier lenguaje .NET.
- # Enrique Catalá Bañuls:
- # Código de ejemplo para obtener los eventos de inicio y fin de servicio de SQL Server efectivos.
- #
- # Esta función crea una cadena de conexión contra SQL Server
- #
- function getStringConnection([string]$instanceName,[string]$user,[string]$pass,[string]$database)
- {
- if(($user –ne $null -and $user –ne ”) -and ($pass –ne $null -and $pass –ne ”))
- {
- $strCon=“Data Source=$instanceName;User ID=$user;Password=$pass;Integrated Security=TRUE;Initial Catalog=$database”
- }
- else
- {
- $strCon=“Data Source=$instanceName;Integrated Security=TRUE;Initial Catalog=$database”
- }
- return $strCon
- }
- # nos viene bien tener un nombre de instancia bien dado (nombremaquinanombreinstancia)
- #
- function getInstanceName([string]$ProviderName)
- {
- if($ProviderName –eq “MSSQLSERVER”)
- {
- $retorno = $Env:computername
- }
- else
- {
- $retorno = $Env:computername+“”+$ProviderName.Substring($ProviderName.IndexOf(‘$’)+1)
- }
- return $retorno
- }
- ##
- ## MAIN BODY
- ##
- try
- {
- # Asigno la instancia y el nombre de BBDD donde he creado los objetos anteriores
- #
- $instance=“(local)sql2008r2”
- $catalog=“tempdb”
- # Obtenemos los eventos interesantes de SQL Server para nuestro ejemplo:
- # 17126: Evento que indica que SQL Server ya acepta conexiones entrantes (está online)
- # 17147: Evento que indica que SQL Server ha dejado de aceptar conexiones (está offline oficialmente)
- #
- $events= Get–WinEvent –FilterXml “
- <QueryList>
- <Query Id=’0′ Path=’Application’>
- <Select Path=’Application’>*[System[(Level=4 or Level=0 or Level=5) and (EventID=17126 or EventID=17147)]]</Select>
- </Query>
- </QueryList>” | Sort–Object –Property “TimeCreated”
- # Creamos un objeto DataTable que contendrá la información que simulamos nuestra aplicación gestionará.
- # Obviamente, debe cumplir el esquema del tipo de datos definido anteriormente que hemos llamado WINDOWS_EVENT_TYPE
- #
- $table = New–Object system.Data.DataTable “$TableName”
- $col0 = New–Object System.Data.DataColumn InstanceName,([string])
- $col1 = New–Object system.Data.DataColumn Type,([string])
- $col2 = New–Object system.Data.DataColumn Date,([datetime])
- #Añadimos las columnas al objeto DataTable
- #
- $table.Columns.Add($col0)
- $table.columns.add($col1)
- $table.columns.add($col2)
- # Ahora simplemente, recorremos la lista de eventos y dependiendo del tipo de evento,
- # indicaremos qué haremos con el
- #
- foreach($event in $events){
- # Eventid 17126 is sql indicating that can accept connections
- #
- if($event.Id –eq 17126){
- $dr = $table.NewRow()
- $dr[0]=getInstanceName $event.ProviderName
- $dr[1]=“Started”
- $dr[2]=$event.TimeCreated
- $table.Rows.Add($dr)
- }
- # Eventid 17147 is sql indicating stop event
- #
- elseif($event.Id –eq 17147)
- {
- $dr = $table.NewRow()
- $dr[0]=getInstanceName $event.ProviderName
- $dr[1]=“Stoped”
- $dr[2]=$event.TimeCreated
- $table.Rows.Add($dr)
- }
- }
- # Una vez procesada la información, nos encontramos con que tenemos el DataTable listo
- # para mandar las actualizaciones a la Base de datos
- #
- $SqlServer = $instance
- $SqlCatalog = $catalog
- # Creamos el objeto SqlConnection y abrimos conexion
- #
- $SqlConnection = New–Object System.Data.SqlClient.SqlConnection
- $SqlConnection.ConnectionString = getStringConnection $instance $user $pass $catalog
- $SqlConnection.Open()
- # Indicamos qué procedimiento almacenado utilizaremos para inserter la información
- #
- $cmd = New–Object System.Data.SqlClient.SqlCommand(“dbo.insert_WindowsStartAndStopEvents”,$SqlConnection)
- $cmd.CommandType = [System.Data.CommandType]‘StoredProcedure’
- # Aqui está la magia: Indicamos que el parámetro @windows_event_table es un tipo de datos “Structured”
- # y le decimos que su valor es el objeto $table (el datatable)
- #
- $parameter =$cmd.Parameters.AddWithValue(“@windows_event_table”,$table)
- $parameter.SqlDbType=[System.Data.SqlDbType]‘Structured’
- # Finalmente solo queda efectuar la llamada
- #
- $cmd.executenonquery()
- # Y cerrar la conexión. Los datos se han procesado correctamente
- #
- $SqlConnection.Close()
- “Se han procesado correctamente los datos”
- }
- catch
- {
- throw
- }
Conclusión
La ventaja de esta aproximación radica por un lado en la sencillez del código, que produce tanto un código cliente sencillo con una única instrucción, como un T-SQL que aglutina todas las acciones mediante la sentencia MERGE y la escalabilidad que produce eliminar de raíz el potencialmente elévalo número de peticiones al servidor, que independientemente deben resolverse.
Siguiendo esta metodología, podemos conseguir que nuestras operaciones de actualización sean más fáciles de tratar por SQL Server, dado que le proporcionamos la información en el formato para el que más está optimizado, los conjuntos.
2 comments
Excelente Publicación, mis respetos por la redacción y sencillez.
Saludos y muchas gracias por la info.
Muchas gracias Moisés