Início / dba / Perguntas / 3999
Accepted
8kb
Asked: 2011-07-21 14:30:08 +0800 CST

A adesão ao princípio DRY justifica o SQL dinâmico?

  • 772

Suponha que eu tenha duas consultas que serão executadas com frequência em meu banco de dados:

SELECT 
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserID = @User

SELECT 
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserName LIKE @Name + '%

Eles devem estar em dois procedimentos armazenados separados ou apenas um único que cria as instruções dinamicamente usando sp_executesql?

Se eles estiverem em dois procedimentos armazenados, precisarei modificar ambos os procedimentos se quiser adicionar ou remover uma coluna na instrução SELECT. E se eu usar SQL dinâmico, presumivelmente estou sacrificando uma pequena quantidade de desempenho.

Este é um caso em que a manutenção e a adesão ao princípio DRY (não se repita) usando SQL dinâmico teriam precedência sobre o ganho de desempenho de um procedimento armazenado?

stored-procedures best-practices

2 respostas

  1. Best Answer

    Strict DRY realmente não se aplica a bancos de dados

    Eu vi DRY ser usado como justificativa para criar visualizações para "reutilização". Então, temos visualizações juntando visualizações, etc., e pioramos o desempenho.

    Geralmente, consultas semelhantes serão usadas de maneiras diferentes. Um pode ter um agregado, outro não, os filtros serão diferentes (como acima). A semelhança não justifica o SQL dinâmico nem justifica uma visualização.

    No seu caso específico acima, eu consideraria uma instrução IF para capturar a diferença, especialmente se o código foi emitido pela mesma página de pesquisa ou formulário: você tem semelhança com base no uso, não apenas nas mesmas colunas e tabelas sendo usadas.

    Além disso, você tem segurança a considerar.

    SQL dinâmico requer EXECUTE AS (escalonamento de direitos) ou permissões nas tabelas base. O uso de um procedimento armazenado simples não requer tais permissões.

    Você também pode ter código de cliente diferente: temos procedimentos armazenados por cliente (permissões de controle de esquemas). Deseja CONCEDER direitos de seleção em todas as suas mesas a todos os clientes?

    • 9

  2. Não tenho certeza se o SQL dinâmico é a única resposta aqui, embora às vezes possa ser a melhor solução se os parâmetros de pesquisa ficarem relativamente complexos. No caso simples que você propõe, por que não uma consulta simples que aceite um ou ambos os parâmetros...

    WHERE UserID = COALESCE(@User, UserID)
AND UserName LIKE COALESCE(@Name+'%', UserName)

    Mas você terá que considerar o SQL dinâmico se as condições de pesquisa ficarem complexas porque o SQL Server terá mais sorte tentando otimizar planos para as várias versões da consulta e tentando criar um plano mágico que satisfaça todos os diferentes critérios de pesquisa.

    Você pode criar procedimentos armazenados separados que identificam o(s) usuário(s) por seus métodos de pesquisa individuais, retornando apenas os valores da chave primária e, em seguida, chamar qualquer procedimento com base nos critérios, despejar os resultados em uma tabela #temp e, em seguida, defina o restante de sua lista de seleção em uma junção com a tabela base. Muito mais trabalho na frente, mas evita o problema de manutenção e permite otimizar procedimentos armazenados para cada critério de pesquisa.

    • 6

relate perguntas