Inserir desempenho com a coluna Geografia

Depois de fazer algumas pesquisas, incluindo olhar para esta questão , parece que não é possível carregar em massa de forma eficiente qualquer um dos tipos baseados em CLR, incluindo geography. ¹

Você disse que adicionar apenas a geographycoluna adicionava uma quantidade significativa de tempo ao processo de carregamento -- isso pode, de fato, ser totalmente razoável, dependendo da quantidade de dados que entra nessa coluna.

O mais preocupante é que está demorando muito para carregar os dados sem a geographycoluna envolvida.

Ao fazer grandes cargas de dados, maximizar a taxa de transferência é a parte mais importante da otimização do processo, principalmente se o aplicativo cliente e o banco de dados estiverem separados por uma conexão de rede. (A propósito, vou presumir que usar um aplicativo cliente para carregar os dados é a melhor decisão de arquitetura para o problema aqui.)

Maximizar o throughput significa minimizar outros tipos de sobrecarga, como preparação de dados no cliente e viagens de ida e volta da rede entre os servidores.

Com base nos comentários, você tem algum tipo de loop acontecendo que lê uma linha da origem e a grava no destino imediatamente. Algo assim (pseudo-código):

while (more rows to read from source)
{
    read one row from source
    write one row to target
}

Isso é ineficiente principalmente porque maximiza as viagens de ida e volta da rede e pequenos blocos de E/S no destino, ambos caros em conjunto.

Provavelmente, a maneira mais fácil de modificar seu código para corrigir esse problema é introduzir um esquema de gravação atrasada, em que um bloco de linhas é lido da origem e liberado para o destino de uma só vez. Algo assim:

while (more rows to read from source)
{
    read 1..batchSize rows from source
    write 1..batchSize rows to target
}

Mesmo usando INSERT ... VALUESvárias linhas por vez (observação: há um limite de 1.000 linhas por instrução) melhorará significativamente o desempenho. O equipamento de teste que construí teve uma melhoria de 43 vezes entre um tamanho de lote de 1 e um tamanho de lote de 1.000. ²

Uma observação sobre parametrização. Embora essa seja uma prática recomendada quando se trata de segurança de aplicativos, para um processo de carregamento em lote, as instruções parametrizadas podem prejudicar o desempenho devido ao tempo de processamento associado. Se você começar a agrupar as coisas, ainda será possível parametrizar toda a instrução, mas o desempenho ficará cada vez pior quanto mais parâmetros você usar (há um limite de algo como 2.000 parâmetros de qualquer maneira). Supondo que os valores da fonte de dados sejam "seguros", eu realmente recomendaria fazer seu próprio escape (substituir ' por '') para as colunas de string e concatenar todos os valores diretamente no texto SQL como literais. A propósito, se você estiver usando .NET, certifique-se de usar um StringBuilderpara concatenar strings.

Sobre como mover a geographycoluna de forma eficiente. Simplesmente agrupar os INSERTs pode ser suficiente. Caso contrário, pode ser vantajoso tentar carregar (ou carregar em massa) a definição de texto da coluna (suponho que você tenha uma lista de pontos de texto ou algo das fontes de dados) junto com o restante dos dados em uma tabela de preparação e, em seguida, preencha o destino fazendo um INSERT/ SELECTpara converter a definição de texto no tipo CLR. (Nota: eu não testei isso.)

De qualquer forma, se você não atingiu a saturação da rede depois de fazer isso, e dependendo da rapidez com que pode ler da fonte de dados versus gravar no destino, o código pode ser refatorado em uma arquitetura de produtor/consumidor em que as leituras e gravações acontecem de forma assíncrona e possivelmente multithread em um ou em ambos os lados. Os detalhes de como fazer isso estão fora do escopo deste site, mas achei que deveria ser mencionado. Eu não acho que você terá que ir tão longe para obter um desempenho aceitável.

Por fim, para completar esta resposta, em termos de índices, para um carregamento único, é vantajoso criar índices não clusterizados depois que todos os dados forem carregados na tabela. Isso reduz o número de inserções de índice interno, da mesma forma que INSERTas instruções em lote reduzem as viagens de ida e volta da rede. No entanto, provavelmente é melhor criar o índice clusterizado antes de carregar, para que não haja necessidade de reescrever a tabela inteira para dar ordem a ela (supondo que a chave de clustering esteja sempre aumentando).

¹ Bem, provavelmente. Se você extrair Microsoft.SqlServer.Typesdo banco de dados de recursos e referenciá-lo em um assembly, isso deve ser possível; no entanto, isso não é suportado e provavelmente interromperá o processo terrivelmente se a Microsoft o atualizar entre as versões.

² Este foi um teste de thread único inserindo valores constantes de números inteiros e strings, não selecionando de uma fonte de dados; 1.500.000 linhas a 201 bytes/linha; usando INSERT ... VALUESlevou 771.971 ms para um tamanho de lote de 1 e 18.120 ms para um tamanho de lote de 1.000. Eu também testei os mesmos dados SqlBulkCopypara comparação e levou 16.576ms para um tamanho de lote de 1.000 e 13.829ms para um tamanho de lote de 10.000 (este tamanho de lote satura a taxa de transferência). Você pode baixar a fonte aqui -- adicione o programa a um aplicativo de console; Eu usei .NET 4.0.