c# - studio - Consulta extremadamente lenta en código pero rápida en SSMS

sql ssms tools

Question

Tengo una consulta bastante simple en la que sigo obteniendo tiempos de espera (tarda más de tres minutos en completarse, la detuve antes para poder publicar esta pregunta) sobre cuándo se ejecuta en el código, sin embargo, cuando ejecuto la misma consulta desde la misma computadora en Sql Server Management Studio, la consulta solo tomará 2532 ms la primera consulta cuando los datos no se almacenan en caché en el servidor y 524 ms para consultas repetidas.

Aquí está mi código c #

using (var conn = new SqlConnection("Data Source=backend.example.com;Connect Timeout=5;Initial Catalog=Logs;Persist Security Info=True;User ID=backendAPI;Password=Redacted"))
                using (var ada = new SqlDataAdapter(String.Format(@"
SELECT [PK_JOB],[CLIENT_ID],[STATUS],[LOG_NAME],dt 
FROM [ES_HISTORY] 
inner join [es_history_dt] on [PK_JOB] = [es_historyid] 
Where client_id = @clientID and dt > @dt and (job_type > 4 {0}) {1}
Order by dt desc"
     , where.ToString(), (cbShowOnlyFailed.Checked ? "and Status = 1" : "")), conn))
{
    ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID);
    ada.SelectCommand.Parameters.AddWithValue("@dt", dtpFilter.Value);
    //ada.SelectCommand.CommandTimeout = 60;
    conn.Open();
    Logs.Clear();
    ada.Fill(Logs); //Time out exception for 30 sec limit.
}

Aquí está mi código que estoy ejecutando en SSMS, lo saqué directamente de ada.SelectCommand.CommandText

declare @clientID varchar(200)
set @clientID = ''138''
declare @dt datetime
set @dt = ''9/19/2011 12:00:00 AM''

SELECT [PK_JOB],[CLIENT_ID],[STATUS],[LOG_NAME],dt 
FROM [ES_HISTORY] 
inner join [es_history_dt] on [PK_JOB] = [es_historyid] 
Where client_id = @clientID and dt > @dt and (job_type > 4 or job_type = 0 or job_type = 1 or job_type = 4 ) 
Order by dt desc

¿Qué está causando la mayor discrepancia por la diferencia en el tiempo?

Para mantener la sección de comentarios limpia, responderé algunas preguntas frecuentes aquí.

La misma computadora y el inicio de sesión se utilizan tanto para la aplicación como para ssms.

Solo se devuelven 15 filas en mi consulta de ejemplo. Sin embargo, es_history contiene 11351699 rows y es_history_dt contiene 8588493 rows . Ambas tablas están bien indexadas y el plan de ejecución en SSMS dice que están utilizando búsquedas de índice para las búsquedas, por lo que son búsquedas rápidas. El programa se está comportando como si no estuviera utilizando los índices para la versión C # de la consulta.

Answer 1

Capture el plan de ejecución desde ambos SSMS cuando ejecute manualmente su consulta y luego desde Profiler cuando esté ejecutando su aplicación. Comparar y contrastar.

Answer 2

Ejecute DBCC FREEPROCCACHE , como se sugiere here , solo para asegurarse de que el problema no se deba a un plan de ejecución de consultas obsoletas.

Answer 3

Ejecute el generador de perfiles en su conexión c #: puede haber otra actividad en marcha de la que no tenga conocimiento.

Answer 4

Su código en SSMS no es el mismo código que ejecuta en su aplicación. Esta línea en su aplicación agrega un parámetro NVARCHAR:

 ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID);

mientras que en el script SSMS lo declara como VARCHAR:

declare @clientID varchar(200)

Debido a las reglas de precedencia del tipo de datos, la expresión Where client_id = @clientID en su consulta no es compatible con SARG, donde @clientID es de tipo NVARCHAR (estoy haciendo un acto de fe y asumo que la columna client_id es de tipo VARCHAR). Por lo tanto, la aplicación fuerza un escaneo de la tabla donde la consulta SSMS puede hacer una búsqueda rápida de clave. Este es un problema bien conocido y comprendido con el uso de Parámetros.AddWithValue y se ha discutido en muchos artículos antes, por ejemplo. vea Cómo el código de acceso a datos afecta el rendimiento de la base de datos . Una vez que se entiende el problema, las soluciones son triviales:

• agregue parámetros con el constructor que acepta un tipo : Parameters.Add("@clientID", SqlDbType.Varchar, 200) (y pase la longitud explícita para evitar la contaminación de la memoria caché, vea el rendimiento de las consultas y planifique los problemas de la memoria caché cuando no se especifique la longitud del parámetro correctamente

• o convierta el parámetro en el texto de SQL: where client_id = cast(@clientID as varchar(200)) .

La primera solución es superior porque resuelve el problema de contaminación de caché además del problema de capacidad SARG.

También te recomendaría leer Lenta en la aplicación, ¿Rápido en SSMS? Entendiendo los misterios del rendimiento