Por que algumas consultas SQL são muito mais lentas quando usadas com o SqlCommand?

Eu tenho um procedimento armazenado que executa muito mais rápido do Sql Server Management Studio (2 segundos) do que quando executado com System.Data.SqlClient.SqlCommand ( System.Data.SqlClient.SqlCommand após 2 minutos).

Qual poderia ser a razão disso?


Detalhes: No Sql Server Management Studio, isso é executado em 2 segundos (no database de produção):

  EXEC sp_Stat
     @DepartmentID = NULL

No .NET / C # os seguintes tempos expiram após 2 minutos (no database de produção):

 string selectCommand = @" EXEC sp_Stat @DepartmentID = NULL"; string connectionString = "server=***;database=***;user id=***;pwd=***"; using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand(selectCommand, connection)) { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { } } } } 

Eu também tentei com selectCommand = "sp_Stat" , CommandType = StoredProcedure e um SqlParameter , mas é o mesmo resultado.

E sem EXEC é o mesmo resultado também.

Em um database de desenvolvimento quase vazio, ambos os casos terminam em menos de um segundo. Então, está relacionado a isso, há muitos dados no database, mas parece acontecer apenas no .NET …


O que Marc Gravell escreveu sobre diferentes valores de SET faz a diferença no caso apresentado.

O SQL Server Profiler mostrou que o Sql Server Management Studio executa os seguintes SET que o .NET Sql Client Data Provider não:

 SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON SET NOEXEC, PARSEONLY, FMTONLY OFF 

Quando os incluímos, a mesma consulta demorou o mesmo tempo no SSMS e no .NET. E o SET responsável é …

 SET ARITHABORT ON 

O que eu aprendi? Talvez para usar um profiler em vez de adivinhar …

(Inicialmente, a solução parecia estar relacionada ao parâmetro sniffing. Mas eu havia misturado algumas coisas …)

Outra coisa que pode ser importante são as opções SET que estão ativadas. Algumas dessas opções alteram o plano de consulta o suficiente para alterar o perfil. Alguns podem ter um grande impacto se você estiver olhando (por exemplo) uma coluna calculada + persistida (e possivelmente indexada): se as opções SET não forem compatíveis, ela pode ser forçada a recalcular os valores, em vez de usar valor indexado – que pode alterar uma busca de índice em uma varredura de tabela + cálculo.

Tente usar o profiler para ver quais opções SET estão “em jogo” e veja se o uso dessas opções muda as coisas.

Outro impacto é a string de conexão; por exemplo, se você ativar o MARS, isso pode mudar o comportamento de maneira sutil.

Finalmente, as transactions (implícitas ( TransactionScope ) ou explícitas) podem ter um grande impacto, dependendo do nível de isolamento.

Isso é quase certamente devido a um plano de consulta em cache ‘incorreto’. Isso surgiu em SO algumas vezes.

Você tem statistics atualizadas? Um plano regular de manutenção de índice agendado?

Você pode testar se é definitivamente devido ao plano de consulta em cache, adicionando isso à sua definição de procedimento armazenado:

 CREATE PROCEDURE usp_MyProcedure WITH RECOMPILE... 

Isso irá indexar novamente um database inteiro (cuidado se o database for muito grande!):

 exec sp_msforeachtable "dbcc dbreindex('?')" 

SO postagens:

Grande diferença no tempo de execução do proc armazenado entre o Managment Studio e o TableAdapter.

Parâmetro Sniffing (ou Spoofing) no SQL Server

otimizar para desconhecido para o SQL Server 2005?

Plano de Execução Diferente para o mesmo Procedimento Armazenado

Teve um problema semelhante e acabou por ter MultipleActiveResultSets = true na cadeia de conexão (que é suposto ter impacto mínimo) estava fazendo puxar 1,5 milhões de registros em uma conexão remota levar 25 minutos em vez de cerca de 2 minutos.

Tivemos um problema semelhante, em que uma consulta seria concluída em 2 segundos no SSMS e levaria mais de 90 segundos quando chamada de um cliente .NET (escrevemos vários aplicativos / sites VB / C # para testá-lo).

Suspeitamos que o plano de consulta seria diferente, e reescrevemos a consulta com dicas de looping explícito (“inner loop join” e “with index”). Isso resolveu o problema.