Come usare l'invio in batch per migliorare le prestazioni delle applicazioni di database SQLHow to use batching to improve SQL Database application performance

Le operazioni di invio in batch al database SQL di Azure migliorano in modo significativo le prestazioni e la scalabilità delle applicazioni.Batching operations to Azure SQL Database significantly improves the performance and scalability of your applications. Per comprendere i vantaggi, la prima parte di questo articolo descrive alcuni risultati dei test di esempio che confrontano le richieste sequenziali e in batch inviate a un database SQL.In order to understand the benefits, the first part of this article covers some sample test results that compare sequential and batched requests to a SQL Database. Il resto dell'articolo illustra le tecniche, gli scenari e le considerazioni che facilitano l'uso corretto dell'invio in batch nelle applicazioni Azure.The remainder of the article shows the techniques, scenarios, and considerations to help you to use batching successfully in your Azure applications.

Perché l'invio in batch è importante per il database SQL?Why is batching important for SQL Database?

L'invio in batch di chiamate a un servizio remoto è una strategia nota per migliorare le prestazioni e la scalabilità.Batching calls to a remote service is a well-known strategy for increasing performance and scalability. Ogni interazione con un servizio remoto, ad esempio la serializzazione, il trasferimento in rete e la deserializzazione, comporta costi fissi di elaborazione.There are fixed processing costs to any interactions with a remote service, such as serialization, network transfer, and deserialization. Il raggruppamento di più transazioni distinte in un singolo batch consente di ridurre al minimo questi costi.Packaging many separate transactions into a single batch minimizes these costs.

In questo articolo si esamineranno diversi scenari e strategie di invio in batch al database SQL.In this paper, we want to examine various SQL Database batching strategies and scenarios. Queste strategie sono importanti anche per le applicazioni locali che usano SQL Server, tuttavia ci sono diversi motivi per evidenziare l'uso dell'invio in batch per il database SQL:Although these strategies are also important for on-premises applications that use SQL Server, there are several reasons for highlighting the use of batching for SQL Database:

  • La latenza di rete per l'accesso al database SQL è potenzialmente maggiore, in particolare se si accede dall'esterno dello stesso data center di Microsoft Azure.There is potentially greater network latency in accessing SQL Database, especially if you are accessing SQL Database from outside the same Microsoft Azure datacenter.
  • Le caratteristiche multi-tenant del database SQL indicano che l'efficienza del livello di accesso ai dati è correlata alla scalabilità generale del database.The multitenant characteristics of SQL Database means that the efficiency of the data access layer correlates to the overall scalability of the database. Il database SQL deve impedire che qualsiasi tenant/utente singolo monopolizzi le risorse del database a scapito di altri tenant.SQL Database must prevent any single tenant/user from monopolizing database resources to the detriment of other tenants. In risposta all'utilizzo eccessivo di quote predefinite, il database SQL può ridurre la velocità effettiva o rispondere con eccezioni di limitazione delle richieste.In response to usage in excess of predefined quotas, SQL Database can reduce throughput or respond with throttling exceptions. Strategie efficienti, come l'invio in batch, consentono di effettuare un maggior numero di operazioni sul database SQL prima di raggiungere questi limiti.Efficiencies, such as batching, enable you to do more work on SQL Database before reaching these limits.
  • L'invio in batch è efficace anche per le architetture che usano più database (partizionamento orizzontale).Batching is also effective for architectures that use multiple databases (sharding). L'efficienza dell'interazione con ogni unità database rimane comunque un fattore chiave ai fini della scalabilità generale.The efficiency of your interaction with each database unit is still a key factor in your overall scalability.

Uno dei vantaggi che derivano dall'uso del database SQL consiste nel non dover gestire i server che ospitano il database.One of the benefits of using SQL Database is that you don’t have to manage the servers that host the database. Tuttavia, questa infrastruttura gestita implica anche una diversa concezione delle ottimizzazioni del database.However, this managed infrastructure also means that you have to think differently about database optimizations. Non è più possibile migliorare l'hardware o l'infrastruttura di rete del database.You can no longer look to improve the database hardware or network infrastructure. Gli ambienti sono controllati da Microsoft Azure.Microsoft Azure controls those environments. L'area principale su cui è possibile esercitare un controllo è la modalità di interazione dell'applicazione con il database SQL.The main area that you can control is how your application interacts with SQL Database. L'invio in batch è una di queste ottimizzazioni.Batching is one of these optimizations.

La prima parte del documento esamina le diverse tecniche di invio in batch per le applicazioni .NET che usano il database SQL.The first part of the paper examines various batching techniques for .NET applications that use SQL Database. Le ultime due sezioni illustrano invece le linee guida e gli scenari di invio in batch.The last two sections cover batching guidelines and scenarios.

Strategie di invio in batchBatching strategies

Nota sui risultati della tempistica in questo argomentoNote about timing results in this topic

Nota

I risultati non sono benchmark ma servono per indicare le prestazioni relative.Results are not benchmarks but are meant to show relative performance. Le tempistiche si basano su una media di almeno 10 esecuzioni del test.Timings are based on an average of at least 10 test runs. Le operazioni sono inserimenti in una tabella vuota.Operations are inserts into an empty table. Questi test sono stati misurati con un database antecedente a V12 e non corrispondono necessariamente alla velocità effettiva che si potrebbe ottenere in un database V12 usando i nuovi livelli di servizio.These tests were measured pre-V12, and they do not necessarily correspond to throughput that you might experience in a V12 database using the new service tiers. Il vantaggio relativo della tecnica di invio in batch dovrebbe essere simile.The relative benefit of the batching technique should be similar.

TransazioniTransactions

Può apparire inconsueto che si inizi un'analisi dell'invio in batch parlando di transazioni.It seems strange to begin a review of batching by discussing transactions. Tuttavia, l'uso delle transazioni sul lato client ha un sottile effetto sull'invio in batch sul lato server che migliora le prestazioni.But the use of client-side transactions has a subtle server-side batching effect that improves performance. Inoltre, le transazioni possono essere aggiunte con poche righe di codice, quindi sono un modo rapido per migliorare le prestazioni delle operazioni sequenziali.And transactions can be added with only a few lines of code, so they provide a fast way to improve performance of sequential operations.

Si consideri il codice C# seguente che contiene una sequenza di operazioni di inserimento e aggiornamento in una semplice tabella.Consider the following C# code that contains a sequence of insert and update operations on a simple table.

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)");

Il codice ADO.NET seguente esegue queste operazioni in sequenza.The following ADO.NET code sequentially performs these operations.

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

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

Il modo migliore per ottimizzare il codice consiste nell'implementare una qualche forma di invio in batch di queste chiamate sul lato client.The best way to optimize this code is to implement some form of client-side batching of these calls. Esiste tuttavia un modo semplice per migliorare le prestazioni del codice, eseguendo semplicemente il wrapping della sequenza di chiamate in una transazione.But there is a simple way to increase the performance of this code by simply wrapping the sequence of calls in a transaction. Ecco lo stesso codice che usa una transazione.Here is the same code that uses a transaction.

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();
}

Le transazioni vengono in effetti usate in entrambi questi esempi.Transactions are actually being used in both of these examples. Nel primo ogni singola chiamata rappresenta una transazione implicita.In the first example, each individual call is an implicit transaction. Nel secondo esempio viene eseguito il wrapping di tutte le chiamate in una transazione esplicita.In the second example, an explicit transaction wraps all of the calls. Secondo la documentazione relativa al log delle transazioni write-ahead, i record del log vengono scaricati su disco al momento del commit della transazione.Per the documentation for the write-ahead transaction log, log records are flushed to the disk when the transaction commits. Si conseguenza, se si includono più chiamate in una transazione, la scrittura nel log delle transazioni può essere ritardata finché non viene eseguito il commit della transazione stessa.So by including more calls in a transaction, the write to the transaction log can delay until the transaction is committed. In effetti, si abilita l'invio in batch per le operazioni di scrittura nel log delle transazioni del server.In effect, you are enabling batching for the writes to the server’s transaction log.

La tabella seguente illustra alcuni risultati di test ad hoc.The following table shows some ad-hoc testing results. I test eseguono le medesime operazioni sequenziali di inserimento con e senza transazioni.The tests performed the same sequential inserts with and without transactions. Per maggiore chiarezza, il primo set di test è stato eseguito in remoto da un portatile al database in Microsoft Azure.For more perspective, the first set of tests ran remotely from a laptop to the database in Microsoft Azure. Il secondo set di test è stato eseguito da un servizio cloud e un database entrambi residenti nello stesso data center di Microsoft Azure (Stati Uniti occidentali).The second set of tests ran from a cloud service and database that both resided within the same Microsoft Azure datacenter (West US). La tabella seguente mostra la durata in millisecondi delle operazioni di inserimento sequenziali con e senza transazioni.The following table shows the duration in milliseconds of sequential inserts with and without transactions.

Da ambiente locale ad AzureOn-Premises to Azure:

OperazioniOperations Senza transazione (ms)No Transaction (ms) Con transazione (ms)Transaction (ms)
11 130130 402402
1010 12081208 12261226
100100 1266212662 1039510395
10001000 128852128852 102917102917

Da Azure ad Azure (stesso data center):Azure to Azure (same datacenter):

OperazioniOperations Senza transazione (ms)No Transaction (ms) Con transazione (ms)Transaction (ms)
11 2121 2626
1010 220220 5656
100100 21452145 341341
10001000 2147921479 27562756

Nota

I risultati non sono benchmark.Results are not benchmarks. Vedere la nota sui risultati della tempistica in questo argomento.See the note about timing results in this topic.

In base ai risultati di test precedenti, il wrapping di una singola operazione in una transazione riduce in effetti le prestazioni.Based on the previous test results, wrapping a single operation in a transaction actually decreases performance. Tuttavia, maggiore è il numero di operazioni in una singola transazione, più evidente risulta il miglioramento delle prestazioni.But as you increase the number of operations within a single transaction, the performance improvement becomes more marked. La differenza nelle prestazioni è anche più significativa se tutte le operazioni vengono eseguite nello stesso data center di Microsoft Azure.The performance difference is also more noticeable when all operations occur within the Microsoft Azure datacenter. La maggiore latenza dovuta all'uso del database SQL dall'esterno del data center di Microsoft Azure vanifica il vantaggio in termini di prestazioni derivante dall'uso delle transazioni.The increased latency of using SQL Database from outside the Microsoft Azure datacenter overshadows the performance gain of using transactions.

Anche se l'uso delle transazioni può migliorare le prestazioni, continuare a osservare le procedure consigliate per transazioni e connessioni.Although the use of transactions can increase performance, continue to observe best practices for transactions and connections. Usare transazioni più brevi possibile e chiudere la connessione al database al termine delle operazioni.Keep the transaction as short as possible, and close the database connection after the work completes. L'istruzione using nell'esempio precedente assicura la chiusura della connessione al termine del blocco di codice successivo.The using statement in the previous example assures that the connection is closed when the subsequent code block completes.

L'esempio precedente illustra che è possibile aggiungere una transazione locale al codice ADO.NET con due righe.The previous example demonstrates that you can add a local transaction to any ADO.NET code with two lines. Le transazioni rappresentano un modo rapido per migliorare le prestazioni del codice usato per le operazioni sequenziali di inserimento, aggiornamento ed eliminazione.Transactions offer a quick way to improve the performance of code that makes sequential insert, update, and delete operations. Per prestazioni ottimali, provare tuttavia a modificare ulteriormente il codice per sfruttare l'invio in batch sul lato client, ad esempio i parametri con valori di tabella.However, for the fastest performance, consider changing the code further to take advantage of client-side batching, such as table-valued parameters.

Per altre informazioni sulle transazioni in ADO.NET, vedere Transazioni locali in ADO.NET.For more information about transactions in ADO.NET, see Local Transactions in ADO.NET.

Parametri con valori di tabellaTable-valued parameters

I parametri con valori di tabella supportano tipi di tabella definiti dall'utente come parametri nelle funzioni, nelle stored procedure e nelle istruzioni Transact-SQL.Table-valued parameters support user-defined table types as parameters in Transact-SQL statements, stored procedures, and functions. Questa tecnica di invio in batch sul lato client consente di inviare più righe di dati nel parametro con valori di tabella.This client-side batching technique allows you to send multiple rows of data within the table-valued parameter. Per usare parametri con valori di tabella, definire prima di tutto un tipo di tabella.To use table-valued parameters, first define a table type. L'istruzione Transact-SQL seguente crea un tipo di tabella denominato MyTableType.The following Transact-SQL statement creates a table type named MyTableType.

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

Nel codice si crea un oggetto DataTable con gli stessi nomi e tipi del tipo di tabella.In code, you create a DataTable with the exact same names and types of the table type. Passare l'oggetto DataTable in un parametro in una chiamata di stored procedure o query di testo.Pass this DataTable in a parameter in a text query or stored procedure call. Questa tecnica è illustrata nell'esempio seguente:The following example shows this technique:

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();
}

Nell'esempio precedente l'oggetto SqlCommand inserisce righe da un parametro con valori di tabella, @TestTvp.In the previous example, the SqlCommand object inserts rows from a table-valued parameter, @TestTvp. L'oggetto DataTable creato in precedenza viene assegnato a questo parametro con il metodo SqlCommand.Parameters.Add.The previously created DataTable object is assigned to this parameter with the SqlCommand.Parameters.Add method. L'invio in batch delle operazioni di inserimento in una singola chiamata migliora sensibilmente le prestazioni rispetto alle operazioni di inserimento sequenziali.Batching the inserts in one call significantly increases the performance over sequential inserts.

Per migliorare ulteriormente l'esempio precedente, usare una stored procedure anziché un comando basato su testo.To improve the previous example further, use a stored procedure instead of a text-based command. Il comando Transact-SQL seguente crea una stored procedure che accetta il parametro con valori di tabella SimpleTestTableType .The following Transact-SQL command creates a stored procedure that takes the SimpleTestTableType table-valued parameter.

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

Modificare quindi la dichiarazione dell'oggetto SqlCommand nell'esempio di codice precedente come segue.Then change the SqlCommand object declaration in the previous code example to the following.

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

Nella maggior parte dei casi i parametri con valori di tabella hanno prestazioni equivalenti o superiori rispetto ad altre tecniche di invio in batch.In most cases, table-valued parameters have equivalent or better performance than other batching techniques. I parametri con valori di tabella sono spesso preferibili perché più flessibili di altre opzioni.Table-valued parameters are often preferable, because they are more flexible than other options. Ad esempio, altre tecniche come la copia bulk di SQL consentono solo l'inserimento di nuove righe.For example, other techniques, such as SQL bulk copy, only permit the insertion of new rows. Con i parametri con valori di tabella è invece possibile usare la logica della stored procedure per determinare le righe da aggiornare e quelle da inserire.But with table-valued parameters, you can use logic in the stored procedure to determine which rows are updates and which are inserts. È anche possibile modificare il tipo di tabella perché contenga la colonna "Operation" che indica se la riga specificata deve essere inserita, aggiornata o eliminata.The table type can also be modified to contain an “Operation” column that indicates whether the specified row should be inserted, updated, or deleted.

La tabella seguente illustra i risultati, in millisecondi, dei test ad hoc per l'uso di parametri con valori di tabella.The following table shows ad-hoc test results for the use of table-valued parameters in milliseconds.

OperazioniOperations Da ambiente locale ad Azure (ms)On-Premises to Azure (ms) Azure stesso data center (ms)Azure same datacenter (ms)
11 124124 3232
1010 131131 2525
100100 338338 5151
10001000 26152615 382382
1000010000 2383023830 35863586

Nota

I risultati non sono benchmark.Results are not benchmarks. Vedere la nota sui risultati della tempistica in questo argomento.See the note about timing results in this topic.

Il miglioramento delle prestazioni che deriva dall'invio in batch è evidente.The performance gain from batching is immediately apparent. Nel test sequenziale precedente 1000 operazioni hanno richiesto 129 secondi all'esterno del data center e 21 secondi all'interno del data center.In the previous sequential test, 1000 operations took 129 seconds outside the datacenter and 21 seconds from within the datacenter. Con i parametri con valori di tabella 1000 operazioni richiedono invece solo 2,6 secondi all'esterno del data center e 0,4 secondi all'interno.But with table-valued parameters, 1000 operations take only 2.6 seconds outside the datacenter and 0.4 seconds within the datacenter.

Per altre informazioni sui parametri con valori di tabella, vedere Usare parametri con valori di tabella (motore di database).For more information on table-valued parameters, see Table-Valued Parameters.

Copia bulk di SQLSQL bulk copy

La copia bulk di SQL è un altro modo per inserire una grande quantità di dati in un database di destinazione.SQL bulk copy is another way to insert large amounts of data into a target database. Le applicazioni .NET possono usare la classe SqlBulkCopy per eseguire le operazioni di inserimento bulk..NET applications can use the SqlBulkCopy class to perform bulk insert operations. In termini di funzionamento, la classe SqlBulkCopy è analoga allo strumento da riga di comando Bcp.exe o all'istruzione Transact-SQL BULK INSERT.SqlBulkCopy is similar in function to the command-line tool, Bcp.exe, or the Transact-SQL statement, BULK INSERT. L'esempio di codice seguente illustra come eseguire la copia bulk delle righe nella tabella di origine DataTablealla tabella di destinazione MyTable in SQL Server.The following code example shows how to bulk copy the rows in the source DataTable, table, to the destination table in 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);
    }
}

In alcuni casi la copia bulk è preferibile rispetto ai parametri con valori di tabella.There are some cases where bulk copy is preferred over table-valued parameters. Vedere la tabella di confronto dei parametri con valori di tabella rispetto alle operazioni BULK INSERT nell'argomento Parametri con valori di tabella (motore di database).See the comparison table of Table-Valued parameters versus BULK INSERT operations in the topic Table-Valued Parameters.

I risultati dei test ad hoc seguenti mostrano le prestazioni, in millisecondi, dell'invio in batch con SqlBulkCopy .The following ad-hoc test results show the performance of batching with SqlBulkCopy in milliseconds.

OperazioniOperations Da ambiente locale ad Azure (ms)On-Premises to Azure (ms) Azure stesso data center (ms)Azure same datacenter (ms)
11 433433 5757
1010 441441 3232
100100 636636 5353
10001000 25352535 341341
1000010000 2160521605 27372737

Nota

I risultati non sono benchmark.Results are not benchmarks. Vedere la nota sui risultati della tempistica in questo argomento.See the note about timing results in this topic.

Nei batch di dimensioni inferiori l'uso dei parametri con valori di tabella ha prodotto prestazioni migliori rispetto alla classe SqlBulkCopy .In smaller batch sizes, the use table-valued parameters outperformed the SqlBulkCopy class. Tuttavia, l'esecuzione della classe SqlBulkCopy risulta del 12-31% più rapida rispetto ai parametri con valori di tabella per i test di 1.000 e 10.000 righe.However, SqlBulkCopy performed 12-31% faster than table-valued parameters for the tests of 1,000 and 10,000 rows. Come i parametri con valori di tabella, la classe SqlBulkCopy è un'opzione valida per le operazioni di inserimento in batch, in particolare rispetto alle prestazioni di operazioni non in batch.Like table-valued parameters, SqlBulkCopy is a good option for batched inserts, especially when compared to the performance of non-batched operations.

Per altre informazioni sulla copia bulk in ADO.NET, vedere Operazioni di copia bulk in SQL Server.For more information on bulk copy in ADO.NET, see Bulk Copy Operations in SQL Server.

Istruzioni INSERT con parametri a più righeMultiple-row Parameterized INSERT statements

Un'alternativa per i batch piccoli consiste nella creazione di un'istruzione INSERT con parametri di grandi dimensioni per l'inserimento di più righe.One alternative for small batches is to construct a large parameterized INSERT statement that inserts multiple rows. L'esempio di codice seguente illustra questa tecnica.The following code example demonstrates this technique.

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();
}

Questo esempio è ideato per illustrare il concetto di base.This example is meant to show the basic concept. Uno scenario più realistico prevedrebbe l'esecuzione di un ciclo sulle entità richieste per costruire contemporaneamente la stringa di query e i parametri del comando.A more realistic scenario would loop through the required entities to construct the query string and the command parameters simultaneously. Esiste un limite massimo di 2100 parametri di query, il che limita il numero totale di righe elaborabili in questo modo.You are limited to a total of 2100 query parameters, so this limits the total number of rows that can be processed in this manner.

I risultati dei test ad hoc seguenti mostrano le prestazioni di questo tipo di istruzione di inserimento, espresse in millisecondi.The following ad-hoc test results show the performance of this type of insert statement in milliseconds.

OperazioniOperations Parametri con valori di tabella (ms)Table-valued parameters (ms) Singola istruzione INSERT (ms)Single-statement INSERT (ms)
11 3232 2020
1010 3030 2525
100100 3333 5151

Nota

I risultati non sono benchmark.Results are not benchmarks. Vedere la nota sui risultati della tempistica in questo argomento.See the note about timing results in this topic.

Questo approccio può essere leggermente più veloce per i batch minori di 100 righe.This approach can be slightly faster for batches that are less than 100 rows. Sebbene l'entità del miglioramento sia lieve, questa tecnica rappresenta un'altra opzione che potrebbe rivelarsi utile in scenari di applicazione specifici.Although the improvement is small, this technique is another option that might work well in your specific application scenario.

DataAdapterDataAdapter

La classe DataAdapter consente di modificare un oggetto DataSet e di inviare quindi le modifiche come operazioni di tipo INSERT, UPDATE e DELETE.The DataAdapter class allows you to modify a DataSet object and then submit the changes as INSERT, UPDATE, and DELETE operations. Se si usa DataAdapter in questo modo, è importante notare che vengono eseguite chiamate separate per ogni singola operazione.If you are using the DataAdapter in this manner, it is important to note that separate calls are made for each distinct operation. Per migliorare le prestazioni, usare la proprietà UpdateBatchSize impostata sul numero di operazioni da eseguire in batch contemporaneamente.To improve performance, use the UpdateBatchSize property to the number of operations that should be batched at a time. Per altre informazioni, vedere Esecuzione di operazioni batch usando DataAdapters.For more information, see Performing Batch Operations Using DataAdapters.

Entity FrameworkEntity framework

Entity Framework non supporta attualmente l'invio in batch.Entity Framework does not currently support batching. Diversi sviluppatori della community hanno provato a elaborare soluzioni alternative, ad esempio l'override del metodo SaveChanges .Different developers in the community have attempted to demonstrate workarounds, such as override the SaveChanges method. Tuttavia, le soluzioni sono in genere complesse e personalizzate a seconda dell'applicazione e del modello di dati.But the solutions are typically complex and customized to the application and data model. Il progetto codeplex di Entity Framework include attualmente una pagina di discussione sulla richiesta di questa funzionalità.The Entity Framework codeplex project currently has a discussion page on this feature request. Per accedere alla discussione, vedere la pagina Design Meeting Notes - 2 agosto 2012.To view this discussion, see Design Meeting Notes - August 2, 2012.

XMLXML

Per completezza, è importante considerare l'XML come strategia di invio in batch.For completeness, we feel that it is important to talk about XML as a batching strategy. Tuttavia, l'uso di XML non offre vantaggi rispetto ad altri metodi e presenta numerosi svantaggi.However, the use of XML has no advantages over other methods and several disadvantages. L'approccio è simile a quello dei parametri con valori di tabella, con la differenza che alla stored procedure viene passato un file o una stringa XML invece di una tabella definita dall'utente.The approach is similar to table-valued parameters, but an XML file or string is passed to a stored procedure instead of a user-defined table. La stored procedure analizza i comandi al suo interno.The stored procedure parses the commands in the stored procedure.

Questo approccio presenta diversi svantaggi:There are several disadvantages to this approach:

  • L'uso di XML può risultare complesso e soggetto a errori.Working with XML can be cumbersome and error prone.
  • L'analisi dell'XML nel database può implicare un uso intensivo della CPU.Parsing the XML on the database can be CPU-intensive.
  • Nella maggior parte dei casi questo metodo è più lento rispetto ai parametri con valori di tabella.In most cases, this method is slower than table-valued parameters.

Per questi motivi, l'uso dell'XML per le query in batch non è consigliabile.For these reasons, the use of XML for batch queries is not recommended.

Considerazioni sull'invio in batchBatching considerations

Le sezioni seguenti includono altre indicazioni per l'uso dell'invio in batch nelle applicazioni di database SQL.The following sections provide more guidance for the use of batching in SQL Database applications.

CompromessiTradeoffs

A seconda dell'architettura, l'invio in batch può comportare un compromesso tra prestazioni e resilienza.Depending on your architecture, batching can involve a tradeoff between performance and resiliency. Si consideri ad esempio uno scenario in cui il proprio ruolo viene inaspettatamente disattivato.For example, consider the scenario where your role unexpectedly goes down. La perdita di una riga di dati produce un impatto inferiore alla perdita di un grosso batch di righe non inviate.If you lose one row of data, the impact is smaller than the impact of losing a large batch of unsubmitted rows. Esiste un rischio maggiore nei casi in cui le righe vengono memorizzate in un buffer prima dell'invio al database in una finestra temporale specificata.There is a greater risk when you buffer rows before sending them to the database in a specified time window.

Considerando questo compromesso, valutare con attenzione i tipi di operazioni da eseguire in batch.Because of this tradeoff, evaluate the type of operations that you batch. Usare più ampiamente i batch, con dimensioni maggiori e finestre temporali più ampie, per i dati meno critici.Batch more aggressively (larger batches and longer time windows) with data that is less critical.

Dimensioni dei batchBatch size

I test non hanno in genere evidenziato vantaggi correlati alla suddivisione di batch di grandi dimensioni in blocchi più piccoli.In our tests, there was typically no advantage to breaking large batches into smaller chunks. In effetti, questa suddivisione ha causato spesso prestazioni inferiori rispetto all'invio di un singolo batch di grandi dimensioni.In fact, this subdivision often resulted in slower performance than submitting a single large batch. Si consideri ad esempio uno scenario che prevede l'inserimento di 1000 righe.For example, consider a scenario where you want to insert 1000 rows. La tabella seguente mostra il tempo necessario per usare parametri con valori di tabella per l'inserimento di 1000 righe divise in batch più piccoli.The following table shows how long it takes to use table-valued parameters to insert 1000 rows when divided into smaller batches.

Dimensioni dei batchBatch size IterazioniIterations Parametri con valori di tabella (ms)Table-valued parameters (ms)
10001000 11 347347
500500 22 355355
100100 1010 465465
5050 2020 630630

Nota

I risultati non sono benchmark.Results are not benchmarks. Vedere la nota sui risultati della tempistica in questo argomento.See the note about timing results in this topic.

È possibile notare che le prestazioni migliori per 1000 righe si ottengono inviandole tutte insieme.You can see that the best performance for 1000 rows is to submit them all at once. In altri test, non riportati qui, si è notato un lieve miglioramento delle prestazioni suddividendo un batch di 10000 righe in due batch da 5000.In other tests (not shown here) there was a small performance gain to break a 10000 row batch into two batches of 5000. Lo schema della tabella in questi test è tuttavia relativamente semplice, quindi è consigliabile condurre test su dati e dimensioni di batch specifici per verificare questi risultati.But the table schema for these tests is relatively simple, so you should perform tests on your specific data and batch sizes to verify these findings.

Un altro fattore da tenere presente è il fatto che se il batch complessivo diventa troppo grande, il database SQL potrebbe applicare limitazioni e rifiutarne il commit.Another factor to consider is that if the total batch becomes too large, SQL Database might throttle and refuse to commit the batch. Per ottenere risultati ottimali, testare il proprio scenario specifico per determinare le dimensioni ideali dei batch.For the best results, test your specific scenario to determine if there is an ideal batch size. Rendere configurabili le dimensioni del batch in fase di esecuzione per consentire modifiche rapide in base alle prestazioni o agli errori.Make the batch size configurable at runtime to enable quick adjustments based on performance or errors.

Infine, individuare un equilibro tra le dimensioni del batch e i rischi associati all'invio in batch.Finally, balance the size of the batch with the risks associated with batching. Se si verificano errori temporanei o il ruolo ha esito negativo, valutare le conseguenze di dover ripetere l'operazione o di perdere i dati nel batch.If there are transient errors or the role fails, consider the consequences of retrying the operation or of losing the data in the batch.

Elaborazione parallelaParallel processing

Che cosa accadrebbe se si adottasse l'approccio di ridurre le dimensioni del batch, ma si utilizzassero più thread per eseguire le operazioni?What if you took the approach of reducing the batch size but used multiple threads to execute the work? Anche in questo caso, i test indicano che diversi batch multithreading di dimensioni ridotte producono prestazioni generalmente inferiori a un singolo batch di dimensioni maggiori.Again, our tests showed that several smaller multithreaded batches typically performed worse than a single larger batch. Il test seguente tenta di inserire 1000 righe in uno o più batch paralleli.The following test attempts to insert 1000 rows in one or more parallel batches. Questo test indica come più batch simultanei causino in effetti una riduzione delle prestazioni.This test shows how more simultaneous batches actually decreased performance.

Dimensioni dei batch [iterazioni]Batch size [Iterations] Due thread (ms)Two threads (ms) Quattro thread (ms)Four threads (ms) Sei thread (ms)Six threads (ms)
1000 [1]1000 [1] 277277 315315 266266
500 [2]500 [2] 548548 278278 256256
250 [4]250 [4] 405405 329329 265265
100 [10]100 [10] 488488 439439 391391

Nota

I risultati non sono benchmark.Results are not benchmarks. Vedere la nota sui risultati della tempistica in questo argomento.See the note about timing results in this topic.

Esistono diverse potenziali cause della riduzione delle prestazioni derivante dal parallelismo:There are several potential reasons for the degradation in performance due to parallelism:

  • Sono presenti più chiamate di rete simultanee invece di una.There are multiple simultaneous network calls instead of one.
  • L'esecuzione di più operazioni su una singola tabella può determinare contese e blocchi.Multiple operations against a single table can result in contention and blocking.
  • Il multithreading implica overhead.There are overheads associated with multithreading.
  • Il costo dell'apertura di più connessioni annulla il vantaggio dell'elaborazione parallela.The expense of opening multiple connections outweighs the benefit of parallel processing.

Se si opera su più tabelle o database, questa strategia può produrre un aumento delle prestazioni.If you target different tables or databases, it is possible to see some performance gain with this strategy. Il partizionamento orizzontale o le federazioni di database rappresentano uno scenario possibile per questo approccio.Database sharding or federations would be a scenario for this approach. Il partizionamento orizzontale usa più database e indirizza dati diversi a ognuno di essi.Sharding uses multiple databases and routes different data to each database. Se ogni piccolo batch viene indirizzato a un database diverso, l'esecuzione delle operazioni in parallelo può risultare più efficiente.If each small batch is going to a different database, then performing the operations in parallel can be more efficient. Tuttavia, il miglioramento delle prestazioni non è sufficientemente elevato da giustificare la decisione di adottare il partizionamento orizzontale del database nella propria soluzione.However, the performance gain is not significant enough to use as the basis for a decision to use database sharding in your solution.

In alcune progettazioni, l'esecuzione parallela di batch di piccole dimensioni può produrre un miglioramento della velocità effettiva delle richieste in un sistema sotto carico.In some designs, parallel execution of smaller batches can result in improved throughput of requests in a system under load. In questo caso, anche se l'esecuzione di un singolo batch di dimensioni maggiori è più rapida, quella di più batch in parallelo può risultare più efficiente.In this case, even though it is quicker to process a single larger batch, processing multiple batches in parallel might be more efficient.

Se si usa l'esecuzione parallela, provare a controllare il numero massimo di thread di lavoro.If you do use parallel execution, consider controlling the maximum number of worker threads. Un numero più piccolo potrebbe ridurre il rischio di contese e i tempi di esecuzione.A smaller number might result in less contention and a faster execution time. Tenere anche presente il carico aggiuntivo di questa soluzione sul database di destinazione, sia in termini di connessioni sia di transazioni.Also, consider the additional load that this places on the target database both in connections and transactions.

Le indicazioni tipiche relative alle prestazioni dei database influiscono anche sull'invio in batch.Typical guidance on database performance also affects batching. Le prestazioni delle operazioni di inserimento, ad esempio, risultano ridotte per le tabelle con chiave primaria di grandi dimensioni o con numerosi indici non cluster.For example, insert performance is reduced for tables that have a large primary key or many nonclustered indexes.

Se i parametri con valori di tabella usano una stored procedure, è possibile eseguire il comando SET NOCOUNT ON all'inizio della routine.If table-valued parameters use a stored procedure, you can use the command SET NOCOUNT ON at the beginning of the procedure. Questa istruzione rimuove la restituzione del conteggio delle righe interessate nella routine.This statement suppresses the return of the count of the affected rows in the procedure. Tuttavia, nei test l'uso di SET NOCOUNT ON non ha avuto alcun effetto sulle prestazioni o le ha ridotte.However, in our tests, the use of SET NOCOUNT ON either had no effect or decreased performance. La stored procedure usata per i test era semplice, con un singolo comando INSERT del parametro con valori di tabella.The test stored procedure was simple with a single INSERT command from the table-valued parameter. È possibile che stored procedure più complesse possano trarre vantaggio da questa istruzione.It is possible that more complex stored procedures would benefit from this statement. Non si deve tuttavia supporre che l'aggiunta di SET NOCOUNT ON alla stored procedure migliori automaticamente le prestazioni.But don’t assume that adding SET NOCOUNT ON to your stored procedure automatically improves performance. Per comprendere l'effetto, testare la stored procedure con e senza l'istruzione SET NOCOUNT ON .To understand the effect, test your stored procedure with and without the SET NOCOUNT ON statement.

Scenari di invio in batchBatching scenarios

Nelle sezioni seguenti viene descritto come usare parametri con valori di tabella in tre scenari di applicazione.The following sections describe how to use table-valued parameters in three application scenarios. Il primo scenario illustra in che modo interagiscono il buffering e l'invio in batch.The first scenario shows how buffering and batching can work together. Nel secondo scenario le prestazioni vengono migliorate eseguendo operazioni master/dettaglio in una singola chiamata di stored procedure.The second scenario improves performance by performing master-detail operations in a single stored procedure call. Lo scenario finale illustra come usare parametri con valori di tabella in un'operazione "UPSERT".The final scenario shows how to use table-valued parameters in an “UPSERT” operation.

BufferingBuffering

Sebbene alcuni scenari siano particolarmente adatti all'invio in batch, ne esistono numerosi che potrebbero trarre vantaggio da questo tipo di operazione grazie all'elaborazione ritardata.Although there are some scenarios that are obvious candidate for batching, there are many scenarios that could take advantage of batching by delayed processing. L'elaborazione ritardata implica tuttavia anche un maggiore rischio che i dati vengano persi in caso di errore imprevisto.However, delayed processing also carries a greater risk that the data is lost in the event of an unexpected failure. È importante comprendere tale rischio e valutarne le conseguenze.It is important to understand this risk and consider the consequences.

Si consideri ad esempio un'applicazione Web che tiene traccia della cronologia di navigazione di ogni utente.For example, consider a web application that tracks the navigation history of each user. Per ogni richiesta di pagina, l'applicazione potrebbe eseguire una chiamata al database per registrare la visualizzazione della pagina da parte dell'utente.On each page request, the application could make a database call to record the user’s page view. Tuttavia è possibile conseguire livelli maggiori di prestazioni e scalabilità mediante il buffering delle attività di navigazione dell'utente e quindi inviando i dati al database in batch.But higher performance and scalability can be achieved by buffering the users’ navigation activities and then sending this data to the database in batches. È possibile attivare l'aggiornamento del database in base al tempo trascorso e/o alle dimensioni del buffer.You can trigger the database update by elapsed time and/or buffer size. Ad esempio, una regola potrebbe indicare che il batch deve essere elaborato dopo 20 secondi o quando il buffer raggiunge i 1000 elementi.For example, a rule could specify that the batch should be processed after 20 seconds or when the buffer reaches 1000 items.

L'esempio di codice seguente usa Reactive Extensions - Rx per elaborare eventi memorizzati nel buffer generati da una classe di monitoraggio.The following code example uses Reactive Extensions - Rx to process buffered events raised by a monitoring class. Quando il buffer si riempie o si raggiunge un timeout, il batch di dati utente viene inviato al database con un parametro con valori di tabella.When the buffer fills or a timeout is reached, the batch of user data is sent to the database with a table-valued parameter.

La classe NavHistoryData seguente modella i dettagli di navigazione dell'utente.The following NavHistoryData class models the user navigation details. Contiene informazioni di base quali l'identificatore utente, l'URL a cui si accede e l'ora di accesso.It contains basic information such as the user identifier, the URL accessed, and the access time.

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 classe NavHistoryDataMonitor è responsabile del buffering dei dati di navigazione dell'utente nel database.The NavHistoryDataMonitor class is responsible for buffering the user navigation data to the database. Contiene un metodo RecordUserNavigationEntry che risponde generando un evento OnAdded .It contains a method, RecordUserNavigationEntry, which responds by raising an OnAdded event. Il codice seguente illustra la logica del costruttore che utilizza Rx per creare una raccolta osservabile in base all'evento.The following code shows the constructor logic that uses Rx to create an observable collection based on the event. Sottoscrive quindi questa raccolta osservabile con il metodo Buffer.It then subscribes to this observable collection with the Buffer method. L'overload specifica che il buffer deve essere inviato ogni 20 secondi o 1000 voci.The overload specifies that the buffer should be sent every 20 seconds or 1000 entries.

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

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

Il gestore converte tutti gli elementi memorizzati nel buffer nel tipo con valori di tabella e quindi passa questo tipo a una stored procedure che elabora il batch.The handler converts all of the buffered items into a table-valued type and then passes this type to a stored procedure that processes the batch. Il codice seguente illustra la definizione completa per le classi NavHistoryDataEventArgs e NavHistoryDataMonitor.The following code shows the complete definition for both the NavHistoryDataEventArgs and the NavHistoryDataMonitor classes.

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();
        }
    }
}

Per usare questa classe di buffering, l'applicazione crea un oggetto statico NavHistoryDataMonitor.To use this buffering class, the application creates a static NavHistoryDataMonitor object. Ogni volta che l'utente accede a una pagina, l'applicazione chiama il metodo NavHistoryDataMonitor.RecordUserNavigationEntry.Each time a user accesses a page, the application calls the NavHistoryDataMonitor.RecordUserNavigationEntry method. La logica di buffering continua a provvedere all'invio delle voci al database in batch.The buffering logic proceeds to take care of sending these entries to the database in batches.

Master/dettaglioMaster detail

I parametri con valori di tabella sono utili per gli scenari INSERT semplici.Table-valued parameters are useful for simple INSERT scenarios. Tuttavia, può risultare più difficile inviare in batch le operazioni di inserimento che includono più tabelle.However, it can be more challenging to batch inserts that involve more than one table. Lo scenario "master/dettaglio" è un buon esempio.The “master/detail” scenario is a good example. La tabella master identifica l'entità primaria.The master table identifies the primary entity. In una o più tabelle dei dettagli sono archiviati altri dati sull'entità.One or more detail tables store more data about the entity. In questo scenario, le relazioni di chiave esterna applicano la relazione dei dettagli a un'entità master univoca.In this scenario, foreign key relationships enforce the relationship of details to a unique master entity. Si consideri una versione semplificata di una tabella PurchaseOrder e la tabella associata OrderDetail.Consider a simplified version of a PurchaseOrder table and its associated OrderDetail table. L'istruzione Transact-SQL seguente crea la tabella PurchaseOrder con quattro colonne: OrderID, OrderDate, CustomerID e Status.The following Transact-SQL creates the PurchaseOrder table with four columns: OrderID, OrderDate, CustomerID, and 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 ))

Ogni ordine contiene uno o più acquisti di prodotti.Each order contains one or more product purchases. Tali informazioni vengono acquisite nella tabella PurchaseOrderDetail.This information is captured in the PurchaseOrderDetail table. L'istruzione Transact-SQL seguente crea la tabella PurchaseOrderDetail con cinque colonne: OrderID, OrderDetailID, ProductID, UnitPrice e OrderQty.The following Transact-SQL creates the PurchaseOrderDetail table with five columns: OrderID, OrderDetailID, ProductID, UnitPrice, and 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 colonna OrderID della tabella PurchaseOrderDetail deve fare riferimento a un ordine dalla tabella PurchaseOrder.The OrderID column in the PurchaseOrderDetail table must reference an order from the PurchaseOrder table. La seguente definizione di una chiave esterna applica il vincolo.The following definition of a foreign key enforces this constraint.

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

Per usare parametri con valori di tabella, è necessario avere un tipo di tabella definito dall'utente per ogni tabella di destinazione.In order to use table-valued parameters, you must have one user-defined table type for each target table.

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

Definire quindi una stored procedure che accetti tabelle di questi tipi.Then define a stored procedure that accepts tables of these types. Questa routine consente a un'applicazione di inviare in batch localmente un set di ordini e i dettagli degli ordini in una singola chiamata.This procedure allows an application to locally batch a set of orders and order details in a single call. L'istruzione Transact-SQL seguente fornisce la dichiarazione completa della stored procedure per questo esempio di ordine di acquisto.The following Transact-SQL provides the complete stored procedure declaration for this purchase order example.

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

In questo esempio la tabella @IdentityLink definita a livello locale archivia i valori OrderID effettivi dalle righe appena inserite.In this example, the locally defined @IdentityLink table stores the actual OrderID values from the newly inserted rows. Gli identificatori di ordine sono diversi dai valori OrderID temporanei nei parametri con valori di tabella @orders e @details.These order identifiers are different from the temporary OrderID values in the @orders and @details table-valued parameters. Per questo motivo, la tabella @IdentityLink connette quindi i valori OrderID del parametro @orders ai valori OrderID reali per le nuove righe della tabella PurchaseOrder.For this reason, the @IdentityLink table then connects the OrderID values from the @orders parameter to the real OrderID values for the new rows in the PurchaseOrder table. Dopo questo passaggio, la tabella @IdentityLink può facilitare l'inserimento dei dettagli degli ordini con il valore OrderID effettivo che soddisfa il vincolo di chiave esterna.After this step, the @IdentityLink table can facilitate inserting the order details with the actual OrderID that satisfies the foreign key constraint.

Questa stored procedure può essere utilizzata dal codice o da altre chiamate Transact-SQL.This stored procedure can be used from code or from other Transact-SQL calls. Vedere la sezione sui parametri con valori di tabella di questo articolo per un esempio di codice.See the table-valued parameters section of this paper for a code example. Il codice Transact-SQL seguente mostra come chiamare sp_InsertOrdersBatch.The following Transact-SQL shows how to call the 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

Questa soluzione consente a ogni batch di usare un set di valori OrderID che iniziano da 1.This solution allows each batch to use a set of OrderID values that begin at 1. Questi valori OrderID temporanei descrivono le relazioni nel batch, ma i valori OrderID effettivi vengono determinati al momento dell'operazione di inserimento.These temporary OrderID values describe the relationships in the batch, but the actual OrderID values are determined at the time of the insert operation. È possibile eseguire ripetutamente le stesse istruzioni dell'esempio precedente e generare ordini univoci nel database.You can run the same statements in the previous example repeatedly and generate unique orders in the database. Per questo motivo, considerare l'aggiunta di più logica di database o di codice che impedisca gli ordini duplicati quando si usa questa tecnica di invio in batch.For this reason, consider adding more code or database logic that prevents duplicate orders when using this batching technique.

In questo esempio viene spiegato che è possibile eseguire in batch anche operazioni di database più complesse, ad esempio operazioni master/dettaglio, usando i parametri con valori di tabella.This example demonstrates that even more complex database operations, such as master-detail operations, can be batched using table-valued parameters.

UPSERTUPSERT

Un altro scenario di invio in batch prevede l'aggiornamento simultaneo di righe esistenti e l'inserimento di nuove righe.Another batching scenario involves simultaneously updating existing rows and inserting new rows. Questa operazione viene talvolta denominata "UPSERT" (aggiornamento + inserimento).This operation is sometimes referred to as an “UPSERT” (update + insert) operation. Invece di eseguire chiamate separate a INSERT e UPDATE, l'istruzione MERGE è più adatta a questa attività.Rather than making separate calls to INSERT and UPDATE, the MERGE statement is best suited to this task. L'istruzione MERGE può eseguire operazioni di inserimento e aggiornamento in una singola chiamata.The MERGE statement can perform both insert and update operations in a single call.

I parametri con valori di tabella possono essere usati con l'istruzione MERGE per eseguire aggiornamenti e inserimenti.Table-valued parameters can be used with the MERGE statement to perform updates and inserts. Si consideri ad esempio una tabella Employee semplificata che contiene le colonne seguenti: EmployeeID, FirstName, LastName, SocialSecurityNumber:For example, consider a simplified Employee table that contains the following columns: 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 ))

In questo esempio, è possibile usare il fatto che la colonna SocialSecurityNumber è unica per eseguire un'operazione MERGE di più dipendenti.In this example, you can use the fact that the SocialSecurityNumber is unique to perform a MERGE of multiple employees. Creare innanzitutto il tipo di tabella definito dall'utente:First, create the user-defined table type:

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

Successivamente, creare una stored procedure oppure scrivere codice che usi l'istruzione MERGE per eseguire l'aggiornamento e l'inserimento.Next, create a stored procedure or write code that uses the MERGE statement to perform the update and insert. L'esempio seguente usa l'istruzione MERGE in un parametro con valori di tabella, @employees, di tipo EmployeeTableType.The following example uses the MERGE statement on a table-valued parameter, @employees, of type EmployeeTableType. Il contenuto della tabella @employees non è illustrato.The contents of the @employees table are not shown here.

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]);

Per altre informazioni, vedere la documentazione e gli esempi relativi all'istruzione MERGE.For more information, see the documentation and examples for the MERGE statement. Anche se la stessa operazione può essere eseguita in una chiamata di stored procedure in più passaggi con le operazioni INSERT e UPDATE, l'istruzione MERGE è più efficiente.Although the same work could be performed in a multiple-step stored procedure call with separate INSERT and UPDATE operations, the MERGE statement is more efficient. Il codice del database può anche creare chiamate Transact-SQL che usano l'istruzione MERGE direttamente senza richiedere due chiamate di database per INSERT e UPDATE.Database code can also construct Transact-SQL calls that use the MERGE statement directly without requiring two database calls for INSERT and UPDATE.

Riepilogo delle indicazioniRecommendation summary

L'elenco seguente fornisce un riepilogo delle indicazioni relative all'invio in batch descritte in questo argomento:The following list provides a summary of the batching recommendations discussed in this topic:

  • Usare il buffering e l'invio in batch per migliorare le prestazioni e la scalabilità delle applicazioni di database SQL.Use buffering and batching to increase the performance and scalability of SQL Database applications.
  • Esaminare i compromessi tra invio in batch/buffering e resilienza.Understand the tradeoffs between batching/buffering and resiliency. In caso di errore in un ruolo, il rischio di perdere un batch non elaborato di dati aziendali critici potrebbe vanificare il vantaggio in termini di prestazioni che deriva dall'invio in batch.During a role failure, the risk of losing an unprocessed batch of business-critical data might outweigh the performance benefit of batching.
  • Provare a mantenere tutte le chiamate al database entro un singolo data center per ridurre la latenza.Attempt to keep all calls to the database within a single datacenter to reduce latency.
  • Se si sceglie una singola tecnica di invio in batch, i parametri con valori di tabella offrono il miglior rapporto tra prestazioni e flessibilità.If you choose a single batching technique, table-valued parameters offer the best performance and flexibility.
  • Per prestazioni di inserimento estremamente veloci, attenersi alle linee guida generali, ma testare lo scenario:For the fastest insert performance, follow these general guidelines but test your scenario:
    • Per < 100 righe, usare un singolo comando INSERT con parametri.For < 100 rows, use a single parameterized INSERT command.
    • Per < 1000 righe usare parametri con valori di tabella.For < 1000 rows, use table-valued parameters.
    • Per >= 1000 righe usare SqlBulkCopy.For >= 1000 rows, use SqlBulkCopy.
  • Per operazioni di aggiornamento ed eliminazione, usare parametri con valori di tabella con la logica della stored procedure che determini il corretto funzionamento in ogni riga del parametro della tabella.For update and delete operations, use table-valued parameters with stored procedure logic that determines the correct operation on each row in the table parameter.
  • Linee guida sulle dimensioni dei batch:Batch size guidelines:
    • Usare batch di grandi dimensioni considerando i requisiti delle applicazioni e aziendali.Use the largest batch sizes that make sense for your application and business requirements.
    • Bilanciare il miglioramento delle prestazioni dei batch di grandi dimensioni e i rischi di errori temporanei o irreversibili.Balance the performance gain of large batches with the risks of temporary or catastrophic failures. Qual è la conseguenza di più tentativi o di perdita dei dati nel batch?What is the consequence of retries or loss of the data in the batch?
    • Testare i batch con le massime dimensioni per verificare che il database SQL non li rifiuti.Test the largest batch size to verify that SQL Database does not reject it.
    • Creare le impostazioni di configurazione che controllano l'invio in batch, ad esempio le dimensioni dei batch o la finestra temporale di buffering.Create configuration settings that control batching, such as the batch size or the buffering time window. Queste impostazioni offrono flessibilità.These settings provide flexibility. È possibile modificare il comportamento di invio in batch in produzione senza ridistribuire il servizio cloud.You can change the batching behavior in production without redeploying the cloud service.
  • Evitare l'esecuzione parallela di batch che operano su una singola tabella in un database.Avoid parallel execution of batches that operate on a single table in one database. Se si sceglie di dividere un singolo batch tra più thread di lavoro, eseguire i test per determinare il numero ideale di thread.If you do choose to divide a single batch across multiple worker threads, run tests to determine the ideal number of threads. Dopo una soglia non specificata, più thread determinano una riduzione delle prestazioni invece di un aumento.After an unspecified threshold, more threads will decrease performance rather than increase it.
  • Considerare il buffering in base a dimensioni e tempo come modalità di implementazione dell'invio in batch per più scenari.Consider buffering on size and time as a way of implementing batching for more scenarios.

Passaggi successiviNext steps

Questo articolo descrive in che modo le tecniche di progettazione e codifica di database correlate all'invio in batch possano migliorare le prestazioni e la scalabilità delle applicazioni.This article focused on how database design and coding techniques related to batching can improve your application performance and scalability. Questo è però solo uno dei fattori della strategia complessiva.But this is just one factor in your overall strategy. Per altri modi per migliorare le prestazioni e la scalabilità, vedere le indicazioni sulle prestazioni del database SQL di Azure per i database singoli e le considerazioni su prezzo e prestazioni per un pool elastico.For more ways to improve performance and scalability, see Azure SQL Database performance guidance for single databases and Price and performance considerations for an elastic pool.