Em um banco de dados em que estou trabalhando, há uma tabela chamada personscom cerca de um milhão de linhas e 60 restrições FK (FK) de outras tabelas (algumas com muitos milhões de linhas) apontando para ela.
Se eu deletar uma linha de persons, demora muitos minutos, o que não seria um problema, mas também mantém a tabela bloqueada, bloqueando assim o banco de dados para todos os processos. No passado, isso fazia com que os usuários relatassem que o sistema estava inativo.
É claro que a situação melhoraria muito se eu adicionasse índices de suporte para todos os FKs (atualmente, apenas 20 das 60 tabelas o possuem). Mas muitos desses FKs são para colunas como modified_by, então todos os índices não teriam outra finalidade, e degradariam o desempenho do sistema nas operações diárias, apenas para ganhar uma melhora em um caso excepcional.
Antes de executar o DELETE, já me certifico de que todas as linhas de referência foram excluídas ou atualizadas. Faço isso manualmente, pois sou fortemente contra o uso do CASCADE.
Não estou considerando a exclusão reversível porque, caso contrário, teria que alterar todo o software que lê a personstabela para pular as linhas excluídas.
Pergunta
Existe uma maneira de (possivelmente temporariamente) alterar o mecanismo de bloqueio da personstabela, para que, mesmo que o DELETE demore uma hora, ele não afete os processos simultâneos?
Aqui está o plano de execução anônimo .
Considerações
Desabilitar FKs pode ser uma possibilidade. O risco é que outra pessoa crie inconsistência enquanto eu excluo a linha, então não consigo reativar os FKs.
Linhas a serem excluídas: normalmente uma de cada vez. Ou operação manual ou programada.
Ponto interessante: não verifiquei o plano de execução imediatamente, mas aparentemente, quase todas as operações custam 0%, exceto 7 casos de "Clustered index Scan (Clustered)" no PK das tabelas de referência; um deles custa 57%, o outro de 1% a 16%. Ainda não entendo por que ele deve verificar um índice clusterizado.
Você deve sempre indexar os campos usados como chaves, estrangeiras ou não. Isso dá ao banco de dados muito mais opções ao analisar como executar a consulta e adicioná-las pode realmente aumentar o desempenho geral do seu aplicativo.
Assim como [praticamente] tudo no Planet Database, é um compromisso.
Aqui, você está equilibrando a integridade dos dados - sabendo com certeza quem mudou alguma coisa - contra a velocidade - não tendo esses índices extras que retardam as inserções. Realmente importa se você não conhece (com certeza) o usuário que alterou esses registros? Se não, você está bem como está. Se isso importa - e em muitos casos, realmente importa - então você está preso à sobrecarga dos índices.
Com os FKs no lugar, você não poderá excluí-los de qualquer maneira, portanto, os valores dessa atividade são duvidosos.
A pergunta realmente deveria ser: por que a exclusão está demorando uma hora?
Se você estiver excluindo apenas um registro de uma tabela de um milhão de linhas e não houver linhas nas tabelas com FKs de volta a esta tabela, o que está demorando? Deve ser praticamente instantâneo.
Consultas lentas, até mesmo exclusões, geralmente são causadas por índices ausentes - você está executando a exclusão com base no campo Chave Primária do registro? Ou alguma outra combinação de campos [não indexados]?