none
COMPLICADO: Qual é mais rápido : INSERT ou UPDATE ? RRS feed

  • Pergunta

  • Boa noite,

     

    Gostaria de saber qual o maior custo ? Um update em um registro ou deletá-lo e inseri-lo novamente ?

     

    O caso de ter índices clusterizados ou nâo influi na velocidade do comando ?

     

    Trabalho com milhares de registros na mesma tabela e com um custo de processamento muito alto.

     

    Vocês sabem se existe um artigo com esse comparativo ? Tecnicamente como o SQL faz , internamente ,  o comando update ?

     

    Abraço a todos ...

    quarta-feira, 12 de dezembro de 2007 23:06

Todas as Respostas

  • Boa Noite,

     

    Na pior das hipóteses, o UPDATE irá empatar com o DELETE seguido do INSERT. Isso porque sobre certas circunstâncias, um UPDATE pode realizar exatamente um DELETE seguido de um INSERT. Nas demais situações, o UPDATE certamente irá superar o desempenho de um DELETE seguido de um INSERT.

     

    Índices irão influenciar na velocidade do comando. No caso dos comandos de INSERT e UPDATE, os índices (clusterizados ou não) sofrerão algum impacto por conta da aparição ou sumiço de novos registros. Já o comando UPDATE pode ou não provocar alterações nos índices.

     

    Se você possui um índice não clusterizado e atualiza alguma coluna pertencente ao índice, será necessário algum esforço do SGBD para reposicionar a entrada do índice já que você alterou colunas que estão no índice (chave do índice). Se o seu Update não atualizar nenhuma coluna do índice não clusterizado, então não haverá nenhuma atividade de manutenção nesse índice.

     

    Os índices clusterizados obedecem a estrutura de árvore BTREE e em seus nós folhas estão presentes os próprios dados. Isso significa que se você atualizar qualquer coluna da tabela, o índice clusterizado sofrerá algum overhead. Se as colunas atualizadas fizerem parte do índice, esse overhead deve influenciar o índice mais fortemente já que podem haver movimentações por todo o índice. Se as colunas atualizadas não fizerem parte do índice, esse overhead pode acontecer apenas nas páginas de dados sem movimentação nos outros níveis.

     

    No que se concerne a índices, um update pode ou não demorar mais por conta dos índices enquanto que as instruções de INSERT e DELETE sempre irão influenciá-los. Isso é mais um evidência de que um UPDATE tende a ser mais veloz que um DELETE seguido de um INSERT. No caso da tabela não possuir índices, pode até ser que o resultado fique mais empatado já que o tempo necessário para pesquisar o registro (seja para deletar ou excluir) pode ser consideravelmente grande em tabelas com muitos registros.

     

    Apontar um comparativo é mais delicado, mas uma regra geral é que quanto menos alterações forem feitas nas colunas do índice, mais o comando de UPDATE será mais performático que um comando de DELETE seguido de um INSERT.

     

    Se minha explicação já foi suficiente para convencê-lo, pediria apenas uma classificação da minha resposta. Se você ainda não se convenceu, posso postar posteriormente uma explicação mais detalhada do funcionamento do UPDATE, mas já adianto que ela será mais Hard Code.

     

    [ ]s,

     

    Gustavo

    quarta-feira, 12 de dezembro de 2007 23:40
  • Boa Noite,

     

    Gostaria de fazer outra colocação. Vejo muitas vezes uma certa preocupação com número de registros. Alguns dizem que a partir de 100.000 registros a tabela é grande. Outros reservam esse número para 1.000.000 enquanto outros preferem patamares maiores.

     

    Com certeza afirmar qual seria a quantidade ideal é algo um tanto arbitrário já que cada ambiente iria influenciar nessa resposta. No entanto, um outro fator que não é comentado é a largura do registro. Suponha duas tabelas com o esquema abaixo:

     

    TAB1 (Codigo INT, Nome VARCHAR(50), CPF CHAR(11), Descricao VARCHAR(4000))
    TAB2 (Codigo INT, CPF CHAR(11), Lancamento SMALLMONEY)

     

    A primeira tabela possui uma largura aproximada de 4065 bytes enquanto que a segunda possui uma largura aproximada de 19bytes. Se o SQL Server é capaz de trabalhar páginas de 8K, uma página caberia aproximadamente dois registros da primeira tabela contra 400 da segunda.

     

    Isso significa que se TAB2 tiver 400.000 de registros irá gastar cerca de mil páginas o que dará aproximadamente 8MB. Essa quantidade é insignificante para que o SQL Server a gerencie em memória. Em compensação, se TAB1 tiver apenas 100.000 registros, iremos gastar 50.000 páginas o que dá 400MB e isso é significativo para gerenciar na memória (admitindo que a média das descrições ocupe 4000 bytes).

     

    A indexação pode influenciar, mas ela não é o único fator de alto processamento. Seria bom analisar outras questões com bloqueios e a qualidade das consultas.

     

    [ ]s,

     

    Gustavo

    quarta-feira, 12 de dezembro de 2007 23:56
  • Excelente Gustavo. Sua resposta foi suficiente mas eu aceito a explicação, mesmo que for hard code e não lhe incomodar, do funcionamento do UPDATE.

     

    Muito obrigado,

     

     

     

    quinta-feira, 13 de dezembro de 2007 00:00
  • Oi Marcelo,

     

    Confesso que deu preguiça, mas às vezes depois que começo a escrever, disparo. Segue minha explanação.

     

    Toda operação no SQL Server produz um plano de execução. Operações de SELECT tem basicamente um plano de execução com uma única etapa, ou seja, recuperar informações. Essa etapa será desmembrada em outras como análise sintática, análise semântica, compilação, otimização, execução, etc, mas não é o foco para essa explicação.

    Operações de INSERT, UPDATE e DELETE tem um plano baseado em duas etapas, nas quais a primeira é somente leitura e é utilizada unicamente para definir que registros devem ser lidos para posteriormente passarem pelas operações de INSERT, UPDATE e DELETE.

     

    Quando se faz operações de INSERT não é preciso ter nenhuma espécie de ponteiro ou chave. Basta apenas ter os valores das colunas. Quando se faz uma operação de DELETE é preciso ter uma referência para os registros a serem excluídos e quando se faz uma operação de UPDATE, é preciso ter tanto uma referência para os registros afetados quando os novos valores das colunas. A obtenção tanto dos registros, quando dos ponteiros ou referências é obtida nessa primeira etapa (a da leitura).

     

    A segunda etapa irá aplicar as alterações necessárias na tabela. Primeiramente são verificadas questões de integridade como violação de PKs, FKs, entradas duplicadas em índices UNIQUE, etc e por fim triggers são disparadas (se houver). Caso haja qualquer problema aqui, as ações serão desfeitas (no caso da trigger apenas com um ROLLBACK explícito).

    As operações de UPDATE e DELETE possuem duas referências para a tabela. A primeira referência está na etapa de leitura de registros enquanto que a segunda está na etapa de gravação. No caso de operações de INSERT, apenas um referência é mantida já que não é necessário ler registros para fazer um INSERT (a menos que seja um INSERT com SELECT da própria tabela).

     

    Como havia mencionado, toda operação de INSERT e DELETE fatalmente vão afetar os índices existentes, mas no caso das operações de UPDATE, a carga de alterações é dependente do número de índices e da existência ou não de colunas no índice que sofram alterações. Dependendo da quantidade de índices e do impacto de um UPDATE, o SQL Server pode considerar duas estratégias para realizar o UPDATE. A primeira estratégia seria a "por linha (per row)" e a segunda "por índice (per index)".

     

    Na estratégia Per Row, o SQL Server faz a atualização linha-a-linha, ou seja, para cada linha afetada pelo comando UPDATE, é feita manutenção de cada índice envolvido e posteriormente da própria linha. O processo de atualização dos dados irá fazer um loop alterando a linha e posteriormente os índices até que todas as linhas afetadas sejam terminadas.

     

    Na estratégia Per Index, o SQL Server irá copiar as referências dos registros afetados (ponteiros ou chaves) em uma tabela temporária. Se houver mais de um índice haverá mais de uma tabela temporária. Essa tabela temporária utilizará as referências para verificar nas páginas de índices que entradas devem ser alteradas. Se o Update não alterar colunas em um determinado índice, o mesmo não será verificado. Apenas os índices cuja as colunas sejam referenciadas serão verificados

     

    A estratégia Per Row é eficiente em termos de CPU, já que em poucas operações, o UPDATE é feito. A estratégia Per Index é ruim, porque mais passos devem ser executados e isso consome mais CPU. No entanto, a estratégia Per Row é pior do ponto de vista de I/O, pois para cada registro será necessário atualizar os índices e como os índices serão "mexidos" várias vezes, há mais I/O. Já na estratégia Per Index, há uma melhora de I/O, pois, os dados são alterados apenas uma única vez e os índices também são visitados uma única vez. Não há como influenciar na escolha da estratégia. Essa fica à cargo do SQL Server.

     

    Eu havia comentado sobre situações nas quais um UPDATE pode ser substituido por um DELETE seguido de um INSERT. Quando um UPDATE apenas atualiza os dados, ele é conhecido como UPDATE IN PLACE. Quando um UPDATE faz um DELETE e em seguida um INSERT, ele é conhecido como NOT IN PLACE. Via de regra, o SQL Server faz UPDATE IN PLACE através das duas etapas (ler os registros afetados e gravar as alterações). As operações de UPDATE IN PLACE significam que a(s) linha(s) afetada(s) permaneceram no mesmo local e que o Log de transações conterá apenas uma única entrada de UPDATE.

     

    O UPDATE NOT IN PLACE pode acontecer em algumas ocasiões:

     

    - Se você atualizar uma coluna VARCHAR cujo o tamanho é maior do que o tamanho inicial e não há espaço disponível no local onde a linha estava
    - Se você atualizar colunas presentes em um índice Cluster
    - Se você estiver utilizando replicação transacional
    - Se você estiver utilizando triggers de UPDATE

     

    Vale a pena lembrar que UPDATEs IN PLACE são mais performáticos que UPDATEs NOT IN PLACE. Por isso mesmo que não recomendo substituir o UPDATE por um DELETE seguido de um INSERT. Você estaria transformando seu UPDATE IN PLACE para UPDATE NOT IN PLACE.

     

    Se puder classificar agora...

     

    [ ]s,

     

    Gustavo

    quinta-feira, 13 de dezembro de 2007 02:09
  • Gustavo,

     

    Obrigado pela atenção.  Me explique como eu te qualifico, pois confesso que é a primeira vez que entro neste site.

     

    Seus posts me ajudaram bastante.

     

    Aproveitando todo os seu conhecimento vou te expor o meu problema :

     

    - Faço insert em uma tabela o dia inteiro e gostaria de saber se seria mais performático se eu inserisse previamente os registros na noite anterior e durante o dia eu desse apenas UPDATES nos campos necessários. Neste caso o UPDATE é mais rápido do que o INSERT ?

     

    Muito obrigado,

     

    ( Me explique como eu devo te qualificar )

     


    Abração !

     

    quinta-feira, 13 de dezembro de 2007 12:41
  • Oi Marcelo,

     

    Toda resposta tem a pergunta "Esse post foi útil" e as opções "Sim" e "Não". Se a resposta ajudá-lo basta clicar em "Sim"

     

    O UPDATE iria envolver duas etapas, a primeira de leitura dos registros afetados e a segunda de efetivação das alterações. Nesse caso ele tende ser menos performático que o INSERT e recomendo que você mantenha as inserções.

     

    Isso só seria justificável se os INSERTs fosse inseridos a noite e durante o dia você fizesse os UPDATEs de forma que eles não alterem os índices. Nesse caso, embora ele tenha que ler e depois escrever existe a possibilidade (ainda que mínima) dele ser mais rápido, mas esse cenário é atípico e dependeria da realização de vários testes.

     

    Se você está sentindo lentidão nas operações de INSERT, seria muito bom verificar os índices envolvidos nas tabelas (principalmente o índice cluster). O ideal é que os registros sejam inseridos de forma a não "bagunçar" o índice clustered. Se isso for inevitável, podemos trabalhar outras questões como o FILL FACTOR por exemplo.

     

    [ ]s,

     

    Gustavo

     

    quinta-feira, 13 de dezembro de 2007 13:42