ATUALIZE o desempenho onde nenhum dado é alterado

Se eu tiver uma instrução UPDATE que na verdade não altera nenhum dado (porque os dados já estão no estado atualizado), há algum benefício de desempenho em colocar uma verificação na cláusula where para impedir a atualização?

Certamente pode haver, pois há uma pequena diferença de desempenho devido à UPDATE 1 :

• não atualizar nenhuma linha (portanto, nada para gravar no disco, nem mesmo atividade mínima de log) e
• tirando bloqueios menos restritivos do que o necessário para fazer a atualização real (portanto, melhor para simultaneidade) ( consulte a seção Atualização no final )

No entanto, quanta diferença existe precisaria ser medida por você em seu sistema com seu esquema, dados e carga do sistema. Existem vários fatores que influenciam o impacto de uma atualização sem atualização:

• a quantidade de contenção na tabela que está sendo atualizada
• o número de linhas sendo atualizadas
• se houver UPDATE Triggers na tabela que está sendo atualizada (conforme observado por Mark em um comentário na pergunta). Se você executar UPDATE TableName SET Field1 = Field1, um Update Trigger será acionado e indicará que o campo foi atualizado (se você verificar usando as funções UPDATE() ou COLUMNS_UPDATED ) e que o campo nas tabelas INSERTEDe são o mesmo valor.DELETED

Além disso, a seguinte seção de resumo é encontrada no artigo de Paul White, The Impact of Non-Updating Updates (conforme observado por @spaghettidba em um comentário em sua resposta):

O SQL Server contém várias otimizações para evitar logs desnecessários ou liberação de página ao processar uma operação UPDATE que não resultará em nenhuma alteração no banco de dados persistente.

• Atualizações não atualizadas em uma tabela clusterizada geralmente evitam log extra e liberação de página, a menos que uma coluna que forma (parte) da chave de cluster seja afetada pela operação de atualização.
• Se qualquer parte da chave do cluster for 'atualizada' para o mesmo valor, a operação será registrada como se os dados tivessem sido alterados e as páginas afetadas serão marcadas como sujas no buffer pool. Isso é uma consequência da conversão de UPDATE em uma operação de exclusão e inserção.
• As tabelas de heap se comportam da mesma forma que as tabelas em cluster, exceto que não têm uma chave de cluster para causar qualquer registro extra ou liberação de página. Este permanece o caso mesmo quando existe uma chave primária não clusterizada no heap. As atualizações não atualizadas para um heap geralmente evitam o registro e a liberação extras (mas veja abaixo).
• Tanto os heaps quanto as tabelas clusterizadas sofrerão o registro e a limpeza extras para qualquer linha em que uma coluna LOB contendo mais de 8.000 bytes de dados seja atualizada para o mesmo valor usando qualquer sintaxe diferente de 'SET nome_da_coluna = nome_da_coluna'.
• A simples ativação de qualquer tipo de nível de isolamento de versão de linha em um banco de dados sempre causa o registro e a liberação extras. Isso ocorre independentemente do nível de isolamento em vigor para a transação de atualização.

Por favor, tenha em mente (especialmente se você não seguir o link para ver o artigo completo de Paul), os dois itens a seguir:

1. As atualizações não atualizadas ainda têm alguma atividade de log, mostrando que uma transação está começando e terminando. É só que nenhuma modificação de dados acontece (o que ainda é uma boa economia).

2. Como eu disse acima, você precisa testar em seu sistema. Use as mesmas consultas de pesquisa que Paul está usando e veja se obtém os mesmos resultados. Estou vendo resultados ligeiramente diferentes no meu sistema do que é mostrado no artigo. Ainda não há páginas sujas a serem escritas, mas um pouco mais de atividade de log.

... Eu preciso que a contagem de linhas inclua a linha inalterada para saber se devo fazer uma inserção se o ID não existir. ... é possível obter a contagem de linhas que preciso de alguma forma?

De forma simplista, se você estiver lidando apenas com uma única linha, poderá fazer o seguinte:

UPDATE MyTable
SET    Value = 2
WHERE  ID = 2
AND Value <> 2;

IF (@@ROWCOUNT = 0)
BEGIN
  IF (NOT EXISTS(
                 SELECT *
                 FROM   MyTable
                 WHERE  ID = 2 -- or Value = 2 depending on the scenario
                )
     )
  BEGIN
     INSERT INTO MyTable (ID, Value) -- or leave out ID if it is an IDENTITY
     VALUES (2, 2);
  END;
END;

Para várias linhas, você pode obter as informações necessárias para tomar essa decisão usando a OUTPUTcláusula. Ao capturar exatamente quais linhas foram atualizadas, você pode restringir os itens a serem pesquisados ​​para saber a diferença entre não atualizar as linhas que não existem e não atualizar as linhas que existem, mas não precisam da atualização.

Eu mostro a implementação básica na seguinte resposta:

Como evitar o uso da consulta Merge ao upserting vários dados usando o parâmetro xml?

O método mostrado nessa resposta não filtra as linhas que existem ainda não precisam ser atualizadas. Essa parte pode ser adicionada, mas primeiro você precisa mostrar exatamente onde está obtendo o conjunto de dados no qual está mesclando MyTable. Eles estão vindo de uma mesa temporária? Um parâmetro com valor de tabela (TVP)?

ATUALIZAÇÃO 1:

Finalmente consegui fazer alguns testes e aqui está o que encontrei em relação ao log de transações e bloqueio. Primeiro, o esquema para a tabela:

CREATE TABLE [dbo].[Test]
(
  [ID] [int] NOT NULL CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED,
  [StringField] [varchar](500) NULL
);

Em seguida, o teste atualizando o campo para o valor que ele já possui:

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117

Resultados:

-- Transaction Log (2 entries):
Operation
----------------------------
LOP_BEGIN_XACT
LOP_COMMIT_XACT


-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
8 - IX          6 - PAGE
5 - X           7 - KEY

Por fim, o teste que filtra a atualização devido ao valor não mudar:

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117
AND    rt.StringField <> '04CF508B-B78E-4264-B9EE-E87DC4AD237A';

Resultados:

-- Transaction Log (0 entries):
Operation
----------------------------


-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
7 - IU          6 - PAGE
4 - U           7 - KEY

Como você pode ver, nada é gravado no log de transações ao filtrar a linha, ao contrário das duas entradas que marcam o início e o fim da transação. E embora seja verdade que essas duas entradas não são quase nada, elas ainda são alguma coisa.

Além disso, o bloqueio dos recursos PAGE e KEY é menos restritivo ao filtrar as linhas que não foram alteradas. Se nenhum outro processo estiver interagindo com esta tabela, provavelmente não será um problema (mas qual é a probabilidade disso, realmente?). Lembre-se de que esse teste mostrado em qualquer um dos blogs vinculados (e até mesmo meus testes) pressupõe implicitamente que não há contenção na tabela, pois nunca faz parte dos testes. Dizer que as atualizações sem atualização são tão leves que não vale a pena fazer a filtragem precisa ser tomada com um grão de sal, já que o teste foi feito, mais ou menos, no vácuo. Mas em Produção, essa tabela provavelmente não é isolada. Claro, pode muito bem ser que o pouco de registro e bloqueios mais restritivos não se traduzam em menos eficiência. Então, a fonte de informação mais confiável para responder a essa pergunta? Servidor SQL. Especificamente:seu SQL Server. Ele mostrará qual método é melhor para o seu sistema :-).

ATUALIZAÇÃO 2:

Se as operações em que o novo valor é igual ao valor atual (ou seja, sem atualização) numeram as operações em que o novo valor é diferente e a atualização é necessária, o padrão a seguir pode ser ainda melhor, especialmente se há muita disputa na mesa. A ideia é fazer um SELECTprimeiro simples para obter o valor atual. Se você não obtiver um valor, terá sua resposta em relação ao INSERT. Se você tiver um valor, poderá fazer um simples IFe emitir o UPDATE somente se for necessário.

DECLARE @CurrentValue VARCHAR(500) = NULL,
        @NewValue VARCHAR(500) = '04CF508B-B78E-4264-B9EE-E87DC4AD237A',
        @ID INT = 4082117;

SELECT @CurrentValue = rt.StringField
FROM   dbo.Test rt
WHERE  rt.ID = @ID;

IF (@CurrentValue IS NULL) -- if NULL is valid, use @@ROWCOUNT = 0
BEGIN
  -- row does not exist
  INSERT INTO dbo.Test (ID, StringField)
  VALUES (@ID, @NewValue);
END;
ELSE
BEGIN
  -- row exists, so check value to see if it is different
  IF (@CurrentValue <> @NewValue)
  BEGIN
    -- value is different, so do the update
    UPDATE rt
    SET    rt.StringField = @NewValue
    FROM   dbo.Test rt
    WHERE  rt.ID = @ID;
  END;
END;

Resultados:

-- Transaction Log (0 entries):
Operation
----------------------------


-- SQL Profiler (2 Lock:Acquired events):
Mode            Type
--------------------------------------
6 - IS          5 - OBJECT
6 - IS          6 - PAGE

Portanto, existem apenas 2 bloqueios adquiridos em vez de 3, e ambos os bloqueios são Intent Shared, não Intent eXclusive ou Intent Update ( Lock Compatibility ). Lembrando que cada lock adquirido também será liberado, cada lock é na verdade 2 operações, então esse novo método é um total de 4 operações ao invés das 6 operações do método originalmente proposto. Considerando que esta operação está sendo executada uma vez a cada 15 ms (aproximadamente, conforme declarado pelo OP), ou seja, cerca de 66 vezes por segundo. Portanto, a proposta original equivale a 396 operações de bloqueio/desbloqueio por segundo, enquanto esse novo método equivale a apenas 264 operações de bloqueio/desbloqueio por segundo de bloqueios ainda mais leves. Isso não é garantia de desempenho incrível, mas certamente vale a pena testar :-).

Diminua um pouco o zoom e pense no quadro maior. No mundo real, sua declaração de atualização realmente ficará assim:

UPDATE MyTable
  SET Value = 2
WHERE
     ID = 2
     AND Value <> 2;

Ou vai ficar mais assim:

UPDATE Customers
  SET AddressLine1 = '123 Main St',
      AddressLine2 = 'Apt 24',
      City = 'Chicago',
      State = 'IL',
      (and a couple dozen more fields)
WHERE
     ID = 2
     AND (AddressLine1 <> '123 Main St'
     OR AddressLine2 <> 'Apt 24'
     OR City <> 'Chicago'
     OR State <> 'IL'
      (and a couple dozen more fields))

Porque no mundo real, as tabelas têm muitas colunas. Isso significa que você terá que gerar muita lógica de aplicativo dinâmica complexa para criar strings dinâmicas, OU terá que especificar o conteúdo anterior e posterior de cada campo, sempre.

Se você construir essas instruções de atualização dinamicamente para cada tabela, passando apenas os campos que estão sendo atualizados, você pode rapidamente se deparar com um problema de poluição de cache de plano semelhante ao problema de tamanhos de parâmetro NHibernate de alguns anos atrás. Pior ainda, se você criar as instruções de atualização no SQL Server (como nos procedimentos armazenados), queimará ciclos de CPU preciosos porque o SQL Server não é muito eficiente na concatenação de strings em escala.

Por causa dessas complexidades, geralmente não faz sentido fazer esse tipo de comparação linha por linha, campo por campo, enquanto você faz as atualizações. Em vez disso, pense em operações baseadas em conjuntos.

Você pode ver um ganho de desempenho ao pular linhas que não precisam ser atualizadas apenas quando o número de linhas é grande (menos logs, menos páginas sujas para gravar no disco).

Ao lidar com atualizações de linha única, como no seu caso, a diferença de desempenho é completamente insignificante. Se atualizar as linhas em todos os casos facilitar para você, faça-o.

Para obter mais informações sobre o tópico, consulte Atualizações não atualizadas de Paul White

Você pode combinar a atualização e inserir em uma instrução. No SQL Server, você pode usar uma instrução MERGE para atualizar e inserir, caso não seja encontrada. Para MySQL, você pode usar INSERT ON DUPLICATE KEY UPDATE .

É comum filtrar atualizações não atualizadas, pois isso afetaria gatilhos de trilha de auditoria ou colunas de auditoria como LastModifiedDateTime. A maneira mais fácil de fazer isso para várias colunas que representam NULL é usar EXCEPT

Atualizar apenas se for diferente usando EXCETO

UPDATE Table1
SET Col1 = @NewVal1
,Col2 = @NewVal2
...
/*Audit trail columns*/
,LastModifiedBy = @UserID
,LastModifiedDateTime = GETDATE()
WHERE EXISTS ( /*Only updates if at least 1 column is different*/
                SELECT Col1,Col2
                EXCEPT
                SELECT @NewVal1,@NewVal2
            )