Quando fui a uma entrevista, o entrevistador me perguntou "quais são os benefícios dos procedimentos armazenados?" e comecei a atender, mas quando eu disse "Reduz o congestionamento da rede", ele de repente disse "não, não reduz".
Fiz uma pausa repentina, tive medo de perguntar por quê. Após a sessão de entrevista, pesquisei muito na Internet, mas descobri que o ponto que eu disse estava lá, mas não havia explicação para isso. Então me questionei e fiz algumas afirmações:
Como uma stored procedure é pré-compilada, ou seja, já foi criado um plano de execução para ela, aumenta a velocidade de execução. Mas para os casos em que passamos algumas variáveis para o SP, elas precisam ir para o servidor para execução e devem retornar resultados diferentes para cada variável.
Portanto, meu ponto é que o entrevistador está correto: o congestionamento da rede não é reduzido, toda vez que as variáveis devem ser passadas para o banco de dados e os resultados devem ser retornados, portanto, a rede está ocupada com eles. Certo?
Minha afirmação está correta?
No SQL Server, o otimizador de consulta segue estas etapas para executar qualquer consulta (chamada de procedimento armazenado ou instrução SQL ad hoc):
O ponto é: SQL ad-hoc e stored procedures não são diferentes .
Se uma consulta SQL ad-hoc estiver usando parâmetros corretamente - como deveria, de qualquer maneira, para evitar ataques de injeção de SQL - suas características de desempenho não são diferentes e definitivamente não são piores do que executar um procedimento armazenado.
O procedimento armazenado tem outros benefícios (não há necessidade de conceder aos usuários acesso direto à tabela, por exemplo), mas em termos de desempenho, usar consultas SQL ad-hoc devidamente parametrizadas é tão eficiente quanto usar procedimentos armazenados.
Se houver uma única instrução SQL de tamanho moderado (para que caiba em um único pacote de rede), a execução dessa instrução do aplicativo ou o empacotamento como um SP e a execução do SP serão idênticos em termos de rede.
No entanto, um padrão que vejo frequentemente é que uma função de aplicativo requer várias instruções SQL para produzir um resultado. Digamos que estamos escrevendo um pedido para o banco de dados. Primeiro validamos o cliente (1) depois o código do produto (2) depois o nível de estoque (3) depois escrevemos o cabeçalho do pedido (4) retornando o ID que é usado como chave estrangeira na linha do pedido (5). Se cada chamada for feita separadamente do aplicativo, são cinco viagens de ida e volta ao servidor de banco de dados. Se os valores forem passados como parâmetros para um SP, será uma única solicitação com uma única resposta - uma viagem de ida e volta.
Por causa disso, eu argumentaria que empacotar a parte intensiva de SQL das funções de negócios como procedimentos armazenados realmente reduz a carga da rede em cenários típicos.