Início / dba / Perguntas / 222201
Accepted
D-K
Asked: 2018-11-10 07:54:14 +0800 CST

Qual é a maneira mais rápida de redefinir uma coluna para um valor específico?

  • 772

Eu tenho 30 milhões de linhas dentro de um banco de dados sql server onde adicionei uma coluna de marcação simples. Um caractere permitindo null. Eu adicionei um índice para a coluna.

Eu o uso para acompanhar quais linhas foram processadas para uma extração de dados. Durante o desenvolvimento inicial estarei reiniciando o processo de extração do zero (várias vezes) e preciso redefinir o valor para '1'.

Sendo um desenvolvedor de banco de dados neófito (quem estou brincando...), minha primeira tentativa usou um script de atualização simples para redefinir o valor de '2' (o que processei esta linha) para '1'. A primeira vez que executei o script, ele foi executado em cerca de 5 minutos. A última vez que executei - levou 45 minutos. Tem que haver uma maneira melhor.

É possível acelerar isso removendo completamente a coluna e recriando-a com um valor padrão de '1'? A velocidade permanecerá consistente?

-- ATUALIZAR ---

Na próxima vez que precisar fazer isso, tentarei o seguinte:

ALTER TABLE acmewidgets DROP COLUMN LegacyExtract;
ALTER TABLE acmewidgets
        ADD LegacyExtract CHAR NOT NULL DEFAULT (1);
sql-server

3 respostas

  1. Best Answer

    Usei o seguinte e executa em 41 segundos!

    USE AcmeDatabase

DROP INDEX IF EXISTS IX_LegacyExtract ON AcmeWidgetTable

ALTER TABLE AcmeWidgetTable DROP CONSTRAINT IF EXISTS DF_Legacy_Extract

ALTER TABLE AcmeWidgetTable DROP COLUMN IF EXISTS LegacyExtract

GO

ALTER TABLE AcmeWidgetTable CHAR NOT NULL CONSTRAINT DF_Legacy_Extract DEFAULT (0)

GO

CREATE NONCLUSTERED INDEX [IX_LegacyExtract] ON [dbo].[AcmeWidgetTable]
(
    [LegacyExtract] ASC
)
    • 1

  2. Depende do tipo de dados que você tem e se é intensivo de gravação ou não, mas eu usei muito essa abordagem.

    1. Selecione MAX(id) de TABLEX;
    2. Criar Tabela TABLEX_2 COMO TABLEX;
    3. Inserir em TABLEX_2 Selecione COLUMN1,COLUMN2,....,MYNEWVALUE De TABLEX Onde ID <= MAX_ID_IN_STEP_1;
    4. RENOMEAR TABLEX PARA TABLEX_OLD,TABLEX_2 PARA TABLEX;
    5. Inserir em TABLEX Selecione COLUMN1,COLUMN2,....,MYNEWVALUE De TABLEX_OLD Onde ID > MAX_FROM_STEP_1;

    Opcionalmente você também pode fazer: ALTER TABLE TABLEX_2 DISABLE/ENABLE KEYS antes e depois da inserção grande.

    O benefício dessa abordagem é que RENAME é atômico

    • 0

  3. Use um instantâneo de banco de dados. Para uma única coluna, isso pode ser excessivo. Para reversões múltiplas e potencialmente complexas, pode funcionar bem.

    Quando o banco de dados estiver no estado inicial desejado, tire um instantâneo . Isso aparece para os consumidores como um novo banco de dados. Será uma cópia somente leitura dos dados como era quando o instantâneo foi criado. Inicialmente não possui páginas próprias, por isso a criação é rápida e ocupa pouco espaço. À medida que o banco de dados base é alterado, as imagens "antes" das páginas alteradas são gravadas no instantâneo. Assim, o banco de dados instantâneo sempre tem os dados como estavam quando foi criado e o banco de dados base tem os novos dados. O tamanho do instantâneo depende da quantidade de alterações gravadas. No final da sessão, restaure o snapshot sobre o banco de dados base, reinicializando assim os dados.

    Como o instantâneo é legível, também é útil para verificar as alterações comparando os dados "antes" (instantâneo) com os dados "depois" (DB base).

    • 0

relate perguntas