As atualizações "vazias" criam quantidades iguais de log de transações? [duplicado]

meus veteranos concluíram que não podemos usar as instruções MERGE ou UPDATE onde todas as colunas são processadas na mesma instrução, pois isso cria um log excessivo.

Bem, é legal da parte deles concluir isso. Mas, eles forneceram alguma evidência, ou seus scripts de teste, mostrando esse comportamento? Eu estaria interessado em ver tal teste ;-)

O argumento para isso é que quando você executa uma instrução UPDATE no SQL Server, quando você define um valor de coluna e o novo valor é igual ao valor antigo, ele ainda é marcado como uma atualização no log de transações.

Este é um daqueles casos em que um pouco de conhecimento é enganoso. Sim, atualizar uma coluna para o mesmo valor exato é considerado uma atualização, assim como o teste de colunas sendo atualizadas por meio da UPDATE()função retornará 1enquanto a coluna estiver na instrução SET, independentemente do valor ser alterado ou não.

MAS, as peças que faltam são:

1. Se nenhuma das colunas estiver mudando de valor, essa linha não será realmente atualizada. E se nenhuma linha for atualizada, a única atividade do log de transações serão dois registros: um LOP_BEGIN_XACTpara marcar o início da transação e um LOP_COMMIT_XACTpara marcar o fim da transação. Mas nenhuma página de dados real ou página de índice é modificada. Isso pressupõe que "Linha(s) afetada(s)" > 0, mas nada realmente mudou.

2. Se todas as linhas forem filtradas de forma que nenhuma linha seja atualizada (ou seja, "Linha(s) afetadas" = 0), não haverá atividade de Log de Tran.

3. Se qualquer uma das colunas estiver mudando de valor, as colunas adicionais que estão sendo configuradas para seu valor existente terão a mesma aparência no log de transações como não especificando as colunas que não estão mudando de valor.

4. Cada consulta (a menos que agrupada com outras em uma transação explícita) é sua própria transação, e cada transação no log de transações tem, no mínimo, as 2 entradas: uma para o início e outra para COMMIT ou ABORT.

Logo:

• Suas duas opções de "Exemplo 1" e "Exemplo 2" são quase as mesmas no que diz respeito ao Tran Log. Se houver pelo menos uma linha para atualizar, elas devem ser as mesmas. Mas se não houver linhas em que as colunas estejam mudando de valor, o "Exemplo 1" (com a WHEREcláusula) resultará em menos atividade de Tran Log, pois não haverá entradas enquanto no "Exemplo 2" (todas as linhas "atualizadas") haverá as entradas BEGIN e COMMIT. Portanto, eu recomendaria usar a WHEREcláusula, pois está sendo explícita em suas intenções e resultará em um pouco menos de atividade de Tran Log.

• Seguir o conselho de seus "idosos" é garantido para resultar em mais atividade de Tran Log, sem mencionar a diminuição do desempenho. Por quê? Porque:

  • Em alguns casos, a mesma linha será sinalizada para atualização se o nome e o sobrenome forem alterados. Mesmo que você envolva ambas as UPDATEinstruções em uma única Transação Explícita para reduzir a inconsistência, bem como entradas de log BEGIN / END extras, você ainda estará atualizando a linha várias vezes em alguns casos, e cada modificação é registrada.
  • Mesmo que as linhas de dados já estejam na memória, ainda leva mais tempo para redigitalizá-las por cada UPDATEinstrução.

• É sempre melhor saber com certeza e ver por si mesmo, em vez de confiar em conjecturas ou no que outra pessoa afirma. Para isso, você deve testar suas várias opções, incluindo as duas atualizações separadas sugeridas por seus idosos, e após cada teste, verifique através de:

  SELECT   *
  FROM     sys.fn_dblog(NULL, NULL) tl
  ORDER BY tl.[Transaction ID] DESC; -- most recent first
  

PS Eu fiz meu teste inicial no SQL Server 2012 (SP3) Developer Edition. Em seguida, testei novamente no SQL Server 2016 (RTM) Express Edition e o comportamento foi o mesmo.

PPS Logicamente, [T].[first_name] = ISNULL(NULLIF([S].[first_name], [T].[first_name]), [T].[first_name])não é diferente de [T].[first_name] = [S].[first_name], é apenas envolvido em mais funções. Mas se ambas as colunas forem 'A', atualizar isso com um 'A'da mesma tabela em oposição a um 'A'da outra tabela é exatamente a mesma operação.

PPPS Ao verificar quaisquer diferenças nos campos de string, você realmente precisa usar um Collation binário, caso contrário, pode haver alterações em maiúsculas e minúsculas (ou largura ou combinação de caracteres, etc.) . Eu percebo que você mencionou que esses foram exemplos simplificados, mas estou apenas me certificando de que esse aspecto não seja esquecido :-). Por isso:

WHERE [T].[first_name] <> [S].[first_name] OR [T].[last_name] <> [S].[last_name]

torna-se:

WHERE [T].[first_name] <> [S].[first_name] COLLATE Latin1_General_100_BIN2
OR    [T].[last_name] <> [S].[last_name] COLLATE Latin1_General_100_BIN2

E:

ISNULL(NULLIF([S].[first_name], [T].[first_name]), [T].[first_name]),
ISNULL(NULLIF([S].[last_name], [T].[last_name]), [T].[last_name])

torna-se:

ISNULL(NULLIF([S].[first_name] COLLATE Latin1_General_100_BIN2, [T].[first_name]), [T].[first_name]),
ISNULL(NULLIF([S].[last_name] COLLATE Latin1_General_100_BIN2, [T].[last_name]), [T].[last_name])

Qual é o seu nível de registro? Você sabe que o simples limpará o log de transações entre as instruções?

Se você deseja minimizar o registro, várias atualizações

UPDATE [T]
   SET [T].[first_name] = [S].[first_name]
  FROM [#tgt] AS [T]
  JOIN [#src] AS [S] 
    ON [T].[id] = [S].[id]
   AND [T].[first_name] != [S].[first_name];

UPDATE [T]
   SET [T].[first_name] = [S].[first_name]
  FROM [#tgt] AS [T]
  JOIN [#src] AS [S] 
    ON [T].[id] = [S].[id]
   AND [T].[first_name] is null and [S].[first_name] is not null;

UPDATE [T]
   SET [T].[first_name] = null
  FROM [#tgt] AS [T]
  JOIN [#src] AS [S] 
    ON [T].[id] = [S].[id]
   AND [T].[first_name] is not null and [S].[first_name] is null;

Então a mesma coisa para last_name

Eu sei que parece longo mas irá minimizar o registo
As múltiplas actualizações são normalmente mais eficientes do que OR

Uh, oh, acabei de ler a mesma transação

Um ou outro vai ser muito diferente

Que tal uma vista

Basta usar a visualização durante a atualização - essa visualização deve ser muito eficiente

select case when [S].[ID] == null 
                 then [T].[first_name]
                 else [S].[first_name]
            end as [first_name]
     , case when [S].[ID] == null 
                 then [T].[last_name]
                 else [S].[last_name]
            end as [last_name]
from      [#tgt] [T] 
left join [#src] [S]
  on [t].[ID] = [S].[ID]