Como usar o envio em lote para melhorar o desempenho do Banco de Dados SQL do Azure e do aplicativo de Instância Gerenciada SQL do Azure
Aplica-se a:
Banco de Dados SQL do Azure Instância Gerenciada SQLdo Azure
As operações em lote para o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure melhoram significativamente o desempenho e a escalabilidade de seus aplicativos. Para entender os benefícios, a primeira parte deste artigo aborda alguns resultados de teste de exemplo que comparam solicitações sequenciais e em lote a um banco de dados no Banco de Dados SQL do Azure ou na Instância Gerenciada SQL do Azure. O restante do artigo mostra as técnicas, cenários e considerações para ajudá-lo a usar o envio em lote com êxito em seus aplicativos do Azure.
Por que o envio em lote é importante para o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure?
Enviar chamadas em lote para um serviço remoto é uma estratégia bem conhecida para aumentar o desempenho e a escalabilidade. Há custos fixos de processamento para quaisquer interações com um serviço remoto, como serialização, transferência de rede e desserialização. Empacotar muitas transações separadas em um único lote minimiza esses custos.
Neste artigo, queremos examinar várias estratégias e cenários de loteamento. Embora essas estratégias também sejam importantes para aplicativos locais que usam o SQL Server, há vários motivos para destacar o uso de lotes para o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure:
- Há uma latência de rede potencialmente maior no acesso ao Banco de Dados SQL do Azure e à Instância Gerenciada SQL do Azure, especialmente se você estiver acessando o Banco de Dados SQL do Azure ou a Instância Gerenciada do SQL do Azure de fora do mesmo datacenter do Microsoft Azure.
- As características multilocatárias do Banco de Dados SQL do Azure e da Instância Gerenciada SQL do Azure significam que a eficiência da camada de acesso a dados está correlacionada à escalabilidade geral do banco de dados. Em resposta ao uso excessivo de cotas predefinidas, o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure podem reduzir a taxa de transferência ou responder com exceções de limitação. Eficiências, como lotes, permitem que você faça mais trabalho antes de atingir esses limites.
- O processamento em lote também é eficaz para arquiteturas que usam vários bancos de dados (fragmentação). A eficiência de sua interação com cada unidade de banco de dados ainda é um fator-chave em sua escalabilidade geral.
Um dos benefícios de usar o Banco de Dados SQL do Azure ou a Instância Gerenciada SQL do Azure é que você não precisa gerenciar os servidores que hospedam o banco de dados. No entanto, essa infraestrutura gerenciada também significa que você precisa pensar de forma diferente sobre otimizações de banco de dados. Não é mais possível procurar melhorar o hardware do banco de dados ou a infraestrutura de rede. O Microsoft Azure controla esses ambientes. A área principal que você pode controlar é como seu aplicativo interage com o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure. O processamento em lote é uma dessas otimizações.
A primeira parte deste artigo examina várias técnicas de processamento em lote para aplicativos .NET que usam o Banco de Dados SQL do Azure ou a Instância Gerenciada SQL do Azure. As duas últimas seções abordam diretrizes e cenários de lotes.
Estratégias de processamento em lote
Observação sobre os resultados de tempo neste artigo
Nota
Os resultados não são parâmetros de referência, mas destinam-se a mostrar um desempenho relativo. Os tempos baseiam-se numa média de pelo menos 10 ensaios. As operações são inserções em uma tabela vazia. Esses testes foram medidos antes da V12 e não correspondem necessariamente à taxa de transferência que você pode experimentar em um banco de dados V12 usando as novas camadas de serviço DTU ou camadas de serviço vCore. O benefício relativo da técnica de loteamento deve ser semelhante.
Transações
Parece estranho começar uma revisão do batching discutindo transações. Mas o uso de transações do lado do cliente tem um efeito de lote sutil do lado do servidor que melhora o desempenho. E as transações podem ser adicionadas com apenas algumas linhas de código, para que elas forneçam uma maneira rápida de melhorar o desempenho de operações sequenciais.
Considere o seguinte código C# que contém uma sequência de operações de inserção e atualização em uma tabela simples.
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)");
O código ADO.NET a seguir executa sequencialmente essas operações.
using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
conn.Open();
foreach(string commandString in dbOperations)
{
SqlCommand cmd = new SqlCommand(commandString, conn);
cmd.ExecuteNonQuery();
}
}
A melhor maneira de otimizar esse código é implementar alguma forma de lote do lado do cliente dessas chamadas. Mas há uma maneira simples de aumentar o desempenho desse código simplesmente envolvendo a sequência de chamadas em uma transação. Aqui está o mesmo código que usa uma transação.
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();
}
Na verdade, as transações estão a ser utilizadas em ambos os exemplos. No primeiro exemplo, cada chamada individual é uma transação implícita. No segundo exemplo, uma transação explícita encapsula todas as chamadas. De acordo com a documentação do log de transações write-ahead, os registros de log são liberados no disco quando a transação é confirmada. Portanto, ao incluir mais chamadas em uma transação, a gravação no log de transações pode atrasar até que a transação seja confirmada. Na verdade, você está habilitando o envio em lote para as gravações no log de transações do servidor.
A tabela a seguir mostra alguns resultados de testes ad hoc. Os testes realizaram as mesmas inserções sequenciais com e sem transações. Para obter mais perspetivas, o primeiro conjunto de testes foi executado remotamente de um laptop para o banco de dados no Microsoft Azure. O segundo conjunto de testes foi executado a partir de um serviço de nuvem e banco de dados que residiam no mesmo datacenter do Microsoft Azure (oeste dos EUA). A tabela a seguir mostra a duração, em milissegundos, de inserções sequenciais com e sem transações.
Local para o Azure:
| Operações | Nenhuma transação (ms) | Transação (ms) |
|---|---|---|
| 1 | 130 | 402 |
| 10 | 1208 | 1226 |
| 100 | 12662 | 10395 |
| 1000 | 128852 | 102917 |
Azure para Azure (mesmo datacenter):
| Operações | Nenhuma transação (ms) | Transação (ms) |
|---|---|---|
| 1 | 21 | 26 |
| 10 | 220 | 56 |
| 100 | 2145 | 341 |
| 1000 | 21479 | 2756 |
Nota
Os resultados não são referências. Consulte a nota sobre os resultados de tempo neste artigo.
Com base nos resultados de testes anteriores, envolver uma única operação em uma transação realmente diminui o desempenho. Mas, à medida que você aumenta o número de operações em uma única transação, a melhoria de desempenho se torna mais acentuada. A diferença de desempenho também é mais percetível quando todas as operações ocorrem no datacenter do Microsoft Azure. O aumento da latência do uso do Banco de Dados SQL do Azure ou da Instância Gerenciada SQL do Azure de fora do datacenter do Microsoft Azure ofusca o ganho de desempenho do uso de transações.
Embora o uso de transações possa aumentar o desempenho, continue a observar as práticas recomendadas para transações e conexões. Mantenha a transação o mais curta possível e feche a conexão do banco de dados após a conclusão do trabalho. A instrução using no exemplo anterior garante que a conexão seja fechada quando o bloco de código subsequente for concluído.
O exemplo anterior demonstra que você pode adicionar uma transação local a qualquer código ADO.NET com duas linhas. As transações oferecem uma maneira rápida de melhorar o desempenho do código que faz operações sequenciais de inserção, atualização e exclusão. No entanto, para obter o desempenho mais rápido, considere alterar ainda mais o código para aproveitar o processamento em lote do lado do cliente, como parâmetros com valor de tabela.
Para obter mais informações sobre transações no ADO.NET, consulte Transações locais no ADO.NET.
Parâmetros de valor de tabela
Os parâmetros com valor de tabela oferecem suporte a tipos de tabela definidos pelo usuário como parâmetros em instruções Transact-SQL, procedimentos armazenados e funções. Essa técnica de processamento em lote do lado do cliente permite enviar várias linhas de dados dentro do parâmetro com valor de tabela. Para usar parâmetros com valor de tabela, primeiro defina um tipo de tabela. A instrução Transact-SQL a seguir cria um tipo de tabela chamado MyTableType.
CREATE TYPE MyTableType AS TABLE
( mytext TEXT,
num INT );
No código, você cria uma DataTable com exatamente os mesmos nomes e tipos do tipo de tabela. Passe esta DataTable em um parâmetro em uma consulta de texto ou chamada de procedimento armazenado. O exemplo a seguir mostra essa 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();
}
No exemplo anterior, o objeto SqlCommand insere linhas de um parâmetro com valor de tabela, @TestTvp. O objeto DataTable criado anteriormente é atribuído a esse parâmetro com o método SqlCommand.Parameters.Add. Agrupar as inserções em uma chamada aumenta significativamente o desempenho em relação às inserções sequenciais.
Para melhorar ainda mais o exemplo anterior, use um procedimento armazenado em vez de um comando baseado em texto. O comando Transact-SQL a seguir cria um procedimento armazenado que usa o parâmetro com valor de tabela SimpleTestTableType .
CREATE PROCEDURE [dbo].[sp_InsertRows]
@TestTvp as MyTableType READONLY
AS
BEGIN
INSERT INTO MyTable(mytext, num)
SELECT mytext, num FROM @TestTvp
END
GO
Em seguida, altere a declaração de objeto SqlCommand no exemplo de código anterior para o seguinte.
SqlCommand cmd = new SqlCommand("sp_InsertRows", connection);
cmd.CommandType = CommandType.StoredProcedure;
Na maioria dos casos, os parâmetros com valor de tabela têm desempenho equivalente ou melhor do que outras técnicas de loteamento. Os parâmetros com valor de tabela são muitas vezes preferíveis, porque são mais flexíveis do que outras opções. Por exemplo, outras técnicas, como cópia em massa SQL, só permitem a inserção de novas linhas. Mas com parâmetros com valor de tabela, você pode usar a lógica no procedimento armazenado para determinar quais linhas são atualizações e quais são inserções. O tipo de tabela também pode ser modificado para conter uma coluna "Operação" que indica se a linha especificada deve ser inserida, atualizada ou excluída.
A tabela a seguir mostra resultados de testes ad hoc para o uso de parâmetros com valor de tabela em milissegundos.
| Operações | Local para o Azure (ms) | Azure mesmo datacenter (ms) |
|---|---|---|
| 1 | 124 | 32 |
| 10 | 131 | 25 |
| 100 | 338 | 51 |
| 1000 | 2615 | 382 |
| 10000 | 23830 | 3586 |
Nota
Os resultados não são referências. Consulte a nota sobre os resultados de tempo neste artigo.
O ganho de desempenho do processamento em lote é imediatamente aparente. No teste sequencial anterior, 1000 operações levavam 129 segundos fora do datacenter e 21 segundos de dentro do datacenter. Mas com parâmetros com valor de tabela, 1000 operações levam apenas 2,6 segundos fora do datacenter e 0,4 segundos dentro do datacenter.
Para obter mais informações sobre parâmetros com valor de tabela, consulte Parâmetros com valor de tabela.
Cópia em massa do SQL
A cópia em massa do SQL é outra maneira de inserir grandes quantidades de dados em um banco de dados de destino. Aplicativos .NET podem usar a classe SqlBulkCopy para executar operações de inserção em massa. SqlBulkCopy é semelhante em função à ferramenta de linha de comando, Bcp.exe, ou a instrução Transact-SQL, BULK INSERT. O exemplo de código a seguir mostra como copiar em massa as linhas na fonte DataTable, tabela, para a tabela de destino, 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);
}
}
Há alguns casos em que a cópia em massa é preferida em relação aos parâmetros com valor de tabela. Consulte a tabela de comparação de parâmetros com valor de tabela versus operações BULK INSERT no artigo Parâmetros com valor de tabela.
Os seguintes resultados de teste ad hoc mostram o desempenho do processamento em lote com SqlBulkCopy em milissegundos.
| Operações | Local para o Azure (ms) | Azure mesmo datacenter (ms) |
|---|---|---|
| 1 | 433 | 57 |
| 10 | 441 | 32 |
| 100 | 636 | 53 |
| 1000 | 2535 | 341 |
| 10000 | 21605 | 2737 |
Nota
Os resultados não são referências. Consulte a nota sobre os resultados de tempo neste artigo.
Em tamanhos de lote menores, o uso de parâmetros com valor de tabela superou a classe SqlBulkCopy . No entanto, SqlBulkCopy executou 12-31% mais rápido do que os parâmetros com valor de tabela para os testes de 1.000 e 10.000 linhas. Como parâmetros com valor de tabela, SqlBulkCopy é uma boa opção para inserções em lote, especialmente quando comparado ao desempenho de operações sem lote.
Para obter mais informações sobre cópia em massa no ADO.NET, consulte Operações de cópia em massa.
Instruções INSERT parametrizadas de várias linhas
Uma alternativa para lotes pequenos é construir uma instrução INSERT parametrizada grande que insere várias linhas. O exemplo de código a seguir demonstra essa 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 exemplo destina-se a mostrar o conceito básico. Um cenário mais realista faria um loop pelas entidades necessárias para construir a cadeia de caracteres de consulta e os parâmetros de comando simultaneamente. Você está limitado a um total de 2100 parâmetros de consulta, portanto, isso limita o número total de linhas que podem ser processadas dessa maneira.
Os resultados do teste ad hoc a seguir mostram o desempenho desse tipo de instrução insert em milissegundos.
| Operações | Parâmetros com valor de tabela (ms) | Instrução única INSERT (ms) |
|---|---|---|
| 1 | 32 | 20 |
| 10 | 30 | 25 |
| 100 | 33 | 51 |
Nota
Os resultados não são referências. Consulte a nota sobre os resultados de tempo neste artigo.
Essa abordagem pode ser um pouco mais rápida para lotes com menos de 100 linhas. Embora a melhoria seja pequena, essa técnica é outra opção que pode funcionar bem em seu cenário de aplicativo específico.
DataAdapter
A classe DataAdapter permite modificar um objeto DataSet e, em seguida, enviar as alterações como operações INSERT, UPDATE e DELETE. Se você estiver usando o DataAdapter dessa maneira, é importante observar que chamadas separadas são feitas para cada operação distinta. Para melhorar o desempenho, use a propriedade UpdateBatchSize para o número de operações que devem ser enviadas em lote de cada vez. Para obter mais informações, consulte Executando operações em lote usando DataAdapters.
Entity Framework
O Entity Framework Core oferece suporte a lotes.
XML
Para completar, sentimos que é importante falar sobre XML como uma estratégia de lotes. No entanto, o uso de XML não tem vantagens sobre outros métodos e várias desvantagens. A abordagem é semelhante aos parâmetros com valor de tabela, mas um arquivo XML ou cadeia de caracteres é passado para um procedimento armazenado em vez de uma tabela definida pelo usuário. O procedimento armazenado analisa os comandos no procedimento armazenado.
Esta abordagem apresenta várias desvantagens:
- Trabalhar com XML pode ser complicado e propenso a erros.
- A análise do XML no banco de dados pode exigir muita CPU.
- Na maioria dos casos, esse método é mais lento do que os parâmetros com valor de tabela.
Por esses motivos, o uso de XML para consultas em lote não é recomendado.
Considerações sobre processamento em lote
As seções a seguir fornecem mais orientações para o uso de lotes no Banco de Dados SQL do Azure e nos aplicativos da Instância Gerenciada SQL do Azure.
Vantagens e desvantagens
Dependendo da sua arquitetura, o processamento em lote pode envolver uma compensação entre desempenho e resiliência. Por exemplo, considere o cenário em que sua função cai inesperadamente. Se você perder uma linha de dados, o impacto será menor do que o impacto da perda de um grande lote de linhas não enviadas. Há um risco maior quando você armazena linhas em buffer antes de enviá-las para o banco de dados em uma janela de tempo especificada.
Devido a essa compensação, avalie o tipo de operações em lote. Lote de forma mais agressiva (lotes maiores e janelas de tempo mais longas) com dados menos críticos.
Tamanho do lote
Em nossos testes, normalmente não havia vantagem em dividir grandes lotes em pedaços menores. Na verdade, essa subdivisão muitas vezes resultou em um desempenho mais lento do que o envio de um único lote grande. Por exemplo, considere um cenário em que você deseja inserir 1000 linhas. A tabela a seguir mostra quanto tempo leva para usar parâmetros com valor de tabela para inserir 1000 linhas quando divididas em lotes menores.
| Tamanho do lote | Iterações | Parâmetros com valor de tabela (ms) |
|---|---|---|
| 1000 | 1 | 347 |
| 500 | 2 | 355 |
| 100 | 10 | 465 |
| 50 | 20 | 630 |
Nota
Os resultados não são referências. Consulte a nota sobre os resultados de tempo neste artigo.
Você pode ver que o melhor desempenho para 1000 linhas é enviá-las todas de uma vez. Em outros testes (não mostrados aqui), houve um pequeno ganho de desempenho para quebrar um lote de 10000 linhas em dois lotes de 5000. Mas o esquema de tabela para esses testes é relativamente simples, portanto, você deve executar testes em seus dados específicos e tamanhos de lote para verificar essas descobertas.
Outro fator a considerar é que, se o lote total se tornar muito grande, o Banco de Dados SQL do Azure ou a Instância Gerenciada SQL do Azure poderá ser acelerado e se recusar a confirmar o lote. Para obter os melhores resultados, teste seu cenário específico para determinar se há um tamanho de lote ideal. Torne o tamanho do lote configurável em tempo de execução para permitir ajustes rápidos com base no desempenho ou erros.
Por fim, equilibre o tamanho do lote com os riscos associados ao loteamento. Se houver erros transitórios ou se a função falhar, considere as consequências de tentar novamente a operação ou de perder os dados no lote.
Processamento paralelo
E se você adotasse a abordagem de reduzir o tamanho do lote, mas usasse vários threads para executar o trabalho? Mais uma vez, nossos testes mostraram que vários lotes multithreaded menores normalmente tiveram um desempenho pior do que um único lote maior. O teste a seguir tenta inserir 1000 linhas em um ou mais lotes paralelos. Este teste mostra como lotes mais simultâneos realmente diminuíram o desempenho.
| Tamanho do lote [Iterações] | Dois tópicos (ms) | Quatro tópicos (ms) | Seis tópicos (ms) |
|---|---|---|---|
| 1000 [1] | 277 | 315 | 266 |
| 500 [2] | 548 | 278 | 256 |
| 250 [4] | 405 | 329 | 265 |
| 100 [10] | 488 | 439 | 391 |
Nota
Os resultados não são referências. Consulte a nota sobre os resultados de tempo neste artigo.
Existem várias razões potenciais para a degradação do desempenho devido ao paralelismo:
- Existem várias chamadas de rede simultâneas em vez de uma.
- Várias operações em uma única tabela podem resultar em contenção e bloqueio.
- Há despesas gerais associadas ao multithreading.
- A despesa de abrir várias conexões supera o benefício do processamento paralelo.
Se você segmentar tabelas ou bancos de dados diferentes, é possível ver algum ganho de desempenho com essa estratégia. A fragmentação de banco de dados ou federações seria um cenário para essa abordagem. O compartilhamento usa vários bancos de dados e roteia dados diferentes para cada banco de dados. Se cada pequeno lote estiver indo para um banco de dados diferente, executar as operações em paralelo pode ser mais eficiente. No entanto, o ganho de desempenho não é significativo o suficiente para ser usado como base para uma decisão de usar fragmentação de banco de dados em sua solução.
Em alguns projetos, a execução paralela de lotes menores pode resultar em melhor taxa de transferência de solicitações em um sistema sob carga. Neste caso, embora seja mais rápido processar um único lote maior, processar vários lotes em paralelo pode ser mais eficiente.
Se você usar a execução paralela, considere controlar o número máximo de threads de trabalho. Um número menor pode resultar em menos contenção e um tempo de execução mais rápido. Além disso, considere a carga adicional que isso coloca no banco de dados de destino tanto em conexões quanto em transações.
Fatores de desempenho relacionados
As orientações típicas sobre o desempenho do banco de dados também afetam o envio em lote. Por exemplo, o desempenho de inserção é reduzido para tabelas que têm uma chave primária grande ou muitos índices não clusterizados.
Se os parâmetros com valor de tabela usarem um procedimento armazenado, você poderá usar o comando SET NOCOUNT ON no início do procedimento. Esta instrução suprime o retorno da contagem das linhas afetadas no procedimento. No entanto, em nossos testes, o uso de SET NOCOUNT ON não teve efeito ou diminuiu o desempenho. O procedimento armazenado de teste foi simples com um único comando INSERT do parâmetro table-value. É possível que procedimentos armazenados mais complexos se beneficiem dessa declaração. Mas não assuma que adicionar SET NOCOUNT ON ao seu procedimento armazenado melhora automaticamente o desempenho. Para entender o efeito, teste seu procedimento armazenado com e sem a instrução SET NOCOUNT ON .
Cenários de processamento em lote
As seções a seguir descrevem como usar parâmetros com valor de tabela em três cenários de aplicativo. O primeiro cenário mostra como o buffer e o processamento em lote podem funcionar juntos. O segundo cenário melhora o desempenho executando operações mestre-detalhe em uma única chamada de procedimento armazenado. O cenário final mostra como usar parâmetros com valor de tabela em uma operação "UPSERT".
Colocação em buffer
Embora existam alguns cenários que são candidatos óbvios para o processamento em lote, há muitos cenários que poderiam tirar proveito do processamento em lote por atraso no processamento. No entanto, o atraso no tratamento também acarreta um maior risco de perda dos dados em caso de falha inesperada. É importante compreender este risco e considerar as consequências.
Por exemplo, considere um aplicativo Web que rastreia o histórico de navegação de cada usuário. Em cada solicitação de página, o aplicativo pode fazer uma chamada de banco de dados para registrar a exibição de página do usuário. Mas um maior desempenho e escalabilidade podem ser alcançados armazenando em buffer as atividades de navegação dos usuários e, em seguida, enviando esses dados para o banco de dados em lotes. Você pode acionar a atualização do banco de dados por tempo decorrido e/ou tamanho do buffer. Por exemplo, uma regra pode especificar que o lote deve ser processado após 20 segundos ou quando o buffer atingir 1000 itens.
O exemplo de código a seguir usa Reative Extensions - Rx para processar eventos em buffer gerados por uma classe de monitoramento. Quando o buffer é preenchido ou um tempo limite é atingido, o lote de dados do usuário é enviado para o banco de dados com um parâmetro com valor de tabela.
A classe NavHistoryData a seguir modela os detalhes de navegação do usuário. Ele contém informações básicas, como o identificador do usuário, a URL acessada e o tempo de acesso.
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; }
}
A classe NavHistoryDataMonitor é responsável por armazenar em buffer os dados de navegação do usuário no banco de dados. Ele contém um método, RecordUserNavigationEntry, que responde gerando um evento OnAdded . O código a seguir mostra a lógica do construtor que usa Rx para criar uma coleção observável com base no evento. Em seguida, ele assina essa coleção observável com o método Buffer. A sobrecarga especifica que o buffer deve ser enviado a cada 20 segundos ou 1000 entradas.
public NavHistoryDataMonitor()
{
var observableData =
Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");
observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
}
O manipulador converte todos os itens armazenados em buffer em um tipo com valor de tabela e, em seguida, passa esse tipo para um procedimento armazenado que processa o lote. O código a seguir mostra a definição completa para as classes NavHistoryDataEventArgs e 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);
}
O manipulador converte todos os itens armazenados em buffer em um tipo com valor de tabela e, em seguida, passa esse tipo para um procedimento armazenado que processa o lote. O código a seguir mostra a definição completa para as classes NavHistoryDataEventArgs e NavHistoryDataMonitor.
public class NavHistoryDataEventArgs : System.EventArgs
{
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 usar essa classe de buffer, o aplicativo cria um objeto NavHistoryDataMonitor estático. Cada vez que um usuário acessa uma página, o aplicativo chama o método NavHistoryDataMonitor.RecordUserNavigationEntry. A lógica de buffer continua a cuidar do envio dessas entradas para o banco de dados em lotes.
Detalhe mestre
Os parâmetros com valor de tabela são úteis para cenários INSERT simples. No entanto, pode ser mais desafiador inserir inserções em lote que envolvam mais de uma tabela. O cenário "mestre/detalhe" é um bom exemplo. A tabela mestra identifica a entidade primária. Uma ou mais tabelas detalhadas armazenam mais dados sobre a entidade. Nesse cenário, as relações de chave estrangeira impõem a relação de detalhes a uma entidade mestre exclusiva. Considere uma versão simplificada de uma tabela PurchaseOrder e sua tabela OrderDetail associada. O Transact-SQL a seguir cria a tabela PurchaseOrder com quatro colunas: OrderID, OrderDate, CustomerID e 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 encomenda contém uma ou mais compras de produtos. Essas informações são capturadas na tabela PurchaseOrderDetail. O Transact-SQL a seguir cria a tabela PurchaseOrderDetail com cinco colunas: OrderID, OrderDetailID, ProductID, UnitPrice e 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 ))
A coluna OrderID na tabela PurchaseOrderDetail deve fazer referência a um pedido da tabela PurchaseOrder. A seguinte definição de chave estrangeira impõe essa restrição.
ALTER TABLE [dbo].[PurchaseOrderDetail] WITH CHECK ADD
CONSTRAINT [FK_OrderID_PurchaseOrder] FOREIGN KEY([OrderID])
REFERENCES [dbo].[PurchaseOrder] ([OrderID])
Para usar parâmetros com valor de tabela, você deve ter um tipo de tabela definido pelo usuário para cada tabela 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
Em seguida, defina um procedimento armazenado que aceite tabelas desses tipos. Este procedimento permite que um aplicativo agrupe localmente um conjunto de pedidos e detalhes do pedido em uma única chamada. O Transact-SQL a seguir fornece a declaração de procedimento armazenado completa para este exemplo de ordem 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
Neste exemplo, a tabela definida @IdentityLink localmente armazena os valores reais de OrderID das linhas recém-inseridas. Esses identificadores de ordem são diferentes dos valores temporários de OrderID nos @orders parâmetros e @details com valor de tabela. Por esse motivo, a tabela conecta @IdentityLink os valores OrderID do parâmetro aos valores reais de @orders OrderID para as novas linhas na tabela PurchaseOrder. Após esta etapa, a tabela pode facilitar a inserção dos detalhes do pedido com o OrderID real que satisfaz a @IdentityLink restrição de chave estrangeira.
Este procedimento armazenado pode ser usado a partir de código ou de outras chamadas Transact-SQL. Consulte a seção de parâmetros com valor de tabela deste documento para obter um exemplo de código. O Transact-SQL a seguir mostra como chamar o 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 solução permite que cada lote use um conjunto de valores OrderID que começam em 1. Esses valores temporários de OrderID descrevem as relações no lote, mas os valores reais de OrderID são determinados no momento da operação de inserção. Você pode executar as mesmas instruções no exemplo anterior repetidamente e gerar ordens exclusivas no banco de dados. Por esse motivo, considere adicionar mais código ou lógica de banco de dados que impeça pedidos duplicados ao usar essa técnica de lote.
Este exemplo demonstra que operações de banco de dados ainda mais complexas, como operações mestre-detalhe, podem ser agrupadas em lote usando parâmetros com valor de tabela.
UPSERT
Outro cenário de envio em lote envolve a atualização simultânea de linhas existentes e a inserção de novas linhas. Esta operação é por vezes referida como uma operação "UPSERT" (update + insert). Em vez de fazer chamadas separadas para INSERT e UPDATE, a instrução MERGE pode ser uma substituição adequada. A instrução MERGE pode executar operações de inserção e atualização em uma única chamada. A mecânica de bloqueio da instrução MERGE funciona de forma diferente das instruções INSERT e UPDATE separadas. Teste suas cargas de trabalho específicas antes de implantar na produção.
Os parâmetros com valor de tabela podem ser usados com a instrução MERGE para executar atualizações e inserções. Por exemplo, considere uma tabela simplificada Employee que contenha as seguintes colunas: 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 ))
Neste exemplo, você pode usar o fato de que o SocialSecurityNumber é exclusivo para executar um MERGE de vários funcionários. Primeiro, crie o tipo de tabela definido pelo usuário:
CREATE TYPE EmployeeTableType AS TABLE
( Employee_ID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
SocialSecurityNumber NVARCHAR(50) );
GO
Em seguida, crie um procedimento armazenado ou escreva código que use a instrução MERGE para executar a atualização e inserir. O exemplo a seguir usa a instrução MERGE em um parâmetro com valor de tabela, , @employeesdo tipo EmployeeTableType. O conteúdo da @employees tabela não é mostrado aqui.
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 obter mais informações, consulte a documentação e os exemplos da instrução MERGE. Embora o mesmo trabalho possa ser executado em uma chamada de procedimento armazenado de várias etapas com operações INSERT e UPDATE separadas, a instrução MERGE é mais eficiente. O código do banco de dados também pode construir chamadas Transact-SQL que usam a instrução MERGE diretamente sem exigir duas chamadas de banco de dados para INSERT e UPDATE.
Resumo da recomendação
A lista a seguir fornece um resumo das recomendações de processamento em lote discutidas neste artigo:
- Use o buffer e o envio em lote para aumentar o desempenho e a escalabilidade do Banco de Dados SQL do Azure e dos aplicativos da Instância Gerenciada SQL do Azure.
- Entenda as compensações entre loteamento/buffering e resiliência. Durante uma falha de função, o risco de perder um lote não processado de dados críticos para os negócios pode superar o benefício de desempenho do processamento em lote.
- Tente manter todas as chamadas para o banco de dados em um único datacenter para reduzir a latência.
- Se você escolher uma única técnica de lote, os parâmetros com valor de tabela oferecem o melhor desempenho e flexibilidade.
- Para obter o desempenho de inserção mais rápido, siga estas diretrizes gerais, mas teste seu cenário:
- Para < 100 linhas, use um único comando INSERT parametrizado.
- Para < 1000 linhas, use parâmetros com valor de tabela.
- Para >= 1000 linhas, use SqlBulkCopy.
- Para operações de atualização e exclusão, use parâmetros com valor de tabela com lógica de procedimento armazenado que determina a operação correta em cada linha do parâmetro table.
- Diretrizes de tamanho de lote:
- Use os maiores tamanhos de lote que façam sentido para seus requisitos de aplicativos e negócios.
- Equilibre o ganho de desempenho de grandes lotes com os riscos de falhas temporárias ou catastróficas. Qual é a consequência de novas tentativas ou perda dos dados no lote?
- Teste o maior tamanho de lote para verificar se o Banco de Dados SQL do Azure ou a Instância Gerenciada SQL do Azure não o rejeita.
- Crie definições de configuração que controlem o envio em lote, como o tamanho do lote ou a janela de tempo de buffer. Essas configurações oferecem flexibilidade. Você pode alterar o comportamento de envio em lote na produção sem reimplantar o serviço de nuvem.
- Evite a execução paralela de lotes que operam em uma única tabela em um banco de dados. Se você optar por dividir um único lote em vários threads de trabalho, execute testes para determinar o número ideal de threads. Após um limite não especificado, mais threads diminuirão o desempenho em vez de aumentá-lo.
- Considere o buffer em tamanho e tempo como uma forma de implementar o processamento em lote para mais cenários.
Próximos passos
Este artigo concentrou-se em como o design de banco de dados e as técnicas de codificação relacionadas ao processamento em lote podem melhorar o desempenho e a escalabilidade do aplicativo. Mas este é apenas um fator na sua estratégia geral. Para obter mais maneiras de melhorar o desempenho e a escalabilidade, consulte Diretrizes de desempenho do banco de dados e Considerações sobre preço e desempenho para um pool elástico.
Comentários
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja: https://aka.ms/ContentUserFeedback.
Submeter e ver comentários