Início / dba / Perguntas / 129659
Accepted
Magier
Asked: 2016-02-19 03:12:09 +0800 CST

Qual é o sentido e o benefício de usar SqlCommand.Prepare()?

  • 772

Encontrei um código de desenvolvedor onde o método SqlCommand.Prepare() (consulte MSDN) é amplamente usado antes da execução de consultas SQL. E eu me pergunto qual é o benefício disso?

Amostra:

command.Prepare();
command.ExecuteNonQuery();
//...
command.Parameters[0].Value = 20;
command.ExecuteNonQuery();

Eu brinquei um pouco e tracei. A execução do Comando após a chamada do Prepare()método faz com que o Sql Server execute a seguinte instrução:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@id int,@desc text',N'INSERT INTO dbo.testtable (id) VALUES (@id)',@id=20'
select @p1

Depois disso, quando o parâmetro obtém seu valor e SqlCommand.ExecuteNonQuery()é chamado, o seguinte é executado no Sql-Server:

exec sp_execute 1,@id=20

Para mim, parece que a instrução é compilada assim que Prepare()é executada. Eu me pergunto qual é o benefício disso? Isso significa que ele é colocado no cache do plano e pode ser reutilizado assim que a consulta final for executada com os valores de parâmetro desejados?

Eu descobri (e documentei em outra pergunta ) que SqlCommands que são executados com SqlParameters sempre são encapsulados em sp_executesqlchamadas de procedimento. Isso torna o Sql Server capaz de armazenar e reutilizar os planos independentemente dos valores dos parâmetros.

Em relação a isso, eu me pergunto se o prepare()método é meio inútil ou obsoleto ou se estou perdendo alguma coisa aqui?

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Preparar um lote SQL separadamente da execução do lote SQL preparado é uma construção efetivamente **inútil para SQL Server dado como os planos de execução são armazenados em cache. Separar as etapas de preparação (análise, vinculação de quaisquer parâmetros e compilação) e execução só faz sentido quando não há armazenamento em cache. O objetivo é economizar o tempo gasto na análise e compilação reutilizando um plano existente, e é isso que o cache interno do plano faz. Mas nem todos os RDBMSs fazem esse nível de cache (claramente pela existência dessas funções) e, portanto, cabe ao código do cliente solicitar que um plano seja "armazenado em cache" e, assim, salvar esse ID de cache e reutilizá-lo isto. Este é um fardo adicional no código do cliente (e o programador deve se lembrar de fazê-lo e acertar) que é desnecessário. Na verdade, a página do MSDN para o método IDbCommand.Prepare() afirma:

    O servidor armazena em cache os planos automaticamente para reutilização conforme necessário; portanto, não há necessidade de chamar esse método diretamente em seu aplicativo cliente.

    Deve-se notar que, até onde meu teste mostra (que corresponde ao que você mostra na pergunta), chamar SqlCommand.Prepare() , não faz uma operação apenas de "preparação": chama sp_prepexec que prepara e executa o SQL ; ele não chama sp_prepare, que analisa e compila apenas (e não aceita nenhum valor de parâmetro, apenas seus nomes e tipos de dados). Portanto, não pode haver nenhum benefício de "pré-compilação" na chamada SqlCommand.Prepare, pois ela faz uma execução imediata (supondo que o objetivo seja adiar a execução). NO ENTANTO , há um pequeno benefício potencial interessante de dar call SqlCommand.Prepare(), mesmo que dê call sp_prepexecem vez de sp_prepare. Veja a nota (** ) na parte inferior para obter detalhes.

    Meus testes mostram que mesmo quando SqlCommand.Prepare()é chamado (e observe que esta chamada não emite nenhum comando no SQL Server), o ExecuteNonQueryou ExecuteReaderseguinte é totalmente executado e, se for ExecuteReader, retorna linhas. Ainda assim, o SQL Server Profiler mostra que a primeira SqlCommand.Execute______()chamada após a SqlCommand.Prepare()chamada é registrada como um evento "Prepare SQL" e as .Execute___()chamadas subsequentes são registradas como eventos "Exec Prepared SQL".

    código de teste

    Ele foi carregado no PasteBin em: http://pastebin.com/Yc2Tfvup . O código cria um aplicativo de console .NET/C# que deve ser executado enquanto um rastreamento do SQL Server Profiler está em execução (ou uma sessão de eventos estendidos). Ele faz uma pausa após cada etapa para que fique claro quais declarações têm um efeito específico.

    ATUALIZAR

    Encontrei mais informações e um pequeno motivo potencial para evitar ligar para SqlCommand.Prepare(). Uma coisa que notei em meus testes e o que deve ser observado por qualquer pessoa executando esse aplicativo de console de teste: nunca há uma chamada explícita feita sp_unprepare. Fiz algumas pesquisas e encontrei a seguinte postagem nos fóruns do MSDN:

    SqlCommand - Preparar ou não preparar?

    A resposta aceita contém várias informações, mas os pontos importantes são:

    • ** O que a preparação realmente economiza é principalmente o tempo que leva para transmitir a string de consulta pela rede.
    • Uma consulta preparada não tem garantia de que um plano armazenado em cache seja salvo e não é mais rápida do que uma consulta ad hoc quando chega ao servidor.
    • Os RPCs (CommandType.StoredProcedure) não ganham nada com a preparação.
    • No servidor, um identificador preparado é basicamente um índice em um mapa na memória contendo TSQL para executar.
    • O mapa é armazenado por conexão, não sendo possível o uso de conexão cruzada.
    • A redefinição enviada quando o cliente reutiliza a conexão do pool limpa o mapa.

    Também encontrei a seguinte postagem da equipe SQLCAT, que parece estar relacionada, mas pode ser simplesmente um problema específico do ODBC, enquanto o SqlClient limpa corretamente ao descartar o SqlConnection. É difícil dizer, já que a postagem do SQLCAT não menciona nenhum teste adicional que ajude a provar isso como uma causa, como limpar o pool de conexões, etc.

    Cuidado com as instruções SQL preparadas

    CONCLUSÃO

    Dado que:

    • o único benefício real de ligar SqlCommand.Prepare()parece ser que você não precisa enviar o texto da consulta novamente pela rede,
    • chamando sp_preparee sp_prepexecarmazenando o texto da consulta como parte da memória da conexão (ou seja, memória do SQL Server)

    Eu recomendaria não ligar SqlCommand.Prepare()porque o único benefício potencial é economizar pacotes de rede, enquanto a desvantagem é ocupar mais memória do servidor. Embora a quantidade de memória consumida seja provavelmente muito pequena na maioria dos casos, a largura de banda da rede raramente é um problema, pois a maioria dos servidores de banco de dados está diretamente conectada aos servidores de aplicativos com um mínimo de 10 Megabit (mais provavelmente 100 Megabit ou Gigabit atualmente) conexões ( e alguns até estão na mesma caixa ;-). Isso e a memória é quase sempre um recurso mais escasso do que a largura de banda da rede.

    Suponho que, para quem escreve software que pode se conectar a vários bancos de dados, a conveniência de uma interface padrão pode mudar essa análise de custo/benefício. Mas, mesmo nesse caso, tenho que acreditar que ainda é fácil abstrair uma interface de banco de dados "padrão" que permita diferentes provedores (e, portanto, diferenças entre eles, como não precisar chamar Prepare()), dado que fazer isso é Object básico Programação orientada que provavelmente você já está fazendo no código do seu app ;-).

    • 23

  2. Em relação a isso, eu me pergunto se o método prepare () é meio inútil ou obsoleto ou se estou perdendo alguma coisa aqui?

    Eu diria que o método Prepare tem valor limitado no mundo SqlCommand, não que seja totalmente inútil. Um benefício é que Prepare faz parte da interface IDbCommand que o SqlCommand implementa. Isso permite que o mesmo código seja executado em outros provedores de DBMS (que podem exigir Prepare) sem lógica condicional para determinar se Prepare deve ser chamado ou não.

    A preparação não tem valor com o Provedor .NET para SQL Server além desses casos de uso, AFAIK.

    • 5

relate perguntas