Alguém disse que é preferível criar suas consultas para evitar exceções de chave duplicadas, mas não estou convencido de que seja mais eficiente do que apenas definir IGNORE_DUP_KEY = ONo índice.
Meu objetivo é garantir que uma linha ou conjunto de linhas exista para um ou mais usuários antes de tentar atualizar essas linhas. Eu faço isso para que, quando tento atualizar a linha com uma instrução de atualização como a abaixo, e nenhuma linha seja afetada, é porque a [Count]parte do predicado não foi satisfeita, ao contrário da linha que não existe (ou seja, a [ID]parte do predicado não sendo satisfeita):
UPDATE [Inventory]
SET [Count] = [Count] + 1
WHERE [ID] = 3
AND ([Count] + 1) <= @MaxInventory
Eu poderia executar EXISTS(SELECT 1 From [Inventory] WHERE [ID] = 3para verificar essa única linha e inserir a linha apenas se ela não existir. Isso simplesmente evita inserções desnecessárias. A inserção, se necessário, ainda teria que lidar com transações simultâneas, portanto, exceções de chave duplicada ainda podem ocorrer.
Estou curioso para saber se é mais eficiente apenas ativar IGNORE_DUP_KEYneste cenário, em vez de permitir que o erro seja lançado e capturado. Especificamente, estou curioso para saber se é tão rápido ou possivelmente ainda mais rápido do que executar uma verificação existente, apenas para tentar inserir o registro e deixá-lo ignorar chaves duplicadas.
Isso se torna ainda mais importante quando estou verificando e inicializando vários registros ao mesmo tempo. Por exemplo, se eu precisar garantir que os registros de milhares de usuários existam em uma única instrução de atualização, a lógica seria muito mais simples se eu apenas executasse essa instrução de inserção antecipadamente, permitindo que ela ignorasse chaves duplicadas. Evitar duplicatas seria mais complexo, porque eu teria que primeiro consultar a tabela para a qual os registros não existem e, em seguida, tentar adicionar apenas esses registros (novamente, ignorando as chaves duplicadas). Apenas inserir pode ser mais rápido, mesmo que todos os registros existam.
Eu poderia encontrá-lo no meio do caminho e verificar se algum dos registros está faltando, como com uma junção à esquerda ou uma COUNTcomparação, mas por que se preocupar se a inserção ignorando chaves duplicadas é apenas mais rápida?
É uma boa ideia usar IGNORE_DUP_KEYe apenas tentar inserções em vez de se preocupar em verificar a existência de linhas antes do tempo? Se não, por quê?
É definitivamente uma configuração atípica para habilitar um índice. Eu não acho que você vai encontrar muitas pessoas que pulam e falam sobre usá-lo.
Claro, existem alguns posts úteis sobre o assunto de Paul White:
Se sua preocupação é em torno de padrões upsert (ou algo semelhante), este artigo de Michael Swart é realmente informativo:
Cada recurso tem um caso de uso e
IGNORE_DUP_KEYnão é exceção.Como explico em IGNORE_DUP_KEY mais lento em índices clusterizados , essa opção só potencialmente beneficia o desempenho (em um índice clusterizado) quando o número de exceções de chave duplicada é suficientemente pequeno.
O ponto de equilíbrio depende do sistema e requer testes realistas para avaliar adequadamente. Tente não fazer suposições ou julgamentos com antecedência. Teste cuidadosamente cada uma das alternativas de implementação robustas e escolha aquela que faz mais sentido em seu cenário.
Como exemplo de um motivo de surpresa de desempenho, considere que o ponto de inserção do índice deve estar localizado em algum momento, portanto, repetir essa operação pode ser mais barato do que o esperado. Quando o SQL Server pode usar a otimização de "compartilhamento de conjunto de linhas" , há ainda menos sobrecarga. O mecanismo localiza o ponto de inserção uma vez ao verificar a existência e mantém essa referência direta para a operação de inserção.
Embora não seja diretamente relevante para a pergunta, devo mencionar algumas ressalvas ao usar
IGNORE_DUP_KEY:MERGEinstruções.Geralmente, prefiro instruções separadas a
MERGE, mas você deve testá-las para o uso pretendido.MERGEpode combinar uma inserção e atualização em uma única instrução, enquanto aproveita a otimização de preenchimento de furos e o compartilhamento de conjuntos de linhas .Usamos
IGNORE_DUP_KEY = ONpara nossos processos de ETL em que os dados de origem e fluxo geralmente vêm com duplicatas. Não temos controle sobre isso.Isso funciona bem.
Não o usamos para nossos dados operacionais finais.
Não encontrei outro uso para ele.