Esta página foi útil?
Seus comentários sobre este conteúdo são importantes. Queremos saber sua opinião.
Comentários adicionais?
1500 caracteres restantes
Exportar (0) Imprimir
Expandir Tudo

Técnicas de envio em lote para aplicativos de Banco de dados SQL no Azure

Atualizado: janeiro de 2015

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

Revisores: Conor Cunningham, Michael Thomassy

O envio em lote de operações para o Banco de Dados SQL melhora significativamente o desempenho e a escalabilidade de serviços de nuvem no Microsoft Azure. Este documento demonstra primeiro a eficácia do envio em lote. Abrange as técnicas, os cenários e as considerações para ajudá-lo a usar o envio em lote com êxito em seus serviços de nuvem.

  1. Por que o envio em lote é importante para o Banco de dados SQL?

  2. Estratégias de envio em lote

    1. Transações

    2. Parâmetros com valor de tabela

    3. Cópia em massa do SQL

    4. Instruções INSERT com parâmetros de várias linhas

    5. DataAdapter

    6. Estrutura da entidade

    7. XML

  3. Considerações sobre o envio em lote

    1. Prós e contras

    2. Tamanho do lote

    3. processamento paralelo

    4. Fatores de desempenho relacionados

  4. Cenários de envio em lote

    1. Buffer

    2. Detalhe mestre

    3. UPSERT

  5. Recomendações

O envio em lote de chamadas para um serviço remoto é uma estratégia conhecida para aumentar o desempenho e a escalabilidade. Há menos custos de processamento fixos para todas as interações com um serviço remoto, como a serialização, a transferência de rede e a desserialização. O empacotamento de muitas transações separadas em um único lote minimiza esses custos.

Neste documento, queremos examinar várias estratégias e cenários de envio em lote do Banco de Dados SQL. Embora essas estratégias também sejam importantes para aplicativos locais que usam o SQL Server, há duas razões principais para realçar o uso do envio em lote para o Banco de Dados SQL:

  • Há uma latência de rede potencialmente maior potencial no acesso ao Banco de Dados SQL, especialmente se você estiver acessando o Banco de Dados SQL de fora do mesmo datacenter do Microsoft Azure.

  • As características multitenant do Banco de Dados SQL indicam que a eficiência da camada de acesso a dados está correlacionada à escalabilidade total do banco de dados. O Banco de Dados SQL precisa evitar que um locatário/usuário monopolize os recursos de banco de dados em detrimento de outros locatários. Em resposta ao uso além das cotas predefinidas, o Banco de Dados SQL pode reduzir a taxa de transferência ou responder com exceções de limitação. As eficiências, como o envio em lote, permitem a você trabalhar mais no Banco de Dados SQL antes de alcançar esses limites. O envio em lote também é efetivo para as arquiteturas que usam vários bancos de dados ou federações. A eficiência de sua interação com cada unidade de banco de dados ainda é um fator essencial em sua escalabilidade total.

Um dos benefícios de usar o Banco de Dados SQL é que você não precisa gerenciar os servidores que hospedam o banco de dados. Porém, essa infraestrutura gerenciada também significa que você precisa pensar de modo diferente sobre otimizações de banco de dados. Você não pode mais tentar melhorar a infraestrutura de hardware de banco de dados ou de rede. O Microsoft Azure controla os ambientes. A área principal que você pode controlar é o meio como seu aplicativo interage com o Banco de Dados SQL. O envio em lote é uma dessas otimizações.

A primeira parte do papel examina várias técnicas de envio em lote para aplicativos .NET que usam o Banco de Dados SQL. As duas últimas seções abordam diretrizes e cenários de envio em lote.

Parece estranho iniciar uma revisão do envio em lote discutindo transações. No entanto, o uso de transações do lado do cliente tem um efeito sutil de envio em lote do lado do servidor que melhora o desempenho. E as transações podem ser adicionadas com apenas algumas linhas de código, de modo que fornecem uma maneira rápida de melhorar o desempenho de operações sequenciais.

Considere a seguinte sequência de inserção e as operações de 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 essas operações em sequência.

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 um formulário de envio em lote do lado do cliente dessas chamadas. Porém, há uma maneira simples de aumentar o desempenho do código simplesmente encapsulando 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();
}

As transações estão sendo realmente usadas 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 para o disco quando a transação é confirmada. Assim, com a inclusão de mais chamadas em uma transação, a gravação no log de transações pode atrasar até que a transação seja confirmada. De fato, 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 teste ad hoc. Os testes executaram as mesmas inserções sequenciais com e sem transações. Para obter mais perspectiva, 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 de um serviço de nuvem e banco de dados que residiam no mesmo datacenter do Microsoft Azure (West US). A tabela a seguir mostra a duração em milissegundos de inserções sequenciais com e sem transações.

 

Operações No local para Microsoft Azure Microsoft Azure (mesmo datacenter)

Nenhuma transação (ms)

Transação (ms)

Nenhuma transação (ms)

Transação (ms)

1

130

402

21

26

10

1208

1226

220

56

100

12662

10395

2145

341

1000

128852

102917

21479

2756

noteObservação
Os resultados não são parâmetros de comparação, mas devem mostrar o desempenho relativo. Os intervalos se baseiam em uma média de pelo menos 10 execuções de teste. As operações são inserções em uma tabela vazia.

O encapsulamento de uma única operação em uma transação na verdade diminui o desempenho. Porém, à medida que você aumenta o número de operações em uma única transação, a melhoria de desempenho fica mais evidente. A diferença de desempenho também é mais perceptível quando todas as operações ocorrem no datacenter do Microsoft Azure. A latência aumentada do uso do Banco de Dados SQL 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 seguindo 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 depois que o trabalho for concluído. A instrução de uso no exemplo anterior assegura que a conexão seja fechada quando o bloco subsequente de código termina.

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 um modo rápido de melhorar o desempenho de código que executa as operações de inserção sequencial, atualização e exclusão. No entanto, para obter o desempenho mais rápido, considere alterar o código ainda mais para tirar proveito do envio 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 (ADO.NET).

Os parâmetros com valor de tabela oferecem suporte a tipos de tabela definidos pelo usuário como parâmetros em instruções, procedimentos armazenados e funções de Transact-SQL. Essa técnica de envio em lote do lado do cliente permite enviar várias linhas de dados no 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 denominada 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 essa DataTable em um parâmetro em uma consulta de texto ou em uma chamada de procedimento armazenado. O seguinte exemplo 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 criado anteriormente com base no objeto DataTable é atribuído a esse parâmetro com método SqlCommand.Parameters.Add. O envio em lote de inserções em uma chamada aumenta significativamente o desempenho em relação às inserções sequenciais.

Para aprimorar 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

Altere a declaração do objeto SqlCommand no exemplo de código anterior para a 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 o desempenho equivalente ou melhor do que outras técnicas de envio em lote. Com frequência, os parâmetros com valor de tabela são preferíveis, pois são mais flexíveis do que as outras opções. Por exemplo, outras técnicas, como cópia em massa do SQL, permitem apenas a inserção de linhas novas. No entanto, 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 os resultados do teste ad hoc para o uso de parâmetros com valor de tabela em milissegundos.

 

Operações No local para Microsoft Azure (ms) Microsoft Azure, mesmo datacenter (ms)

1

124

32

10

131

25

100

338

51

1000

2615

382

10000

23830

3586

noteObservação
Os resultados não são parâmetros de comparação, mas devem mostrar o desempenho relativo. Esses testes usam um procedimento armazenado semelhante ao exemplo nesta seção. Os intervalos se baseiam em uma média de pelo menos 10 execuções de teste. As operações são inserções em uma tabela vazia.

O ganho de desempenho do envio em lote é imediatamente evidente. No teste sequencial anterior, 1000 operações levaram 129 segundos fora do datacenter e 21 segundos dentro do datacenter. Porém, com parâmetros com valor de tabela, 1000 operações levam somente 2,6 segundos fora do datacenter e 0,4 segundos no datacenter.

Para obter mais informações sobre parâmetros com valor de tabela, consulte Parâmetros com valor de tabela (mecanismo de banco de dados).

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 à função para a ferramenta de linha de comando, Bcp.exe, ou à instrução Transact-SQL, BULK INSERT. O exemplo de código a seguir mostra como copiar em massa as linhas na DataTable de origem, table, para a tabela de destino no 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);
    }
}

Há alguns casos em que a cópia em massa é preferível 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 tópico Parâmetros com valor de tabela (mecanismo de banco de dados).

Os seguintes resultados do teste ad hoc mostram o desempenho do envio em lote com SqlBulkCopy em milissegundos.

 

Operações No local para Microsoft Azure (ms) Microsoft Azure, mesmo datacenter (ms)

1

433

57

10

441

32

100

636

53

1000

2535

341

10000

21605

2737

noteObservação
Os resultados não são parâmetros de comparação, mas devem mostrar o desempenho relativo. Os intervalos se baseiam em uma média de pelo menos 10 execuções de teste. As operações são inserções em uma tabela vazia.

Em tamanhos de lote menores, os parâmetros com valor de tabela superaram a classe SqlBulkCopy. No entanto, SqlBulkCopy foi executado 12 a 31% mais rapidamente do que os parâmetros com valor de tabela para testes de 1.000 e 10.000 linhas. Assim como os parâmetros com valor de tabela, SqlBulkCopy é uma boa opção para inserções em lotes, especialmente quando comparado ao desempenho de operações não em lotes.

Para obter mais informações sobre a cópia em massa no ADO.NET, consulte Operações de cópia em massa no SQL Server.

Uma alternativa para lotes pequenos é criar uma grande instrução INSERT com parâmetros 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 visa mostrar o conceito básico. Um cenário mais realista percorreria as entidades necessárias para criar 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 forma.

Os seguintes resultados do teste ad hoc mostram o desempenho desse tipo de instrução de inserção em milissegundos.

 

Operações Parâmetros com valor de tabela (ms) Instrução INSERT única (ms)

1

32

20

10

30

25

100

33

51

noteObservação
Os resultados não são parâmetros de comparação. Os intervalos se baseiam em uma média de pelo menos 30 execuções de teste. As operações são inserções em uma tabela vazia.

Essa abordagem pode ser ligeiramente 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 específico do aplicativo.

A classe DataAdapter permite que você modifique um objeto DataSet e envie as alterações como operações INSERT, UPDATE e DELETE. Se você estiver usando DataAdapter assim, é importante observar que as 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 lotes usando DataAdapters.

O Entity Framework não oferece suporte ao envio em lote no momento. Os desenvolvedores diferentes na comunidade tentaram demonstrar soluções alternativas, como a substituição do método SaveChanges. Mas as soluções são normalmente complexas e personalizadas para o aplicativo e o modelo de dados. O projeto do codeplex Entity Framework tem uma página de discussão sobre essa solicitação de recurso. Para exibir essa discussão, consulte Design Meeting Notes – August 2, 2012.

Por questões de integridade, achamos que é importante falar sobre XML como uma estratégia para envio em lote. No entanto, o uso de XML não tem nenhuma vantagem sobre outros métodos e tem várias desvantagens. A abordagem é semelhante aos parâmetros com valor de tabela, mas um arquivo XML ou uma cadeia de caracteres é passada a um procedimento armazenado, em vez de uma tabela definida pelo usuário. O procedimento armazenado analisa os comandos no procedimento armazenado.

Essa abordagem tem várias desvantagens:

  1. O trabalho com XML pode ser incômodo e propenso a erros.

  2. A análise do XML no banco de dados pode consumir muitos recursos de CPU.

  3. Na maioria dos casos, esse método é mais lento do que os parâmetros com valor de tabela.

Pelos motivos a seguir, o uso do XML para consultas de lote não é recomendado.

As seções a seguir fornecem mais diretrizes sobre o uso do envio em lote em aplicativos Banco de Dados SQL.

Dependendo da arquitetura, o envio em lote pode envolver uma compensação entre o desempenho e a resiliência. Por exemplo, considere o cenário em que a função fica ativa inesperadamente. Se você perder uma linha de dados, o impacto será menor que o impacto da perda de um lote grande de linhas não enviadas. Há um risco maior ao armazenar as linhas em buffer antes de enviá-las ao banco de dados em uma janela de tempo especificada.

Devido a essa compensação, avalie o tipo de operações que você envia em lote. Realize o envio em lote mais agressivo (lotes maiores e janelas de tempo mais longas) com dados que sejam menos críticos.

Em nossos testes, geralmente não houve nenhuma vantagem em dividir grandes lotes em partes menores. De fato, essa subdivisão resultou frequentemente em desempenho mais lento do que o envio de um único lote maior. Por exemplo, considere um cenário em que você deseja inserir 1000 linhas. A tabela a seguir mostra o tempo necessário 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

noteObservação
Os resultados não são parâmetros de comparação. Os testes locais são executados no laptop em uma rede sem fio. Os intervalos se baseiam em uma média de pelo menos 10 execuções de teste. As operações são inserções em uma tabela vazia.

Você pode ver que o melhor desempenho para 1000 linhas é enviá-las de uma vez. Em outros testes (não mostrados aqui), houve um ganho de desempenho pequeno para desmontar uma fileira de um lote de 10000 em dois lotes de 5000. Mas o esquema da tabela destes testes é relativamente simples, portanto, você deve executar testes nos seus dados específicos e tamanhos de lotes para confirmar estes resultados.

Outro fator a considerar é que se o lote total ficar muito grande, o Banco de Dados SQL poderá limitar e se recusar a confirmar o lote. Para obter os melhores resultados, teste seu cenário específico para determinar se existe 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 nos erros.

Finalmente, equilibre o tamanho do lote com os riscos associados ao envio em lote. Se ocorrerem erros transitórios ou se a função falhar, considere as consequências de repetir a operação ou perder os dados no lote.

E se você adotasse a abordagem de reduzir o tamanho do lote, mas usou vários threads para executar o trabalho? Novamente, nossos testes mostraram que vários lotes menores de vários threads geralmente tiveram um desempenho pior do que um único lote maior. O teste a seguir tenta inserir 1000 linhas em um ou mais lotes paralelos. Esse teste mostra como mais lotes simultâneos diminuíram realmente o desempenho.

 

Tamanho do lote [iterações] Dois threads (ms) Quatro threads (ms) Seis threads (ms)

1000 [1]

277

315

266

500 [2]

548

278

256

250 [4]

405

329

265

100 [10]

488

439

391

noteObservação
Os resultados não são parâmetros de comparação. Os intervalos se baseiam em uma média de pelo menos 10 execuções de teste. As operações são inserções em uma tabela vazia. Os testes de processamento paralelo usaram a Biblioteca paralela de tarefas do .NET.

Há várias razões possíveis para a degradação no desempenho devido ao paralelismo:

  1. Há várias chamadas de rede simultâneas, em vez de uma.

  2. Várias operações em uma única tabela podem levar à contenção e ao bloqueio.

  3. Há sobrecargas associadas ao multithreading.

  4. A despesa de abrir várias conexões supera o benefício do processamento paralelo.

Se você tiver como destino tabelas ou bancos de dados diferentes, poderá observar algum ganho de desempenho com essa estratégia. A fragmentação ou federações de banco de dados seriam um cenário para essa abordagem. A fragmentação usa vários bancos de dados e dados de rotas diferentes para cada banco de dados. Se cada lote pequeno for para um banco de dados diferente, executar as operações em paralelo poderá ser um processo mais eficiente. Porém, o ganho de desempenho não é significativo o suficiente para ser usado como a base para uma decisão de utilizar a fragmentação de banco de dados na sua solução.

Em alguns projetos, a execução paralela de lotes menores pode resultar na produção aprimorada de solicitações em um sistema sob carga. Nesse caso, mesmo que seja mais rápido processar um único lote maior, processar vários lotes em paralelo poderá 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 poderia resultar em menos contenção e em um tempo de execução mais rápido. Além disso, considere a carga adicional que esse local no banco de dados de destino nas conexões e nas transações.

As diretrizes típicas sobre o desempenho de banco de dados também afetam o envio em lote. Por exemplo, o desempenho de inserção é reduzido para tabelas que têm uma grande chave primária ou vários í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. Essa instrução suprime o retorno da contagem de linhas afetadas no procedimento. No entanto, em nossos teste, o uso de SET NOCOUNT ON não teve nenhum efeito ou diminuiu o desempenho. O procedimento armazenado de teste foi simples com um único comando INSERT a partir do parâmetro com valor de tabela. É possível que procedimentos armazenados mais complexos se beneficiem com essa instrução. Mas não suponha que a adição de SET NOCOUNT ON ao procedimento armazenado melhora automaticamente o desempenho. Para entender o efeito, teste o procedimento armazenado com e sem a instrução SET NOCOUNT ON.

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 armazenamento em buffer e o envio em lote podem trabalhar juntos. O segundo cenário melhora o desempenho executando operações mestre-detalhes 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”.

Embora haja alguns cenários que são candidatos óbvios ao envio em lote, há muitos cenários que poderiam se beneficiar com o envio em lote atrasando o processamento. No entanto, o processamento atrasado também representa um risco maior de que os dados sejam perdidos no caso de uma falha inesperada. É importante entender esse risco e considerar as consequências.

Por exemplo, considere um aplicativo Web que controla o histórico de navegação de cada usuário. Em cada solicitação de página, o aplicativo poderia fazer uma chamada de banco de dados para registrar a exibição da página do usuário. Porém, o desempenho e a escalabilidade mais altos podem ser obtidos com o armazenamento em buffer das atividades de navegação dos usuários e o envio desses dados ao banco de dados em lotes. Você pode disparar a atualização do banco de dados por tempo decorrido e/ou tamanho do buffer. Por exemplo, uma regra poderia especificar que o lote deve ser processado depois de 20 segundos ou quando o buffer atingir 1000 itens.

O exemplo de código a seguir usa Extensões reativas (Rx) para processar os eventos em buffer gerados por uma classe de monitoramento. Quando o buffer é preenchido ou um tempo limite é atingido, o lote de dados de usuário é enviado ao banco de dados com um parâmetro com valor de tabela.

A classe NavHistoryData a seguir modela os detalhes da navegação do usuário. Ela contém informações básicas, como o identificador do usuário, a URL acessada e a hora do 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 pelo armazenamento em buffer dos dados de navegação do usuário no banco de dados. Ela 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, 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 em buffer em um tipo com valor de tabela e 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);           
    }
    public void RecordUserNavigationEntry(NavHistoryData data)
    {    
        if (OnAdded != null)
            OnAdded(this, new NavHistoryDataEventArgs(data));
    }
    protected void Handler(IList<EventPattern<NavHistoryDataEventArgs>> items)
    {
        DataTable navHistoryBatch = new DataTable("NavigationHistoryBatch");
        navHistoryBatch.Columns.Add("UserId", typeof(int));
        navHistoryBatch.Columns.Add("URL", typeof(string));
        navHistoryBatch.Columns.Add("AccessTime", typeof(DateTime));
        foreach (EventPattern<NavHistoryDataEventArgs> item in items)
        {
            NavHistoryData data = item.EventArgs.Data;
            navHistoryBatch.Rows.Add(data.UserId, data.URL, data.AccessTime);
        }
        using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand("sp_RecordUserNavigation", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(
                new SqlParameter()
                {
                    ParameterName = "@NavHistoryBatch",
                    SqlDbType = SqlDbType.Structured,
                    TypeName = "NavigationHistoryTableType",
                    Value = navHistoryBatch,
                });
            cmd.ExecuteNonQuery();
        }
    }
}

Para usar essa classe de buffer, o aplicativo cria um objeto NavHistoryDataMonitor estático. Cada vez que o usuário acessa uma página, o aplicativo chama o método NavHistoryDataMonitor.RecordUserNavigationEntry. A lógica do buffer continua cuidando do envio dessa entradas para o banco de dados em lotes.

Os parâmetros com valor de tabela são úteis para cenários de INSERT simples. No entanto, pode ser mais desafiador executar inserções em lotes que envolvem mais de uma tabela. O cenário “mestre/detalhes” é um bom exemplo. A tabela mestra identifica a entidade principal. Uma ou mais tabelas de detalhes 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 mestra exclusiva. Considere uma versão simplificada de uma tabela PurchaseOrder e da tabela associada OrderDetail. A 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 pedido contém uma ou mais compras de produtos. Essas informações são capturadas na tabela PurchaseOrderDetail. A 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 uma ordem da tabela PurchaseOrder. A definição a seguir de uma chave estrangeira impõe essa restrição.

ALTER TABLE [dbo].[PurchaseOrderDetail2]  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 envie em lote 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 esse 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 localmente @IdentityLink armazena os valores reais de OrderID das linhas recentemente inseridas. Esses identificadores de pedido são diferentes dos valores temporários de OrderID nos parâmetros com valor de tabela @orders e @details. Por isso, a tabela @IdentityLink conecta os valores OrderID do parâmetro @orders aos valores reais OrderID para as novas linhas na tabela PurchaseOrder. Depois dessa etapa, a tabela @IdentityLink pode facilitar a inserção dos detalhes do pedido com OrderID real que satisfaz a restrição de chave estrangeira.

Esse procedimento armazenado pode ser usado no código ou de outras chamadas Transact-SQL. Consulte a seção parâmetros com valor de tabela deste documento para obter um exemplo de código. O Transact-SQL a seguir mostra como chamar 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 OrderID valores que começam com 1. Estes valores temporários OrderID descrevem a relação no lote, mas os valores atuaisOrderIDsão determinados no momento da inserção da operação. Você pode executar as mesmas instruções no exemplo anterior repetidamente e gerar pedidos exclusivos no banco de dados. Por isso, considere adicionar mais lógica de código ou de banco de dados que impeça pedidos duplicados ao usar esta técnica para o envio em lote.

Este exemplo demonstra que até mesmo as operações de banco de dados mais complexas, como operações de mestre- detalhes, podem ser enviadas em lote com parâmetros com valor de tabela.

Outro cenário de envio em lote envolve atualizar as linhas existentes e inserir novas linhas simultaneamente. Às vezes, essa operação é conhecida como “UPSERT” (atualização + inserção). Em vez de fazer chamadas separadas para INSERT e UPDATE, a instrução MERGE é mais adequada para essa tarefa. A instrução MERGE pode executar as operações de inserção e atualização em uma única chamada.

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 contém as colunas a seguir: 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 SocialSecurityNumber é exclusivo para executar uma instrução 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 a inserção. O exemplo a seguir usa a instrução MERGE em um parâmetro com valor de tabela, @employees, do tipo EmployeeTableType. O conteúdo da tabela @employees 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 criar as chamadas Transact-SQL que usam a instrução MERGE diretamente sem exigir duas chamadas de banco de dados para INSERT e UPDATE.

  • Use i buffer e o envio em lote para aprimorar o desempenho e a escalabilidade de aplicativos do Banco de Dados SQL.

  • Entenda as compensações entre o envio em lote/buffer e a resiliência. Durante a falha de uma função, o risco de perder um lote não processado de dados críticos da empresa pode superar o benefício do envio em lote.

  • Tente manter todas as chamadas ao banco de dados de um único datacenter para reduzir a latência.

  • Se você escolher uma única técnica de envio em lote, os parâmetros com valor de tabela oferecem o melhor desempenho e flexibilidade.

  • Para obter o desempenho mais rápido de inserção, siga estas diretrizes gerais mas testar seu cenário:

  • Para operações de atualização e exclusão, use parâmetros com valor de tabela com a lógica do procedimento armazenado que determina a operação correta em cada linha do parâmetro de tabela.

  • Diretrizes de tamanho do lote:

    • Use os tamanhos de lote maiores que fizerem sentido para seus requisitos de aplicativo e negócios.

    • Equilibre o ganho de desempenho de lotes grandes com os riscos de falhas catastróficas ou temporárias. Qual é a consequência de repetições ou de perda de dados no lote?

    • Teste o tamanho do lote maior para verificar se o Banco de Dados SQL não o recusa.

    • Crie parâmetros de configuração que controlam o envio em lote, como tamanho do lote ou janela de tempo de buffer. Essas configurações fornecem flexibilidade. Você pode alterar o comportamento do 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, faça testes para determinar o número ideal de threads. Depois de um limite não for especificado, mais threads diminuirão o desempenho, em vez de aumentá-lo.

  • Considere o buffer por tamanho e tempo como uma maneira de implementar o envio em lote para mais cenários.

Consulte também

A Microsoft está realizando uma pesquisa online para saber sua opinião sobre o site do MSDN. Se você optar por participar, a pesquisa online lhe será apresentada quando você sair do site do MSDN.

Deseja participar?
Mostrar:
© 2015 Microsoft