locked
Melhora de Performance RRS feed

  • Pergunta

  • Olá pessoal,

    Tenho uma consulta com a seguinte estrutura:

    UPDATE tabelaA SET ID = tabelaB.ID
    FROM tabelaA, tabelaB
    WHERE tabelaA.codigo = tabelaB.codigo
    AND tabelaA.ID = tabelaB.ID2

    na tabelaA tenho 54 milhões de registros

    na tabelaB tenho 650.000 registros

    Já criei um índice para a FK entre as tabelas, desabilitei as FKs, testei outros indices mas nada que tentei melhorou performance. O tempo de execução está em torno de 15 horas e o comando não terminou.

    O que posso fazer para otimizar este comando?

    Abraço.


    Marco Antônio Pinheiro / MCTS - MCC http://marcoantoniopinheiro.blogspot.com

    • Movido Roberson Ferreira _ segunda-feira, 22 de outubro de 2012 15:44 (De:SQL Server - Desenvolvimento Geral)
    domingo, 21 de outubro de 2012 21:27

Respostas

  • Boa noite,

    Não sei sinceramente como vai ficar a performance, mas experimente utilizar a sintaxe com Join:

    UPDATE tabelaA SET ID = tabelaB.ID
    FROM tabelaA
    JOIN tabelaB
        ON tabelaA.codigo = tabelaB.codigo
        AND tabelaA.ID = tabelaB.ID2
    

    Espero que seja útil.


    Assinatura: http://www.imoveisemexposicao.com.br

    domingo, 21 de outubro de 2012 22:29
  • Marco,

    Verifique as configurações dos seus arquivos físicos (ldf, mdf e ndf), um autogrow em intervalos pequenos podem onerar os recursos da máquina. Uma opção para melhoria tbm é alocar a sua tabela com maior número de registros em um .ndf (isolado dos demais fisicamente).

    Abs,

    Fábio Battestin

    segunda-feira, 22 de outubro de 2012 11:56
  • Marco,

    Vamos por partes, diversos fatores podem atrapalhar:

    1 - Não pense que criar diversos índices poderá ajudar, muito pelo contrário isso poderá criar situações que impactam diretamente na forma de armazenamento e pesquisa dos dados.

    2 - Sempre que estamos fazendo um processo de manipulação de dados, temos que tem em mente que diversos processos estão sendo realizados e isso também poderá impactar em questões de bloqueios e até mesmo travamentos de transações.

    3 - Normalmente quando estamos utilizando conjuntos de tabelas que envolvem diversas linhas temos que pensar em como podemos filtrar os dados que queremos manipular, neste caso, sua claúsula Where esta apresentando 2 condições será que realmente elas são necessárias?

    4 - Situações de fragmentação de dados, índices desatualizadas, página de dados internas mal alocadas, páginas de dados internas desatualizadas, estatísticas de banco de dados e índices desnecessários podem forçar o plano de execução a mudar totalmente o seu caminho de processamento.

    5 - Procure sempre analisar o plano de execução que o SQL Server esta gerando para realizar este tipo de procedimento, analisando e identificando qual operador esta apresentando maior custo de processamento.

    6 - Por questões de boas práticas, utilizar os operadores de junção de tabelas no caso os Joins, possibilitando ao SQL Server fazer uso de mecanismo mais indicados para se trabalhar com relacionamento de dados, no seu caso, você não esta fazendo isso deste tipo de recurso, o que faz com o que o Plano de Execução trabalhe de uma forma bastante diferente.

    7 - Questões como forma de armazenamento dos dados em disco também podem influenciar na perfromance, se o seu Hd ou conjunto de disco estiverem fragmentados, além disso, quando utilizamos RAID, por exemplo RAID 1 temos que ter em mente que este modelo de recurso realiza a gravação de dados em dois ou mais locais de forma simultânea.

    8 - Você esta trabalhando com um volume considerável de dados, por acaso já verificou se realmente precisa trabalhar com Modelo de Recuperação de Banco de Dados Full? Pois este tipo de processamento com o Recovery Model Full vai gerar um custo de processamento em disco muito grande pois você estará em tempo real, atualizando dados em disco e também gravando o que esta sendo manipulado no arquivo de log de transações do seu banco. Ao meu ver o mais correto seria alterar este Recovery Model para Simple.

    9 - Eu particularmente quando vou trabalhar com um conjunto volumoso de dados, prefiro realizar este tipo de procedimento em table temporárias, sendo assim, não gero inicialmente nenhum tipo de bloqueio de dados no objeto que possui as informações. No seu cenário, o uso de Table Temp, poderá ajudar bastante, fazendo o Update na table temp e depois repassando estes dados para sua tabela de origem.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    terça-feira, 23 de outubro de 2012 14:17
    Moderador

Todas as Respostas

  • Boa noite,

    Não sei sinceramente como vai ficar a performance, mas experimente utilizar a sintaxe com Join:

    UPDATE tabelaA SET ID = tabelaB.ID
    FROM tabelaA
    JOIN tabelaB
        ON tabelaA.codigo = tabelaB.codigo
        AND tabelaA.ID = tabelaB.ID2
    

    Espero que seja útil.


    Assinatura: http://www.imoveisemexposicao.com.br

    domingo, 21 de outubro de 2012 22:29
  • É tenta fazer esse esquema do Join, outra dica seria, você verificar os tipos de dados dos campos.

    Exemplo campos que são int, varchar, etc.

    Boa Sorte

    segunda-feira, 22 de outubro de 2012 10:23
  • Marco,

    Verifique as configurações dos seus arquivos físicos (ldf, mdf e ndf), um autogrow em intervalos pequenos podem onerar os recursos da máquina. Uma opção para melhoria tbm é alocar a sua tabela com maior número de registros em um .ndf (isolado dos demais fisicamente).

    Abs,

    Fábio Battestin

    segunda-feira, 22 de outubro de 2012 11:56
  • Obrigado pelo retorno.

    Vou fazer o teste de colocar a tabela em outro arquivo.


    Marco Antônio Pinheiro / MCTS - MCC http://marcoantoniopinheiro.blogspot.com

    segunda-feira, 22 de outubro de 2012 13:53
  • Marco,

    Vamos por partes, diversos fatores podem atrapalhar:

    1 - Não pense que criar diversos índices poderá ajudar, muito pelo contrário isso poderá criar situações que impactam diretamente na forma de armazenamento e pesquisa dos dados.

    2 - Sempre que estamos fazendo um processo de manipulação de dados, temos que tem em mente que diversos processos estão sendo realizados e isso também poderá impactar em questões de bloqueios e até mesmo travamentos de transações.

    3 - Normalmente quando estamos utilizando conjuntos de tabelas que envolvem diversas linhas temos que pensar em como podemos filtrar os dados que queremos manipular, neste caso, sua claúsula Where esta apresentando 2 condições será que realmente elas são necessárias?

    4 - Situações de fragmentação de dados, índices desatualizadas, página de dados internas mal alocadas, páginas de dados internas desatualizadas, estatísticas de banco de dados e índices desnecessários podem forçar o plano de execução a mudar totalmente o seu caminho de processamento.

    5 - Procure sempre analisar o plano de execução que o SQL Server esta gerando para realizar este tipo de procedimento, analisando e identificando qual operador esta apresentando maior custo de processamento.

    6 - Por questões de boas práticas, utilizar os operadores de junção de tabelas no caso os Joins, possibilitando ao SQL Server fazer uso de mecanismo mais indicados para se trabalhar com relacionamento de dados, no seu caso, você não esta fazendo isso deste tipo de recurso, o que faz com o que o Plano de Execução trabalhe de uma forma bastante diferente.

    7 - Questões como forma de armazenamento dos dados em disco também podem influenciar na perfromance, se o seu Hd ou conjunto de disco estiverem fragmentados, além disso, quando utilizamos RAID, por exemplo RAID 1 temos que ter em mente que este modelo de recurso realiza a gravação de dados em dois ou mais locais de forma simultânea.

    8 - Você esta trabalhando com um volume considerável de dados, por acaso já verificou se realmente precisa trabalhar com Modelo de Recuperação de Banco de Dados Full? Pois este tipo de processamento com o Recovery Model Full vai gerar um custo de processamento em disco muito grande pois você estará em tempo real, atualizando dados em disco e também gravando o que esta sendo manipulado no arquivo de log de transações do seu banco. Ao meu ver o mais correto seria alterar este Recovery Model para Simple.

    9 - Eu particularmente quando vou trabalhar com um conjunto volumoso de dados, prefiro realizar este tipo de procedimento em table temporárias, sendo assim, não gero inicialmente nenhum tipo de bloqueio de dados no objeto que possui as informações. No seu cenário, o uso de Table Temp, poderá ajudar bastante, fazendo o Update na table temp e depois repassando estes dados para sua tabela de origem.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    terça-feira, 23 de outubro de 2012 14:17
    Moderador
  • Grande Junior,

    Vou pontuar suas considerações:

    1) Concordo plenamente. Estou tentando ser o mais criterioso possível, analisando o plano de execução e identificando se o índice será útil.

    2) No meu caso, é um processo de migração. E não existe concorrência.

    3) Sim. São necessárias pois a atualização será para todas as linhas do relacionamento. É uma renumeração de Id's, onde utilizo o antigo para mudar o atual.

    4) Pelo que analisei este ponto está OK. Tenho um processo de manutenção que executei antes da consulta.

    5) Vi que no plano de execução inicial estava rolando um Table Scan na tabela maior. Com a criação de um índice este algoritmo não mais apareceu.

    6) Verdade. Não encontrei uma outra forma de atualizar.

    7) Este ponto ainda não realizei uma analise. Mas em uma analise primária, sim, posso ter problemas com meu disco.

    8) Estou usando recovery simple.

    9) Não fiz este teste. Vou analisar este item.

    Obrigado pelos comentários.

    Abraço.

    Marco.


    Marco Antônio Pinheiro / MCTS - MCC http://marcoantoniopinheiro.blogspot.com

    terça-feira, 23 de outubro de 2012 16:22
  • você disse que criou um índice FK, porém FK não é índice e sim um constraint, acho que você quis dizer que criou uma PK ? Primary key..

    porém em qual campo ? se for no ID provavelmente isso que esta também matando o update, pois todo update tem que refazer o índice.

    Eu tentaria remover todos índices na tabela A visto que ela que vai sofrer o update.

    e criaria um indice na tabela B

    CREATE NONCLUSTERED INDEX [ix_indiceteste] ON [dbo].[tabelaB] 
    (
    [codigo] ASC
    )
    INCLUDE ( [ID]) ON [PRIMARY]

    e manda bala.. acho que o tablescan que ele irá fazer pode compensar o update indíce que ele teria que fazer.

    quarta-feira, 24 de outubro de 2012 19:24