Bloquear configurações com inserção em massa do SQL

Bloquear configurações com inserção em massa do SQL

The load consumed 7 seconds for 2.45 million rows during the SQL bulk insert.


Um desafio que podemos enfrentar ao usar a inserção em massa do SQL é se queremos permitir o acesso durante a operação ou impedir o acesso e como coordenamos isso com possíveis transações a seguir. Analisaremos o trabalho com algumas configurações dessa ferramenta e como podemos aplicá-las em OLAP, OLPT e ambientes mistos, nos quais podemos usar a flexibilidade da ferramenta para atender às nossas necessidades de importação de dados.

Considerações

O primeiro ponto que queremos considerar é se nosso ambiente deve bloquear transações usando inserções e carregamentos em massa do SQL ou se ainda devemos permitir acesso. Os ambientes OLAP e OLTP podem ter requisitos diferentes, pois o primeiro nos permite usar bloqueios mais do que o último, porque os ambientes OLTP tendem a ter um requisito “ativo”. Se tivermos uma programação em que devemos carregar dados durante um período antes de gerar relatórios, teremos mais flexibilidade para carregar dados com dicas que podem aumentar o desempenho. Além disso, para ambientes híbridos, podemos ter tabelas nas quais podemos usar essas dicas no nível da tabela ou mesmo durante a inserção real.

A próxima consideração é se queremos bloquear transações no nível da transação ou da tabela. Se usarmos uma tabela exclusivamente para uma carga em massa, podemos ter mais flexibilidade aqui. Se nossa tabela estiver envolvida em inserções em massa do SQL e em feeds de dados, onde dados frequentes são alimentados na tabela, podemos evitar bloqueios. Nos feeds de dados, também esperamos leituras frequentes para que os novos dados tornem os bloqueios um possível problema para relatórios ao vivo ou atrasados.

Experiências com configurações de carga

Veremos um exemplo carregando os dados de um arquivo em uma tabela e experimentando várias técnicas de bloqueio para a tabela. Em nosso exemplo, teremos um arquivo com mais de 2 milhões de linhas com a frase “A raposa marrom rápida saltou sobre os cães preguiçosos” começando na linha 2 e repetindo. A primeira linha (que pularemos em nossa inserção em massa SQL) terá “Dados” e podemos criar esse arquivo usando qualquer linguagem de script que permita um loop, ou podemos copiar e colar as linhas em lotes. Enquanto este exemplo usa essa frase completa, também podemos usar uma letra em cada linha ou uma palavra em cada linha.

Leia Também  ?????? ???????? ? ???, ?????????? ??? ??????????

Como alternativa, se você já possui um arquivo de texto personalizado grande para testar importações, poderá usá-lo se os mapeamentos envolverem uma coluna ou se você tiver uma tabela que tenha mapeamentos idênticos para a importação. Os resultados cronometrados das cargas mostradas nos exemplos abaixo podem diferir dependendo do sistema e dos dados que você possui.

Exemplo das cinco primeiras linhas do nosso arquivo de importação de teste que inseriremos em massa.

Exemplo das cinco primeiras linhas do nosso arquivo de importação de teste

Quando nosso arquivo for grande o suficiente (no meu exemplo, mais de 2 milhões de linhas), o SQL inserirá em massa os dados do arquivo em nossa tabela criada com uma coluna. Como mapeamos uma linha de dados para uma coluna, não especificamos um terminador de campo. A imagem abaixo mostra a seleção dos cinco primeiros resultados de nosso carregamento em massa na tabela que criamos. No código, incluo uma tabela suspensa que pode ser usada quando tudo estiver sendo testado.

Os cinco primeiros resultados de nossa pastilha a granel

Os cinco primeiros resultados de nossa inserção em massa SQL.

A carga consumiu 7 segundos por 2,45 milhões de linhas durante a inserção em massa.

A carga consumiu 7 segundos por 2,45 milhões de linhas.

Quando executei essa inserção em massa do SQL, o tempo de execução do início ao fim exigiu 7 segundos (dentro do intervalo de 5 a 10 segundos). Agora, executaremos a mesma inserção e adicionaremos uma especificação de TABLOCK para nossa operação de carregamento a granel – travando a mesa durante o carregamento:

O resultado disso é o tempo reduzido para inserir a mesma quantidade de dados.

Mesma transação com um bloqueio de tabela na inserção real, consumindo 4 segundos

A mesma transação com um bloqueio de tabela na inserção real, consumindo 4 segundos.

A vantagem de especificar um bloqueio de tabela no nível de carregamento em massa é que, se essa tabela fosse usada para um agendamento de relatórios e um feed, não estaríamos bloqueando a tabela em todas as cargas – apenas a inserção grande que exigiria um bloqueio de tabela , se essa é a especificação. Isso significa que, se tivéssemos 100 inserções em massa SQL na tabela ao longo do dia e 1 dessas cargas exigisse um aumento no desempenho, além de bloquear o acesso à tabela devido à natureza da carga, poderíamos usar o TABLOCK especificação para a carga 1, enquanto as outras 99 cargas não serão afetadas. Isso é útil nesses contextos mistos.

Leia Também  Aprenda a escrever consultas SQL básicas

De acordo com as observações da Microsoft sobre a especificação dessa opção, esse bloqueio ocorre apenas pelo tamanho da carga em massa real – em outras palavras, se tivéssemos sido transformados após isso na mesma transação, esse bloqueio não se aplicaria a essas transações adicionais. deseja especificar dicas de bloqueio para eles também, se esse foi o comportamento desejado). Da mesma forma, podemos carregar em massa simultaneamente a mesma tabela mesmo se esta opção, se especificada, desde que a tabela de destino da carga não possua índices (os índices columnstore são a exceção aqui).

E o cenário em que a tabela é apenas para um agendamento de relatórios em que qualquer inserção em massa do SQL deve ser bloqueada durante qualquer carregamento. Ainda poderíamos especificar o TABLOCK no código durante a inserção real ou no nível da transação, mas também podemos adicionar essa opção no nível da tabela. No código abaixo, configuramos o bloqueio no nível da tabela usando o procedimento da Microsoft sp_tableoption e execute uma verificação para garantir que esta opção foi salva com sucesso.

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

Os resultados de nossa consulta verificando a opção de bloqueio no carregamento em massa para a tabela específica.

Os resultados de nossa consulta verificando a opção de bloqueio no carregamento em massa para a tabela específica.

Agora, quando eu executo a transação de carregamento em massa com esta opção definida no nível da tabela a partir do código de execução acima e removendo o TABLOCK opção, recebo um tempo semelhante com o bloqueio definido no nível da tabela:

Leia Também  Um substituto confiável e flexível para sp_MSforeachdb - SQLBlog.org

Com um bloqueio no nível da tabela, vemos um resultado semelhante à opção TABLOCK especificada na inserção em massa

Com um bloqueio no nível da tabela, vemos um resultado semelhante à opção TABLOCK especificada no bolkinsert SQL

A vantagem aqui em contextos de desenvolvimento apropriados é que não precisaríamos especificar o TABLOCK opção em cada uma de nossas chamadas de inserção em massa SQL. Isso também significaria que durante as cargas, a tabela estaria bloqueada.

Como uma observação, para desativar esta opção na tabela, executaríamos a chamada abaixo para o procedimento armazenado da Microsoft sp_tableoption:

Pensamentos finais

  • Temos relatórios regulares com dados ativos ou relatórios agendados com inserções em massa do SQL? Podemos usar bloqueios em situações em que agendamos relatórios que devem ser concluídos por um período, enquanto que, se usarmos cargas em massa com dados ativos, eles podem exigir acesso consistente

  • Com algumas exceções, podemos achar mais apropriado bloquear uma tabela durante uma carga em massa na própria carga ou na transação se tivermos transformações que se seguem imediatamente e não desejarmos acesso concedido durante esse período

  • Enquanto analisamos a adição de bloqueios em uma tabela em situações em que não queremos que ninguém acesse a tabela enquanto inserimos dados em massa do SQL (aumentando o desempenho), também podemos aplicar medidas de desempenho adicionais, como remover índices ou eliminar e recriando a tabela, se não exigirmos a existência da tabela antes de carregar dados

Timothy Smith
Últimas mensagens de Timothy Smith (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br