.net - funciones - ¿Por qué algunas consultas de SQL son mucho más lentas cuando se usan con SqlCommand?

funciones de agrupamiento en sql

Question

Tengo un procedimiento almacenado que se ejecuta mucho más rápido desde Sql Server Management Studio (2 segundos) que cuando se ejecuta con System.Data.SqlClient.SqlCommand (se agota después de 2 minutos).

¿Cuál podría ser la razón de ésto?

Detalles: en Sql Server Management Studio esto se ejecuta en 2 segundos (en la base de datos de producción):

EXEC sp_Stat @DepartmentID = NULL

En .NET / C #, los siguientes tiempos se agotan después de 2 minutos (en la base de datos de producción):

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())
            {
            }
        }
    }
}

También probé con selectCommand = "sp_Stat" , CommandType = StoredProcedure y un SqlParameter , pero es el mismo resultado.

Y sin EXEC es el mismo resultado también.

En una base de datos de desarrollo casi vacía de datos, ambos casos terminan en menos de 1 segundo. Entonces, está relacionado con que hay muchos datos en la base de datos, pero parece que solo sucede desde .NET ...

Lo que Marc Gravell escribió sobre los diferentes valores de SET marca la diferencia en el caso presentado.

El SQL Server Profiler mostró que Sql Server Management Studio ejecuta los siguientes SET que el proveedor de datos de cliente .NET Sql no realiza:

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

Cuando incluí estos, la misma consulta tomó la misma cantidad de tiempo en SSMS y .NET. Y el SET responsable es ...

SET ARITHABORT ON

¿Qué he aprendido? Tal vez usar un perfilador en lugar de adivinar ...

(Al principio, la solución parecía estar relacionada con la detección de parámetros. Pero había mezclado algunas cosas ...)

Answer 1

Esto es casi seguro que se debe a un plan de consulta en caché "incorrecto". Esto ha surgido TAN algunas veces.

¿Tienes estadísticas actualizadas? ¿Un plan regular de mantenimiento de índices programado?

Puede probar si es definitivamente debido al plan de consulta en caché agregando esto a su definición de procedimiento almacenado:

CREATE PROCEDURE usp_MyProcedure WITH RECOMPILE...

Esto volverá a indexar una base de datos completa (¡cuidado si la base de datos es muy grande!):

exec sp_msforeachtable "dbcc dbreindex(''?'')"

SO posts:

Gran diferencia en el tiempo de ejecución del proceso almacenado entre Managment Studio y TableAdapter.

Parámetro Sniffing (o Spoofing) en SQL Server

¿Optimizar para desconocido para SQL Server 2005?

Diferente Plan de Ejecución para el mismo Procedimiento Almacenado.

Answer 2

Otra cosa que puede ser importante son las opciones SET que están habilitadas. Algunas de estas opciones cambian el plan de consulta lo suficiente para cambiar el perfil. Algunos pueden tener un gran impacto si está viendo (por ejemplo) una columna calculada + persistente (y posiblemente indexada): si las opciones de SET no son compatibles, se puede forzar a volver a calcular los valores, en lugar de usar el valor indexado - que puede cambiar una búsqueda de índice en una tabla de exploración + cálculo.

Intente usar el generador de perfiles para ver qué opciones de SET están "en juego", y ver si usar esas opciones cambia las cosas.

Otro impacto es la cadena de conexión; por ejemplo, si habilita MARS que puede cambiar el comportamiento de manera sutil.

Finalmente, las transacciones (implícitas ( TransactionScope ) o explícitas) pueden tener un gran impacto, según el nivel de aislamiento.

Answer 3

Tuvimos un problema similar, donde una consulta se completaba en 2 segundos en SSMS y tomaba más de 90 segundos cuando se llamaba desde un cliente .NET (escribimos varias aplicaciones / sitios VB / C # para probarlo).

Sospechamos que el plan de consulta sería diferente y reescribimos la consulta con sugerencias de bucle explícito ("unión de bucle interno" y "con índice"). Esto solucionó el problema.

Answer 4

Tuvo un problema similar y resultó que MultipleActiveResultSets = true en la cadena de conexión (que se supone que tiene un impacto mínimo) estaba haciendo que los registros de 1.5mil en una conexión remota tomen 25 minutos en lugar de alrededor de 2 minutos.