Início / dba / Perguntas / 177316
Accepted
Lucky
Asked: 2017-06-27 12:45:08 +0800 CST

Maneira mais eficiente de inserir dados na tabela MS SQL do OpenQuery no servidor vinculado ao DB2?

  • 772

depois de muita pesquisa e experimentação, imaginei que faria uma tentativa de obter alguns conselhos de especialistas.

Estou mantendo um procedimento armazenado que é executado como um trabalho. Ele cria uma tabela com dados bem definidos. Em seguida, ele usa um OpenQuery para coletar alguns dados e inseri-los na tabela.

Exemplo:

insert into LOCAL_TABLE 
select * 
from OPENQUERY(linked_server, 'select * from linktable')

O resultado da openquery é de 2,5 milhões de linhas. Isso leva cerca de 2 horas para ser concluído.

O servidor de banco de dados é um iSeries, então executei a consulta com o Visual Explain e a consulta é concluída em cerca de 1 segundo. Estou executando a consulta do MS SQL Server 2016. Ambos os servidores ficam próximos um do outro conectados por meio de um switch gigabit.

Estou tentando identificar o gargalo aqui e acredito que seja o INSERT.

Eu li sobre BULK INSERT e parece que não posso usar BULK INSERT com um OpenQuery.

Eu li sobre OPENROWSET(BULK... ), mas não acho que possa usar um servidor vinculado com isso. Tenho que manter usando o servidor vinculado para que caso o servidor mude o código não precise ser atualizado.

Finalmente, executar isso como SELECT * INTO pode aumentar o desempenho, mas os tipos de dados seriam definidos pelo conjunto de resultados. Suponho que poderia trabalhar com isso a partir daí, uma vez que seja local, mas gostaria de confirmar que vale a pena o esforço antes de embarcar nele.

Alguma recomendação de como posso melhorar o desempenho aqui?

sql-server db2

3 respostas

  1. Executei a consulta com o Visual Explain e a consulta é concluída em cerca de 1 segundo.

    Então provavelmente não buscou 2,5 milhões de linhas, o que provavelmente é seu gargalo.

    Estou tentando identificar o gargalo aqui e acredito que seja o INSERT.

    Então tente apenas correr

    select * 
from OPENQUERY(linked_server, 'select * from linktable')

    Para eliminar o INSERT.

    executar isso como SELECT * INTO pode aumentar o desempenho

    Teste isso também. E teste INSERT INTO com uma Temp Table.

    Usar um nome de 4 partes não deve melhorar o desempenho.

    • 3

  2. Em vez de OPENQUERY, tente usar apenas um nome de 4 partes...

    insert into local_table
  select * from linked_server.MYIBMI.MYLIB.MYTBL

    A única razão para usar OPENQUERY seria se houvesse uma WHEREcláusula no select. OPENQUERY envia a consulta para o IBM ie apenas recupera os resultados. Usando um nome de 4 partes, a tabela inteira é puxada de volta e executada localmente. Como você não tem um WHERE, não há problema em usar apenas o nome de 4 partes.

    Edit
    David está correto, pois o tempo VE provavelmente não é para todas as linhas. Por padrão, o VE otimiza para *FIRSTIO, ou seja, me dê a primeira página de dados o mais rápido possível; altere para *ALLIO e salve os resultados em um arquivo .CSV. Isso lhe dará uma ideia melhor de quanto tempo levaria para transferir todas as linhas.

    Depois de um pouco de atualização no MS SQL Server .... e encontrar este documento, The Data Loading Performance Guide for MS SQL 2008. Parece que seu método mais rápido com apenas MS SQL e DB2 para eu seria exportar os dados como CSV e, em seguida, use um dos métodos de inserção em massa disponíveis.

    Opcionalmente, você pode usar o MS SQL Server Integration Services (SSIS) ou outra ferramenta ETL, ou até mesmo escrever seu próprio aplicativo de carregamento em massa.

    • 1
  3. Best Answer

    Com base nos conselhos de @Charles e @David Browne - Microsoft , criei e testei quatro métodos de criação e preenchimento de uma tabela com os resultados de um DB2 OpenQuery.

    Como parte dos meus testes, não tentei exportar para CSV. Depois de ler o link de @Charles , parece que o SSIS do método OLE DB Destination seria o método mais rápido usando o Integration Services. Se alguém discordar, sinta-se à vontade para me corrigir.

    Para atualizar, meu objetivo é transferir 2,5 milhões de registros de um banco de dados DB2 OpenQuery e inserir os resultados em uma tabela no MS Sql Server. Meus benchmarks foram realizados em 3 de julho, um dia em que muitas pessoas na empresa tiraram um dia de folga, então espero que a carga do servidor não tenha muito efeito. Por fim, mencionarei que estou usando o método de log de transações bulk-logged para esse banco de dados.

    Resultados do meu teste de benchmark:

    1. INSERIR NA TABELA DO OPENQUERY Método: 00:52:00
    2. INSERIR NA TABELA COM (TABLOCK) DE OPENQUERY Método: 00:49:21
    3. SSIS do método de destino OLE DB : 00:39:34
    4. SELECT * INTO TABLE FROM OPENQUERY Método: 00:46:58

    Parece que usar o SSIS é o método mais rápido para transferência em massa do OpenQuery.

    • 1

relate perguntas