Exportar (0) Imprimir
Expandir todo

Técnicas de procesamiento por lotes para aplicaciones de Base de datos SQL en Azure

Actualizado: junio de 2014

Autores: Jason Roth, Silvano Coriani, Trent Swanson (Full Scale 180 Inc)

Revisores: Conor Cunningham, Michael Thomassy

Las operaciones de procesamiento por lotes en Base de datos SQL mejoran considerablemente el rendimiento y la escalabilidad de los servicios en la nube en . En este documento, primero se muestra la eficacia del procesamiento por lotes. A continuación se explican las técnicas, escenarios y consideraciones que le ayudarán a utilizar el procesamiento por lotes correctamente en los servicios en la nube.

  1. ¿Por qué es importante el procesamiento por lotes para Base de datos SQL?

  2. Estrategias de procesamiento por lotes

    1. Transacciones

    2. Parámetros con valores de tabla

    3. Copia masiva de SQL

    4. Instrucciones INSERT con parámetros de varias filas

    5. DataAdapter

    6. Entity Framework

    7. XML

  3. Consideraciones sobre el procesamiento por lotes

    1. Contrapartidas

    2. Tamaño del lote

    3. Procesamiento paralelo

    4. Factores de rendimiento relacionados

  4. Escenarios de procesamiento por lotes

    1. Almacenamiento en búfer

    2. Maestro y detalles

    3. UPSERT

  5. Recomendaciones

El procesamiento por lotes de llamadas a un servicio remoto es una estrategia conocida para aumentar el rendimiento y la escalabilidad. Toda interacción con un servicio remoto, como serialización, transferencia de red y deserialización, tiene asociados unos costos fijos de procesamiento. El empaquetado de muchas transacciones diferentes en un único lote minimiza estos costos.

En este documento, deseamos examinar diversas estrategias y escenarios de procesamiento por lotes de Base de datos SQL. Aunque estas estrategias también son importantes para las aplicaciones locales que utilizan SQL Server, hay dos motivos principales para resaltar el uso del procesamiento por lotes para Base de datos SQL:

  • Puede haber más latencia de red en el acceso a Base de datos SQL, especialmente si tiene acceso a Base de datos SQL desde fuera del mismo centro de datos de .

  • Las características multiempresa de Base de datos SQL significan que la eficacia de la capa de acceso a datos está relacionada con la escalabilidad global de la base de datos. Base de datos SQL debe impedir que cualquier inquilino o usuario individual monopolice los recursos de base de datos en detrimento de otros inquilinos. Como respuesta a un uso superior de las cuotas predefinidas, Base de datos SQL puede reducir el rendimiento o responder con excepciones de limitación. Las eficiencias, como el procesamiento por lotes, le permiten hacer más trabajo en Base de datos SQL antes de alcanzar estos límites. El procesamiento por lotes también es efectivo para aquellas arquitecturas que utilizan varias bases de datos o federaciones. La eficacia de la interacción con cada unidad de base de datos sigue siendo un factor clave de la escalabilidad global.

Una de las ventajas de utilizar Base de datos SQL es que no es necesario administrar los servidores que hospedan la base de datos. Sin embargo, esta infraestructura administrada significa también que hay que considerar de manera diferente las optimizaciones de la base de datos. Ya no puede intentar mejorar el hardware o la infraestructura de red de la base de datos. controla estos entornos. El área principal que puede controlar es cómo interactúa la aplicación con Base de datos SQL. El procesamiento por lotes es una de estas optimizaciones.

En la primera parte del documento se examinan diversas técnicas de procesamiento por lotes para las aplicaciones .NET que utilizan Base de datos SQL. En las dos últimas secciones se explican instrucciones y escenarios de procesamiento por lotes.

Parece extraño comenzar una explicación del procesamiento por lotes hablando de transacciones. Pero el uso de transacciones del lado cliente tiene un efecto sutil por lotes del lado servidor que mejora el rendimiento. Y las transacciones se pueden agregar con solo unas líneas de código, lo que proporciona una forma rápida de mejorar el rendimiento de las operaciones secuenciales.

Considere la siguiente secuencia de operaciones de inserción y actualización en una tabla sencilla.

List<string> dbOperations = new List<string>();
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 1");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 2");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 3");
dbOperations.Add("insert MyTable values ('new value',1)");
dbOperations.Add("insert MyTable values ('new value',2)");
dbOperations.Add("insert MyTable values ('new value',3)");

El siguiente código ADO.NET realiza secuencialmente estas operaciones.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    conn.Open();

    foreach(string commandString in dbOperations)
    {
        SqlCommand cmd = new SqlCommand(commandString, conn);
        cmd.ExecuteNonQuery();                   
    }
}

La mejor forma de optimizar este código es implementar alguna forma de procesamiento por lotes en el lado cliente de estas llamadas. Pero hay una forma sencilla de mejorar el rendimiento de este código ajustando simplemente la secuencia de llamadas en una transacción. Este es el mismo código que utiliza una transacción.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    conn.Open();
    SqlTransaction transaction = conn.BeginTransaction();

    foreach (string commandString in dbOperations)
    {
        SqlCommand cmd = new SqlCommand(commandString, conn, transaction);
        cmd.ExecuteNonQuery();
    }

    transaction.Commit();
}

En ambos ejemplos se utilizan transacciones realmente. En el primer ejemplo, cada llamada individual es una transacción implícita. En el segundo ejemplo, una transacción explícita ajusta todas las llamadas. Según la documentación del registro de transacciones de escritura previa, las entradas del registro se vacían en el disco cuando se confirma la transacción. Por tanto, al incluir más llamadas en una transacción, la escritura en el registro de transacciones se puede retrasar hasta que se confirma la transacción. A todos los efectos, está habilitando el procesamiento por lotes para las escrituras en el registro de transacciones del servidor.

En la tabla siguiente se muestran algunos resultados de pruebas ad hoc. Las pruebas realizaban las mismas inserciones secuenciales con y sin transacciones. Para tener más perspectiva, el primer conjunto de pruebas se ejecutaba de forma remota desde un equipo portátil en la base de datos en . El segundo conjunto de pruebas se ejecutaba desde un servicio en la nube y una base de datos que residían en el mismo centro de datos de (West US). En la tabla siguiente se muestra la duración en milisegundos de las inserciones secuenciales con y sin transacciones.

 

Operaciones Local en (mismo centro de datos)

Ninguna transacción (ms)

Transacción (ms)

Ninguna transacción (ms)

Transacción (ms)

1

130

402

21

26

10

1208

1226

220

56

100

12662

10395

2145

341

1000

128852

102917

21479

2756

noteNota
Los resultados no son pruebas comparativas, sino que pretenden mostrar el rendimiento relativo. Los controles de tiempo se basan en un promedio de al menos 10 series de pruebas. Las operaciones son inserciones en una tabla vacía.

Al ajustar una única operación en una transacción se reduce realmente el rendimiento. Pero a medida que aumenta el número de operaciones dentro de una única transacción, la mejora del rendimiento se vuelve más marcada. La diferencia de rendimiento también es más destacable cuando todas las operaciones se realizan dentro del centro de datos de . La mayor latencia que supone utilizar Base de datos SQL desde fuera del centro de datos de eclipsa la mejora de rendimiento que aporta el uso de transacciones.

Aunque el uso de transacciones puede aumentar el rendimiento, siga observando las prácticas recomendadas para las transacciones y conexiones. La transacción debe ser lo más corta posible y se debe cerrar la conexión a la base de datos cuando se completa el trabajo. La instrucción using del ejemplo anterior se asegura de que se cierra la conexión cuando el bloque de código siguiente se completa.

El ejemplo anterior muestra que puede agregar una transacción local a cualquier código ADO.NET con dos líneas. Las transacciones ofrecen una forma rápida de mejorar el rendimiento del código que realiza operaciones secuenciales de inserción, actualización y eliminación. Sin embargo, para lograr el máximo rendimiento, considere la posibilidad de cambiar aún más el código para aprovechar el procesamiento por lotes del lado cliente, por ejemplo para usar parámetros con valores de tabla.

Para obtener más información acerca de las transacciones en ADO.NET, vea Transacciones locales (ADO.NET).

Los parámetros con valores de tabla admiten tipos de tabla definidos por el usuario como parámetros en instrucciones, procedimientos almacenados y funciones Transact-SQL. Esta técnica de procesamiento por lotes del lado cliente permite enviar varias filas de datos dentro del parámetro con valores de tabla. Para utilizar parámetros con valores de tabla, debe definir primero un tipo de tabla. La instrucción Transact-SQL siguiente crea un tipo de tabla con el nombre MyTableType.

CREATE TYPE MyTableType AS TABLE 
( mytext TEXT,
  num INT );

En el código, cree DataTable con los mismos nombres y tipos exactos del tipo de tabla. Pase este DataTable en un parámetro en una consulta de texto o una llamada a procedimiento almacenado. En el ejemplo siguiente se muestra esta técnica:

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    DataTable table = new DataTable();
    // Add columns and rows. The following is a simple example.
    table.Columns.Add("mytext", typeof(string));
    table.Columns.Add("num", typeof(int));    
    for (var i = 0; i < 10; i++)
    {
        table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);
    }

    SqlCommand cmd = new SqlCommand(
        "INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",
        connection);
                
    cmd.Parameters.Add(
        new SqlParameter()
        {
            ParameterName = "@TestTvp",
            SqlDbType = SqlDbType.Structured,
            TypeName = "MyTableType",
            Value = table,
        });

    cmd.ExecuteNonQuery();
}

En el ejemplo anterior, el objeto SqlCommand inserta filas desde un parámetro con valores de tabla, @TestTvp. El objeto DataTable creado previamente se asigna a este parámetro con el método SqlCommand.Parameters.Add. El procesamiento por lotes de las inserciones en una llamada aumenta considerablemente el rendimiento con respecto a las inserciones secuenciales.

Para mejorar el ejemplo anterior aún más, utilice un procedimiento almacenado en lugar de un comando basado en texto. El comando Transact-SQL siguiente crea un procedimiento almacenado que toma el parámetro con valores de tabla SimpleTestTableType.

CREATE PROCEDURE [dbo].[sp_InsertRows] 
@TestTvp as MyTableType READONLY
AS
BEGIN
INSERT INTO MyTable(mytext, num) 
SELECT mytext, num FROM @TestTvp
END
GO

Cambie la declaración del objeto SqlCommand en el ejemplo de código anterior a lo siguiente.

SqlCommand cmd = new SqlCommand("sp_InsertRows", connection);
cmd.CommandType = CommandType.StoredProcedure;

En la mayoría de los casos, los parámetros con valores de tabla tienen un rendimiento equivalente o mejor que otras técnicas de procesamiento por lotes. A menudo son preferibles los parámetros con valores de tabla, ya que son más flexibles que otras opciones. Por ejemplo, otras técnicas como la copia masiva de SQL solo permiten la inserción de filas nuevas. Pero con los parámetros con valores de tabla, puede usar lógica en el procedimiento almacenado para determinar qué filas son actualizaciones y qué filas son inserciones. También se puede modificar el tipo de tabla para que contenga una columna “Operación” que indica si la fila especificada se debe insertar, actualizar o eliminar.

En la tabla siguiente se muestran resultados de pruebas ad hoc del uso de parámetros con valores de tabla en milisegundos.

 

Operaciones Local en (ms) , mismo centro de datos (ms)

1

124

32

10

131

25

100

338

51

1000

2615

382

10000

23830

3586

noteNota
Los resultados no son pruebas comparativas, sino que pretenden mostrar el rendimiento relativo. Estas pruebas utilizan un procedimiento almacenado similar al ejemplo de esta sección. Los controles de tiempo se basan en un promedio de al menos 10 series de pruebas. Las operaciones son inserciones en una tabla vacía.

La mejora del rendimiento que se consigue con el procesamiento por lotes se pone de manifiesto inmediatamente. En la prueba secuencial anterior, 1000 operaciones tardaban 129 segundos fuera del centro de datos y 21 segundos dentro del centro de datos. Pero cuando se usan parámetros con valores de tabla, 1000 operaciones solo tardan 2,6 segundos fuera del centro de datos y 0,4 segundos dentro del centro de datos.

Para obtener más información sobre los parámetros con valores de tabla, vea Parámetros con valores de tabla (motor de base de datos).

La copia masiva de SQL es otra manera de insertar grandes cantidades de datos en una base de datos de destino. Las aplicaciones .NET pueden utilizar la clase SqlBulkCopy para realizar operaciones de inserción masiva. SqlBulkCopy tiene una función similar a la herramienta de línea de comandos, Bcp.exe, o a la instrucción BULK INSERT de Transact-SQL. En el ejemplo de código siguiente se muestra cómo realizar la copia masiva de las filas del DataTable de origen, table, a la tabla de destino de SQL Server, MyTable.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "MyTable";
        bulkCopy.ColumnMappings.Add("mytext", "mytext");
        bulkCopy.ColumnMappings.Add("num", "num");
        bulkCopy.WriteToServer(table);
    }
}

Hay algunos casos en los que es preferible usar copia masiva en lugar de parámetros con valores de tabla. Vea la tabla comparativa de los parámetros con valores de tabla y las operaciones BULK INSERT en el tema Parámetros con valores de tabla (motor de base de datos).

Los resultados de pruebas ad hoc siguientes muestran el rendimiento del procesamiento por lotes con SqlBulkCopy en milisegundos.

 

Operaciones Local en (ms) , mismo centro de datos (ms)

1

433

57

10

441

32

100

636

53

1000

2535

341

10000

21605

2737

noteNota
Los resultados no son pruebas comparativas, sino que pretenden mostrar el rendimiento relativo. Los controles de tiempo se basan en un promedio de al menos 10 series de pruebas. Las operaciones son inserciones en una tabla vacía.

En tamaños de lote menores, el uso de parámetros con valores de tabla logró un rendimiento superaron al de la clase SqlBulkCopy. Sin embargo, SqlBulkCopy obtuvo un rendimiento del 12-31 % más rápido que los parámetros con valores de tabla para las pruebas de 1.000 y 10.000 filas. Como los parámetros con valores de tabla, SqlBulkCopy es una buena opción para las inserciones por lotes, especialmente cuando se compara con el rendimiento de las operaciones que no se realizan por lotes.

Para obtener más información acerca de la copia masiva en ADO.NET, vea Operaciones de copia masiva en SQL Server.

Una alternativa para los lotes pequeños consiste en construir una instrucción INSERT grande con parámetros que inserte varias filas. En el ejemplo de código siguiente se muestra esta técnica.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    string insertCommand = "INSERT INTO [MyTable] ( mytext, num ) " +
        "VALUES (@p1, @p2), (@p3, @p4), (@p5, @p6), (@p7, @p8), (@p9, @p10)";

    SqlCommand cmd = new SqlCommand(insertCommand, connection);

    for (int i = 1; i <= 10; i += 2)
    {
        cmd.Parameters.Add(new SqlParameter("@p" + i.ToString(), "test"));
        cmd.Parameters.Add(new SqlParameter("@p" + (i+1).ToString(), i));
    }

    cmd.ExecuteNonQuery();
}

Este ejemplo está pensado para mostrar el concepto básico. Un escenario más realista recorrería las entidades necesarias para generar la cadena de consulta y los parámetros del comando simultáneamente. Está limitado a un total de 2100 parámetros de consulta, lo que limita el número total de filas que se pueden procesar de esta manera.

Los resultados de pruebas ad hoc siguientes muestran el rendimiento de este tipo de instrucción de inserción en milisegundos.

 

Operaciones Parámetros con valores de tabla (ms) Instrucción INSERT única (ms)

1

32

20

10

30

25

100

33

51

noteNota
Los resultados no son pruebas comparativas. Los controles de tiempo se basan en un promedio de al menos 30 series de pruebas. Las operaciones son inserciones en una tabla vacía.

Este enfoque puede ser ligeramente más rápido para lotes de menos de 100 filas. Aunque la mejora es pequeña, esta técnica es otra opción que puede ser válida en el escenario específico de su aplicación.

La clase DataAdapter permite modificar un objeto DataSet y enviar después los cambios como operaciones INSERT, UPDATE y DELETE. Si utiliza DataAdapter de esta manera, es importante tener en cuenta que se realizan llamadas independientes para cada operación. Para mejorar el rendimiento, utilice la propiedad UpdateBatchSize en el número de operaciones que se deben procesar por lotes de una vez. Para obtener más información, vea Realizar operaciones por lotes mediante DataAdapters.

Entity Framework no admite actualmente el procesamiento por lotes. Varios desarrolladores de la comunidad han intentado mostrar soluciones alternativas, como invalidar el método SaveChanges. Pero las soluciones suelen ser complejas y personalizadas para la aplicación y el modelo de datos. El proyecto de Entity Framework de codeplex tiene actualmente una página de conversación sobre esta solicitud de característica. Para ver esta conversación, vea Notas de la reunión de diseño – 2 de agosto de 2012.

Para terminar, creemos que es importante hablar de XML como una estrategia de procesamiento por lotes. No obstante, el uso de XML no presenta ninguna ventaja con respecto a otros métodos y tiene varias desventajas. El enfoque es similar al de los parámetros con valores de tabla, pero se pasa un archivo XML o una cadena a un procedimiento almacenado en lugar de una tabla definida por el usuario. El procedimiento almacenado analiza los comandos del procedimiento almacenado.

Este enfoque tiene varias desventajas:

  1. El trabajo con XML puede ser complejo y propenso a errores.

  2. El análisis del código XML en la base de datos puede usar mucha CPU.

  3. En la mayoría de los casos, este método es más lento que los parámetros con valores de tabla.

Por estas razones, no se recomienda el uso de XML en consultas por lotes.

En las próximas secciones se proporcionan más instrucciones para el uso del procesamiento por lotes en aplicaciones de Base de datos SQL.

En función de la arquitectura, el procesamiento por lotes puede implicar un equilibrio entre rendimiento y resistencia. Por ejemplo, considere el escenario en el que su rol deja de funcionar inesperadamente. Si pierde una fila de datos, el efecto es menor que si pierde un lote grande de filas sin enviar. El riesgo es mayor cuando almacena filas en búfer antes de enviarlas a la base de datos en un período de tiempo especificado.

Debido a esta contrapartida, debe evaluar el tipo de operaciones que procesa por lotes. Procese por lotes más agresivamente (lotes mayores y períodos de tiempo más prolongados) los datos que sean menos críticos.

En nuestras pruebas, normalmente no había ninguna ventaja en dividir los lotes grandes en fragmentos menores. De hecho, esta subdivisión produjo a menudo un rendimiento menor que cuando se enviaba un único lote grande. Por ejemplo, imagine un escenario en el que desea insertar 1000 filas. En la tabla siguiente se muestra el tiempo necesario para utilizar parámetros con valores de tabla con el fin de insertar 1000 filas cuando se dividen en lotes menores.

 

Tamaño del lote [iteraciones] Parámetros con valores de tabla (ms)

1000 [1]

347

500 [2]

355

100 [10]

465

50 [20]

630

noteNota
Los resultados no son pruebas comparativas. Las pruebas local se realizaron en un equipo portátil en una red inalámbrica. Los controles de tiempo se basan en un promedio de al menos 10 series de pruebas. Las operaciones son inserciones en una tabla vacía.

Puede ver que el máximo rendimiento para 1000 filas se consigue cuando se envían todas a la vez. En otras pruebas (no mostradas aquí) hubo una pequeña mejora de rendimiento al dividir un lote de 10.000 filas en dos lotes de 5.000. Pero el esquema de tabla de estas pruebas es relativamente simple, por lo que debe hacer pruebas con sus datos y tamaños de lote específicos para comprobar estos hallazgos.

Otro factor que hay que tener en cuenta es que si el lote total es demasiado grande, Base de datos SQL puede limitar y rechazar la confirmación del lote. Para obtener los mejores resultados, pruebe su escenario concreto para determinar si existe un tamaño de lote ideal. Haga que el tamaño de lote se pueda configurar en tiempo de ejecución para permitir ajustes rápidos en función del rendimiento o de si hay errores.

Por último, sopese el tamaño del lote frente a los riesgos asociados al procesamiento por lotes. Si hay errores transitorios o se produce un error en el rol, tenga en cuenta las consecuencias de reintentar la operación o de perder los datos del lote.

¿Qué pasa si toma el enfoque de reducir el tamaño del lote pero usa varios subprocesos para ejecutar el trabajo? Una vez más, nuestras pruebas mostraron que varios lotes multiproceso menores tenían normalmente un rendimiento peor que un solo lote mayor. La prueba siguiente intenta insertar 1000 filas en uno o varios lotes paralelos. Esta prueba muestra cómo más lotes simultáneos reducen realmente el rendimiento.

 

Tamaño del lote [iteraciones] Dos subprocesos (ms) Cuatro subprocesos (ms) Seis subprocesos (ms)

1000 [1]

277

315

266

500 [2]

548

278

256

250 [4]

405

329

265

100 [10]

488

439

391

noteNota
Los resultados no son pruebas comparativas. Los controles de tiempo se basan en un promedio de al menos 10 series de pruebas. Las operaciones son inserciones en una tabla vacía. Las pruebas de procesamiento en paralelo utilizaban la biblioteca TPL (biblioteca de procesamiento paralelo basado en tareas) de .NET.

Existen varias razones posibles de la degradación del rendimiento debido al paralelismo:

  1. Hay varias llamadas de red simultáneas en lugar de una.

  2. Varias operaciones en una única tabla pueden provocar contención y bloqueo.

  3. Hay sobrecargas asociadas a multithreading.

  4. El gasto que supone abrir varias conexiones sobrepasa el beneficio del procesamiento en paralelo.

Si tiene como destino varias tablas o bases de datos, es posible ver algún aumento del rendimiento con esta estrategia. El particionamiento o sharding o las federaciones de base de datos sería un escenario de este enfoque. El particionamiento utiliza varias bases de datos y enruta distintos datos a cada base de datos. Si cada lote pequeño va a ir a una base de datos diferente, puede ser más eficaz realizar las operaciones en paralelo. Sin embargo, la mejora de rendimiento no es suficientemente importante como para utilizarla como base para tomar una decisión de utilizar el particionamiento de la base de datos en su solución.

En algunos diseños, la ejecución en paralelo de lotes menores puede mejorar el rendimiento de las solicitudes en un sistema con mucha carga. En este caso, aunque es más rápido procesar un único lote mayor, el procesamiento de varios lotes en paralelo puede ser más eficaz.

Si utiliza la ejecución en paralelo, considere la posibilidad de controlar el número máximo de subprocesos de trabajo. Un número menor podría provocar menos contención y un tiempo de ejecución más rápido. Tenga en cuenta también la carga adicional que esto impone sobre la base de datos de destino, tanto en cuanto a conexiones como a transacciones.

Las instrucciones típicas sobre el rendimiento de la base de datos también afectan al procesamiento por lotes. Por ejemplo, el rendimiento de inserción se reduce en el caso de tablas que tienen una clave principal grande o muchos índices no clúster.

Si los parámetros con valores de tabla utilizan un procedimiento almacenado, puede emplear el comando SET NOCOUNT ON al principio del procedimiento. Esta instrucción suprime la devolución del recuento de las filas afectadas en el procedimiento. Sin embargo, en nuestras prueba, el uso de SET NOCOUNT ON no tuvo ningún efecto ni redujo el rendimiento. El procedimiento almacenado de prueba era simple y tenía un único comando INSERT del parámetro con valores de tabla. Es posible que otros procedimientos almacenados más complejos se beneficien de esta instrucción. Pero no suponga que agregar SET NOCOUNT ON al procedimiento almacenado mejora automáticamente el rendimiento. Para entender el efecto, pruebe el procedimiento almacenado con y sin la instrucción SET NOCOUNT ON.

En las próximas secciones se describe cómo utilizar parámetros con valores de tabla en tres escenarios de aplicación. El primer escenario muestra cómo pueden funcionar conjuntamente el almacenamiento en búfer y el procesamiento por lotes. El segundo escenario mejora el rendimiento al realizar las operaciones de maestro y detalles en una única llamada a procedimiento almacenado. El último escenario muestra cómo utilizar parámetros con valores de tabla en una operación “UPSERT”.

Aunque hay algunos escenarios que son candidatos obvio al procesamiento por lotes, hay muchos escenarios que pueden aprovechar el procesamiento por lotes para diferir el procesamiento. Sin embargo, el procesamiento diferido también plantea un riesgo mayor de que se pierdan datos en caso de que se produzca error inesperado. Es importante entender este riesgo y tener en cuenta sus consecuencias.

Por ejemplo, imagine una aplicación web que hace un seguimiento del historial de navegación de cada usuario. En cada solicitud de página, la aplicación podría realizar una llamada a la base de datos para registrar la página que ha visto el usuario. Pero se puede lograr un rendimiento y una escalabilidad mayores si se almacenan en búfer las actividades de navegación de los usuarios y después se envían estos datos a la base de datos en lotes. Puede desencadenar la actualización de la base de datos por tiempo transcurrido y/o por tamaño de búfer. Por ejemplo, una regla podría especificar que el lote se debe procesar después de 20 segundos o cuando el búfer llegue a tener 1000 elementos.

En el ejemplo de código siguiente se usa Extensiones reactivas (Rx) para procesar los eventos almacenado en búfer generados por una clase de supervisión. Cuando se llena de búfer o se alcanza un tiempo de espera, el lote de datos de usuario se envía a la base de datos con un parámetro con valores de tabla.

La siguiente clase NavHistoryData modela los detalles de navegación del usuario. Contiene información básica como el identificador de usuario, la dirección URL a la que se ha tenido acceso y el tiempo de acceso.

public class NavHistoryData
{
    public NavHistoryData(int userId, string url, DateTime accessTime)
    { UserId = userId; URL = url; AccessTime = accessTime; }
    public int UserId { get; set; }
    public string URL { get; set; }
    public DateTime AccessTime { get; set; }
}

La clase NavHistoryDataMonitor es responsable de almacenar en búfer datos de navegación del usuario en la base de datos. Contiene un método, RecordUserNavigationEntry, que responde generando un evento OnAdded. En el código siguiente se muestra la lógica del constructor que utiliza Rx para crear una colección observable basada en el evento. Después se suscribe a esta colección observable con el método Buffer. La sobrecarga especifica que el búfer se debe enviar cada 20 segundos o 1000 entradas.

public NavHistoryDataMonitor()
{
    var observableData =
        Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");

    observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);           
}

El controlador convierte todos los elementos almacenados en búfer en un tipo con valor de tabla y después pasa este tipo a un procedimiento almacenado que procesa el lote. En el código siguiente se muestra la definición completa de las clases NavHistoryDataEventArgs y NavHistoryDataMonitor.

public class NavHistoryDataEventArgs : System.EventArgs
{
    public NavHistoryDataEventArgs(NavHistoryData data) { Data = data; }
    public NavHistoryData Data { get; set; }
}

public class NavHistoryDataMonitor
{
    public event EventHandler<NavHistoryDataEventArgs> OnAdded;

    public NavHistoryDataMonitor()
    {
        var observableData =
            Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");

        observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);           
    }

    public void RecordUserNavigationEntry(NavHistoryData data)
    {    
        if (OnAdded != null)
            OnAdded(this, new NavHistoryDataEventArgs(data));
    }

    protected void Handler(IList<EventPattern<NavHistoryDataEventArgs>> items)
    {
        DataTable navHistoryBatch = new DataTable("NavigationHistoryBatch");
        navHistoryBatch.Columns.Add("UserId", typeof(int));
        navHistoryBatch.Columns.Add("URL", typeof(string));
        navHistoryBatch.Columns.Add("AccessTime", typeof(DateTime));
        foreach (EventPattern<NavHistoryDataEventArgs> item in items)
        {
            NavHistoryData data = item.EventArgs.Data;
            navHistoryBatch.Rows.Add(data.UserId, data.URL, data.AccessTime);
        }

        using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
        {
            connection.Open();

            SqlCommand cmd = new SqlCommand("sp_RecordUserNavigation", connection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(
                new SqlParameter()
                {
                    ParameterName = "@NavHistoryBatch",
                    SqlDbType = SqlDbType.Structured,
                    TypeName = "NavigationHistoryTableType",
                    Value = navHistoryBatch,
                });

            cmd.ExecuteNonQuery();
        }
    }
}

Para utilizar este tipo de almacenamiento en búfer, la aplicación crea un objeto NavHistoryDataMonitor estático. Cada vez que un usuario tiene acceso a una página, la aplicación llama al método NavHistoryDataMonitor.RecordUserNavigationEntry. La lógica de almacenamiento en búfer se encarga de enviar estas entradas a la base de datos en lotes.

Los parámetros con valores de tabla son útiles en escenarios INSERT simples. Sin embargo, puede ser más difícil procesar por lotes inserciones que implican más de una tabla. El escenario de “maestro y detalles” es un buen ejemplo. La tabla maestra identifica la entidad principal. Una o varias tablas de detalle almacenen más datos sobre la entidad. En este escenario, las relaciones de clave externa aplican de la relación de detalles a una entidad principal única. Considere una versión simplificada de una tabla PurchaseOrder y su tabla OrderDetail asociada. El código Transact-SQL siguiente crea la tabla PurchaseOrder con cuatro columnas: OrderID, OrderDate, CustomerID y Status.

CREATE TABLE [dbo].[PurchaseOrder](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[Status] [nvarchar](50) NOT NULL,
 CONSTRAINT [PrimaryKey_PurchaseOrder] 
PRIMARY KEY CLUSTERED ( [OrderID] ASC ))

Cada pedido contiene una o varias compras de productos. Esta información se captura en la tabla PurchaseOrderDetail. El código Transact-SQL siguiente crea la tabla PurchaseOrderDetail con cinco columnas: OrderID, OrderDetailID, ProductID, UnitPrice y OrderQty.

CREATE TABLE [dbo].[PurchaseOrderDetail](
[OrderID] [int] NOT NULL,
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
 CONSTRAINT [PrimaryKey_PurchaseOrderDetail] PRIMARY KEY CLUSTERED 
( [OrderID] ASC, [OrderDetailID] ASC ))

La columna OrderID de la tabla PurchaseOrderDetail debe hacer referencia a un pedido de la tabla PurchaseOrder. La siguiente definición de una clave externa aplica esta restricción.

ALTER TABLE [dbo].[PurchaseOrderDetail2]  WITH CHECK ADD 
CONSTRAINT [FK_OrderID_PurchaseOrder] FOREIGN KEY([OrderID])
REFERENCES [dbo].[PurchaseOrder] ([OrderID])

Para poder utilizar parámetros con valores de tabla, debe tener un tipo de tabla definido por el usuario para cada tabla de destino.

CREATE TYPE PurchaseOrderTableType AS TABLE 
( OrderID INT,
  OrderDate DATETIME,
  CustomerID INT,
  Status NVARCHAR(50) );
GO

CREATE TYPE PurchaseOrderDetailTableType AS TABLE 
( OrderID INT,
  ProductID INT,
  UnitPrice MONEY,
  OrderQty SMALLINT );
GO

Defina después un procedimiento almacenado que acepte tablas de estos tipos. Este procedimiento permite que una aplicación procese por lotes localmente un conjunto de pedidos y detalles de pedido en una única llamada. El código Transact-SQL siguiente proporciona la declaración completa del procedimiento almacenado para este ejemplo de pedido de compra.

CREATE PROCEDURE sp_InsertOrdersBatch (
@orders as PurchaseOrderTableType READONLY,
@details as PurchaseOrderDetailTableType READONLY )
AS
SET NOCOUNT ON;

-- Table that connects the order identifiers in the @orders
-- table with the actual order identifiers in the PurchaseOrder table
DECLARE @IdentityLink AS TABLE ( 
SubmittedKey int, 
ActualKey int, 
RowNumber int identity(1,1)
);
 
      -- Add new orders to the PurchaseOrder table, storing the actual
-- order identifiers in the @IdentityLink table   
INSERT INTO PurchaseOrder ([OrderDate], [CustomerID], [Status])
OUTPUT inserted.OrderID INTO @IdentityLink (ActualKey)
SELECT [OrderDate], [CustomerID], [Status] FROM @orders ORDER BY OrderID;

-- Match the passed-in order identifiers with the actual identifiers
-- and complete the @IdentityLink table for use with inserting the details
WITH OrderedRows As (
SELECT OrderID, ROW_NUMBER () OVER (ORDER BY OrderID) As RowNumber 
FROM @orders
)
UPDATE @IdentityLink SET SubmittedKey = M.OrderID
FROM @IdentityLink L JOIN OrderedRows M ON L.RowNumber = M.RowNumber;

-- Insert the order details into the PurchaseOrderDetail table, 
      -- using the actual order identifiers of the master table, PurchaseOrder
INSERT INTO PurchaseOrderDetail (
[OrderID],
[ProductID],
[UnitPrice],
[OrderQty] )
SELECT L.ActualKey, D.ProductID, D.UnitPrice, D.OrderQty
FROM @details D
JOIN @IdentityLink L ON L.SubmittedKey = D.OrderID;
GO

En este ejemplo, la tabla @IdentityLink definida localmente almacena los valores reales de OrderID de las filas recién insertadas. Estos identificadores de pedidos son diferentes de los valores temporales de OrderID en los parámetros con valores de tabla @orders y @details. Por esta razón, la tabla @IdentityLink conecta los valores de OrderID del parámetro @orders con los valores reales de OrderID para las nuevas filas de la tabla PurchaseOrder. Después de este paso, la tabla @IdentityLink puede facilitar la inserción de detalles de pedido con el OrderID real que cumple la restricción de clave externa.

Este procedimiento almacenado se puede utilizar desde código o desde otras llamadas de Transact-SQL. Vea la sección sobre parámetros con valores de tabla de este documento para obtener un ejemplo de código. El código Transact-SQL siguiente muestra cómo llamar a sp_InsertOrdersBatch.

declare @orders as PurchaseOrderTableType
declare @details as PurchaseOrderDetailTableType

INSERT @orders 
([OrderID], [OrderDate], [CustomerID], [Status])
VALUES(1, '1/1/2013', 1125, 'Complete'),
(2, '1/13/2013', 348, 'Processing'),
(3, '1/12/2013', 2504, 'Shipped')

INSERT @details
([OrderID], [ProductID], [UnitPrice], [OrderQty])
VALUES(1, 10, $11.50, 1),
(1, 12, $1.58, 1),
(2, 23, $2.57, 2),
(3, 4, $10.00, 1)

exec sp_InsertOrdersBatch @orders, @details

Esta solución permite que cada lote utilice un conjunto de valores de OrderID que empiezan en 1. Estos valores temporales de OrderID describen las relaciones del lote, pero los valores reales de OrderID se determinan en el momento de la operación de inserción. Puede ejecutar las mismas instrucciones del ejemplo anterior repetidamente y generar pedidos únicos en la base de datos. Por este motivo, considere la posibilidad de agregar más lógica del código o de la base de datos que impida la duplicación de pedidos cuando se utilice esta técnica de procesamiento por lotes.

Este ejemplo muestra que las operaciones de base de datos más complejas, como las operaciones de maestro y detalles, se pueden procesar por lotes mediante parámetros con valores de tabla.

Otro escenario de procesamiento por lotes implica la actualización de filas existentes y la inserción de filas nuevas simultáneamente. Esta operación se denomina en ocasiones operación “UPSERT” (actualización + inserción). En lugar de realizar llamadas diferentes a INSERT y UPDATE, la instrucción MERGE es más adecuada para esta tarea. La instrucción MERGE puede realizar tanto operaciones de inserción como de actualización en una sola llamada.

Se pueden utilizar parámetros con valores de tabla con la instrucción MERGE para realizar actualizaciones e inserciones. Por ejemplo, imagine una tabla Employee simplificada que contiene las columnas siguientes: EmployeeID, FirstName, LastName, SocialSecurityNumber:

CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[SocialSecurityNumber] [nvarchar](50) NOT NULL,
 CONSTRAINT [PrimaryKey_Employee] PRIMARY KEY CLUSTERED 
([EmployeeID] ASC ))

En este ejemplo, puede utilizar el hecho de que el SocialSecurityNumber es único para realizar una operación MERGE de varios empleados. En primer lugar, cree un tipo de tabla definido por el usuario:

CREATE TYPE EmployeeTableType AS TABLE 
( Employee_ID INT,
  FirstName NVARCHAR(50),
  LastName NVARCHAR(50),
  SocialSecurityNumber NVARCHAR(50) );
GO

A continuación, cree un procedimiento almacenado o escriba código que utilice la instrucción MERGE para realizar la actualización e inserción. En el ejemplo siguiente se utiliza la instrucción MERGE en un parámetro con valores de tabla, @employees, de tipo EmployeeTableType. El contenido de la tabla @employees no se muestra aquí.

MERGE Employee AS target
USING (SELECT [FirstName], [LastName], [SocialSecurityNumber] FROM @employees) 
AS source ([FirstName], [LastName], [SocialSecurityNumber])
ON (target.[SocialSecurityNumber] = source.[SocialSecurityNumber])
WHEN MATCHED THEN 
UPDATE SET
target.FirstName = source.FirstName, 
target.LastName = source.LastName
WHEN NOT MATCHED THEN
   INSERT ([FirstName], [LastName], [SocialSecurityNumber])
   VALUES (source.[FirstName], source.[LastName], source.[SocialSecurityNumber]);

Para obtener más información, vea la documentación y los ejemplos de la instrucción MERGE (Transact-SQL). Aunque se puede realizar el mismo trabajo en una llamada a procedimiento almacenado de varios pasos con operaciones INSERT y UPDATE diferentes, la instrucción MERGE es más eficaz. El código de la base de datos también puede generar llamadas de Transact-SQL que utilizan la instrucción MERGE directamente sin que sean necesarias dos llamadas de base de datos para INSERT y UPDATE.

  • Use el almacenamiento en búfer y el procesamiento por lotes para aumentar el rendimiento y la escalabilidad de las aplicaciones de Base de datos SQL.

  • Entienda las contrapartidas entre el procesamiento por lotes y el almacenamiento en búfer, y la resistencia. En caso de que se produzca un error en un rol, el riesgo de perder un lote sin procesar de datos críticos puede ser mayor que la mejora de rendimiento del procesamiento por lotes.

  • Intente mantener todas las llamadas a la base de datos dentro de un mismo centro de datos para reducir la latencia.

  • Si elige una única técnica de procesamiento por lotes, los parámetros con valores de tabla ofrecen el máximo rendimiento y flexibilidad.

  • Para lograr el máximo rendimiento de inserción, siga estas directrices generales y pruebe su escenario:

  • En las operaciones de actualización y eliminación, use parámetros con valores de tabla con lógica de procedimiento almacenado que determine la operación correcta en cada fila del parámetro de tabla.

  • Instrucciones sobre el tamaño del lote:

    • El uso de tamaños de lote mayores puede tener sentido para sus requisitos de aplicación y empresariales.

    • Equilibre la mejora de rendimiento que suponen los lotes grandes con los riesgos de que se produzcan errores temporales o catastróficos. ¿Cuál es la consecuencia de los reintentos o las pérdidas de datos en el lote?

    • Pruebe el tamaño de lote máximo para comprobar que Base de datos SQL no lo rechaza.

    • Cree valores de configuración que controlen el procesamiento por lotes, como el tamaño del lote o el período de tiempo de almacenamiento en búfer. Estos valores proporcionan flexibilidad. Puede cambiar el comportamiento de procesamiento por lotes en producción sin volver a implementar el servicio en la nube.

  • Evite la ejecución en paralelo de lotes que operan sobre una sola tabla de una base de datos. Si decide dividir un único lote en varios subprocesos de trabajo, realice pruebas para determinar el número ideal de subprocesos. Después de traspasar un umbral no especificado, un número mayor de subprocesos reducirá el rendimiento en lugar de mejorarlo.

  • Considere la posibilidad de almacenar en búfer por tamaño y por hora como una forma de implementar el procesamiento por lotes para varios escenarios.

Vea también

Mostrar:
© 2014 Microsoft