Eu tenho um banco de dados onde carrego arquivos em uma tabela de teste, dessa tabela de teste tenho 1-2 junções para resolver algumas chaves estrangeiras e depois insiro essas linhas na tabela final (que tem uma partição por mês). Eu tenho cerca de 3,4 bilhões de linhas para três meses de dados.
Qual é a maneira mais rápida de obter essas linhas do teste para a mesa final? Tarefa de fluxo de dados do SSIS (que usa uma exibição como origem e tem carregamento rápido ativo) ou um comando Insert INTO SELECT .... ? Eu tentei a tarefa de fluxo de dados e posso obter cerca de 1 bilhão de linhas em cerca de 5 horas (8 núcleos / 192 GB de RAM no servidor), o que parece muito lento para mim.
Uma abordagem comum:
INSERT dbo.[Target] WITH (TABLOCKX) SELECT ...nlinhas, o que pode reduzir a pressão no log de transações e, é claro, significa que, se algum lote falhar, você só precisará iniciar a partir desse lote. Eu escrevi sobre isso (em referência a exclusões, os mesmos conceitos básicos se aplicam) aqui: http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletesSe suas partições são físicas e não apenas lógicas, você pode ganhar algum tempo fazendo com que diferentes processos preencham diferentes partições simultaneamente (é claro que isso significa que você não pode usar
TABLOCK/TABLOCKX). Isso pressupõe que a origem também seja adequada para vários processos selecionando sem sobreposição / bloqueio etc., e tornando esse lado da operação ainda mais lento (dica: crie um índice clusterizado na origem que se adapte ao esquema de particionamento no destino).Você também pode considerar algo muito mais primitivo, como
BCP OUT/BCP IN.Eu não sei se eu pularia para o SSIS para ajudar com isso. Provavelmente há algumas eficiências aí, mas não sei se o esforço justifica a economia.
Olhando para o seu problema de uma perspectiva do SSIS, sinto que a razão pela qual isso pode ter demorado tanto é que você não tinha lotes. Isso pode levar a muitas linhas preenchendo o pipeline do SSIS e prejudicar o desempenho do SSIS como resultado. O que você precisa fazer é alterar suas linhas por configuração de lote e possivelmente seu tamanho máximo de confirmação de inserção. Agora, o que você define também dependerá da quantidade de memória disponível para o seu servidor SSIS? Qual é a velocidade do disco da sua instância do SQL Server? A melhor maneira de fazer isso é testar. Vamos, por exemplo, usar 10.000. Isso enviará um lote para o servidor 10.000 por vez, evitando que seu pipeline fique sobrecarregado e ajudará a executar esse processo mais rapidamente. Essas configurações são definidas em seu destino OLEDB.
Se for um problema, você também pode adicionar uma tarefa de execução SQL antes e depois para fazer como @AaronBertrand sugere e remover/re adicionar quaisquer índices ou restrições à tabela.