none
Delete em tabela com milhões de registros RRS feed

  • Pergunta

  • Olá pessoal!

    Sou iniciante em SQL e preciso de uma ajuda para excluir registros de uma tabela, deixando somente 90 dias de informação na tabela.

    A tabela tem um campo do tipo datetime onde será utilizado no WHERE.

    Gostaria de saber qual seria a melhor forma para se executar esse delete tendo como objetivo uma melhor performance ou se,

    no lugar do delete, gerar uma nova tabela com um SELECT pegando somente os dados referentes a 90 dias e depois dar um truncate na tabela de origem.

     

    Jeferson.

     

     

     

    quarta-feira, 17 de novembro de 2010 13:11

Respostas

  • Boa tarde Jefferson...

    Trabalho com Data Warehouse e mensalmente excluo fotos de tabelas históricas. No meu caso, eu tenho um índice no campo de data (que eu uso como parâmetro para exclusão). Faço a exclusão utilizando o TOP (100000), por exemplo, com um laço while, que sería a exclusão por blocos de 100 em 100 mil. Com isso eu nao comprometo a disponibilidade da minha tabela.

    Um ponto que vale lembrar, é o recovery model do seu database. Caso esteja em simple, não vejo muito problema, mas caso esteja FULL, é bom ficar de olho no arquivo .ldf que ele vai crescer violentamente.

    Agora a segunda opção que vc citou, eu também jah usei. Porém a sua tabela em algum momento vai ficar indisponível, que em alguns cenário isso pode não ser aceitável.

    Bom Jeffeson, no meu caso, eu uso a primeira situação por causa do meu cenário. Não sei qual é o seu.... Ah, não se esqueça do backup antes de uma operação como essa .....rsrsrsrs, pode ser útil.

    att.
    Rafael Melo

    quarta-feira, 17 de novembro de 2010 16:40
  • Você não pode fazer algo do tipo

    DELETE SUATABELA WHERE SEUCAMPODATA < (SELECT GETDATE() - 90)

    Repare que eu uso o seguinte código para obter a data de 90 dias atrás:

    GETDATE() - 90

     


    Se o post foi útil marque como resposta. - Advanced Web Application - MCP - Twitter: @mpghelli
    quinta-feira, 18 de novembro de 2010 17:19
  • Jeferson, boa tarde.

    É o seguinte. Pra ganrantir que o seu delete execute com performance voce deve verificar se o arquivo .MDF e.LDF estão em sub sistemas de disco separados.Isso ajuda a disponibilidade de IO.Também verifique o recovery model da base e garanta que ele esteja em SIMPLE.

    Código: ALTER DATABASE [NomeDoBancoDeDados] SET RECOVERY SIMPLE

    Para evitar o split page aumente o tamanho inicial do arquivo de log e o seu crescimento, por exemplo: Inical de Log 2000MB e crescimento de 500 MB. Isso fará com que o delete aconteça de forma performatica.

    Também verifique que o campo data que você deseja deletar existe um indice.

    Abs


    Thiago Carlos de Alencar
    quinta-feira, 18 de novembro de 2010 19:08

Todas as Respostas

  • Boa tarde Jefferson...

    Trabalho com Data Warehouse e mensalmente excluo fotos de tabelas históricas. No meu caso, eu tenho um índice no campo de data (que eu uso como parâmetro para exclusão). Faço a exclusão utilizando o TOP (100000), por exemplo, com um laço while, que sería a exclusão por blocos de 100 em 100 mil. Com isso eu nao comprometo a disponibilidade da minha tabela.

    Um ponto que vale lembrar, é o recovery model do seu database. Caso esteja em simple, não vejo muito problema, mas caso esteja FULL, é bom ficar de olho no arquivo .ldf que ele vai crescer violentamente.

    Agora a segunda opção que vc citou, eu também jah usei. Porém a sua tabela em algum momento vai ficar indisponível, que em alguns cenário isso pode não ser aceitável.

    Bom Jeffeson, no meu caso, eu uso a primeira situação por causa do meu cenário. Não sei qual é o seu.... Ah, não se esqueça do backup antes de uma operação como essa .....rsrsrsrs, pode ser útil.

    att.
    Rafael Melo

    quarta-feira, 17 de novembro de 2010 16:40
  • Você não pode fazer algo do tipo

    DELETE SUATABELA WHERE SEUCAMPODATA < (SELECT GETDATE() - 90)

    Repare que eu uso o seguinte código para obter a data de 90 dias atrás:

    GETDATE() - 90

     


    Se o post foi útil marque como resposta. - Advanced Web Application - MCP - Twitter: @mpghelli
    quinta-feira, 18 de novembro de 2010 17:19
  • Jeferson, boa tarde.

    É o seguinte. Pra ganrantir que o seu delete execute com performance voce deve verificar se o arquivo .MDF e.LDF estão em sub sistemas de disco separados.Isso ajuda a disponibilidade de IO.Também verifique o recovery model da base e garanta que ele esteja em SIMPLE.

    Código: ALTER DATABASE [NomeDoBancoDeDados] SET RECOVERY SIMPLE

    Para evitar o split page aumente o tamanho inicial do arquivo de log e o seu crescimento, por exemplo: Inical de Log 2000MB e crescimento de 500 MB. Isso fará com que o delete aconteça de forma performatica.

    Também verifique que o campo data que você deseja deletar existe um indice.

    Abs


    Thiago Carlos de Alencar
    quinta-feira, 18 de novembro de 2010 19:08
  • Jeferson,

    O que vc diz "no lugar do delete, gerar uma nova tabela com um SELECT pegando somente os dados referentes a 90 dias e depois dar um truncate na tabela de origem."

    É a solução mais rápida e fácil.

    Apenas acrescente no final de voltar os dados para a tabela principal e excluir a nova tabela.

    Obs.: Pode ocorrer o problema citado pelo Rafael anteriormente. Ao fazer este processo, garanta que somente vc esteja alocado na tabela abrindo uma transação durante a execução dos scripts.

    Att.,


    Marco Antônio Pinheiro / MCTS - Database Developer 2008 http://marcoantoniopinheiro.blogspot.com Se o post foi útil, não esqueça de marcá-lo.
    sexta-feira, 19 de novembro de 2010 13:54
  • Rafael, obrigado pelas dicas.

     

    Peguei um exemplo de um laço com while aqui no forum pra fazer isso também, seguindo essa lógica de exclusão por blocos.

    Obrigado.

     

    Jeferson.

     

     

    sexta-feira, 19 de novembro de 2010 17:10
  • Marcus,

    Utilizei um exemplo de delete abaixo:

     

    DELETE FROM tabela

    WHERE CAMPO < DateAdd (DAY,-90,GETDATE())

     

    Obrigado.

    sexta-feira, 19 de novembro de 2010 17:16
  • Thiago, boa tarde.

     

    Agradeço pelas dicas.

    Não atentei para questão do auto crescimento  do arquivo de log.

    Vou definir esses valores e criar o indice no campo datetime utilizado no WHERE.

     

    Obrigado.

     

    Jeferson.

    sexta-feira, 19 de novembro de 2010 17:19
  • Marco, boa tarde.

     

    Obrigado pelo retorno e pelas observações referentes ao tratamento das tabelas principal e cópia.

    Estou executando esses comandos num servidor de testes para, posteriormente executá-los numa "janela" de manutenção apropriada.

     

    Obrigado.

     

    Jeferson.

    sexta-feira, 19 de novembro de 2010 17:22

  • Olá Jefferson,

    Caso seu problema tenha sido resolvido, favor marcar como resposta os posts que lhe ajudaram a resolver o problema, pois isto facilita aos demais usuários do forúm a encontrarem as informações procuradas.

    Caso ainda o problema ainda não tenha sido resolvido, você pode identificar o que não está dando certo para que possamos respondê-lo?

    Obrigado


    Se o post foi útil marque como resposta. - Advanced Web Application - MCP - Twitter: @mpghelli
    quinta-feira, 25 de novembro de 2010 16:21