c# - SQL Query es lento en la aplicación.NET pero instantáneo en SQL Server Management Studio

sql-server performance

Question

Aquí está el SQL

SELECT tal.TrustAccountValue
FROM TrustAccountLog AS tal
INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID
INNER JOIN Users usr ON usr.UserID = ta.UserID
WHERE usr.UserID = 70402 AND
ta.TrustAccountID = 117249 AND
tal.trustaccountlogid =  
(
 SELECT MAX (tal.trustaccountlogid)
 FROM  TrustAccountLog AS tal
 INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID
 INNER JOIN Users usr ON usr.UserID = ta.UserID
 WHERE usr.UserID = 70402 AND
 ta.TrustAccountID = 117249 AND
 tal.TrustAccountLogDate < ''3/1/2010 12:00:00 AM''
)

Básicamente, hay una tabla Usuarios, una tabla TrustAccount y una tabla TrustAccountLog.
Usuarios: contiene usuarios y sus detalles
TrustAccount: un usuario puede tener múltiples cuentas TrustAccounts.
TrustAccountLog: contiene una auditoría de todos los "movimientos" de TrustAccount. UN
TrustAccount está asociado con múltiples entradas TrustAccountLog. Ahora esta consulta se ejecuta en milisegundos dentro de SQL Server Management Studio, pero por alguna extraña razón demora para siempre en mi aplicación C # e incluso en tiempos de espera (120s) algunas veces.

Aquí está el código en pocas palabras. Se llama varias veces en un bucle y la declaración se prepara.

cmd.CommandTimeout = Configuration.DBTimeout;
cmd.CommandText = "SELECT tal.TrustAccountValue FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = @UserID1 AND ta.TrustAccountID = @TrustAccountID1 AND tal.trustaccountlogid =  (SELECT MAX (tal.trustaccountlogid) FROM  TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = @UserID2 AND ta.TrustAccountID = @TrustAccountID2 AND tal.TrustAccountLogDate < @TrustAccountLogDate2 ))";
cmd.Parameters.Add("@TrustAccountID1", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID1", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountID2", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID2", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountLogDate2", SqlDbType.DateTime).Value =TrustAccountLogDate;

// And then...

reader = cmd.ExecuteReader();
if (reader.Read())
{
   double value = (double)reader.GetValue(0);
   if (System.Double.IsNaN(value))
      return 0;
   else
      return value;
}
else
   return 0;

Answer 1

¿Sonidos posiblemente relacionados con el olfateo de parámetros? ¿Ha intentado capturar exactamente lo que el código del cliente envía a SQL Server (Use el generador de perfiles para capturar la declaración exacta) y luego ejecutar eso en Management Studio?

Parametrización de parámetros: rendimiento del plan de ejecución de procedimientos almacenados deficiente de SQL : detección de parámetros

No he visto esto en el código anteriormente, solo en los procedimientos, pero vale la pena verlo.

Answer 2

Como parece que solo devuelve el valor de una fila de una columna, puede usar ExecuteScalar() en el objeto de comando, que debería ser más eficiente:

    object value = cmd.ExecuteScalar();

    if (value == null)
        return 0;
    else
        return (double)value;

Answer 3

En mi caso, el problema era que mi Entity Framework generaba consultas que usaban exec sp_executesql .

Cuando los parámetros no coinciden exactamente en el tipo, el plan de ejecución no usa índices porque decide poner la conversión en la consulta misma. Como se puede imaginar, esto resulta en un rendimiento mucho más lento.

en mi caso, la columna se definió como CHR (3) y Entity Framework pasaba N''str ''en la consulta que causaba una conversión de nchar a char. Entonces, para una consulta que se ve así:

ctx.Events.Where(e => e.Status == "Snt")

Estaba generando una consulta SQL que se ve así:

FROM [ExtEvents] AS [Extent1] ... WHERE (N''''Snt'''' = [Extent1].[Status]) ...

La solución más fácil en mi caso fue cambiar el tipo de columna, alternativamente puedes luchar con tu código para hacerlo pasar del tipo correcto en primer lugar.

Answer 4

En mi experiencia, la razón habitual por la que una consulta se ejecuta rápidamente en SSMS pero es lenta desde .NET se debe a las diferencias en las conexiones SET conexión. Cuando se abre una conexión mediante SSMS o SqlConnection , se emiten automáticamente un conjunto de comandos SET para configurar el entorno de ejecución. Lamentablemente, SSMS y SqlConnection tienen diferentes valores predeterminados de SET .

Una diferencia común es SET ARITHABORT . Intente emitir SET ARITHABORT ON como el primer comando de su código .NET.

El Analizador de SQL se puede usar para supervisar qué comandos SET emiten tanto SSMS como .NET para que pueda encontrar otras diferencias.

El siguiente código muestra cómo emitir un comando SET pero tenga en cuenta que este código no se ha probado.

using (SqlConnection conn = new SqlConnection("<CONNECTION_STRING>")) {
    conn.Open();

    using (SqlCommand comm = new SqlCommand("SET ARITHABORT ON", conn)) {
        comm.ExecuteNonQuery();
    }

    // Do your own stuff here but you must use the same connection object
    // The SET command applies to the connection. Any other connections will not
    // be affected, nor will any new connections opened. If you want this applied
    // to every connection, you must do it every time one is opened.
}

Answer 5

Espero que su problema específico ya se haya resuelto ya que es una publicación anterior.

Las siguientes opciones de SET tienen el potencial de afectar el plan de reutilización (lista completa al final)

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ARITHABORT ON
GO

Las siguientes dos declaraciones son de msdn - SET ARITHABORT

Establecer ARITHABORT en OFF puede tener un impacto negativo en la optimización de la consulta, lo que genera problemas de rendimiento.

La configuración predeterminada de ARITHABORT para SQL Server Management Studio está activada. Las aplicaciones cliente que configuran ARITHABORT en OFF pueden recibir diferentes planes de consulta, lo que dificulta solucionar problemas de consultas de bajo rendimiento. Es decir, la misma consulta puede ejecutarse rápidamente en el estudio de administración pero lenta en la aplicación.

Otro tema interesante de entender es el Parameter Sniffing como se describe en blog blog

Otra posibilidad es con la conversión (interna) de columnas VARCHAR en NVARCHAR utilizando el parámetro de entrada Unicode como se describe en Solución de problemas del rendimiento del índice SQL en columnas varchar - por Jimmy Bogard

OPTIMIZAR POR DESCONOCIDO

En SQL Server 2008 y superior, considere OPTIMIZAR POR DESCONOCIDO. DESCONOCIDO: especifica que el optimizador de consultas utilice datos estadísticos en lugar del valor inicial para determinar el valor de una variable local durante la optimización de la consulta.

OPCIÓN (RECOMPULE)

Use "OPCIÓN (RECOMPULE)" en lugar de "CON RECOMPULE" si la recompilación es la única solución. Ayuda en la optimización de incrustación de parámetros. Lea Omitir parámetros, Incrustar y las Opciones RECOMPIBLES - por Paul White

Opciones de SET

Las siguientes opciones SET pueden afectar la reutilización del plan, en función de msdn: planificar el almacenamiento en caché en SQL Server 2008

1. ANSI_NULL_DFLT_OFF 2. ANSI_NULL_DFLT_ON 3. ANSI_NULLS 4. ANSI_PADDING 5. ANSI_WARNINGS 6. ARITHABORT 7. CONCAT_NULL_YIELDS_NUL 8. DATEFIRST 9. DATEFORMAT 10. FORCEPLAN 11. IDIOMA 12. NO_BROWSETABLE 13. NUMERIC_ROUNDABORT 14. QUOTED_IDENTIFIER

Answer 6

Le sugiero que intente y cree un procedimiento almacenado, que el servidor Sql puede compilar y almacenar en caché y así mejorar el rendimiento

Answer 7

Lo más probable es que el problema radique en el criterio

tal.TrustAccountLogDate < @TrustAccountLogDate2

El plan de ejecución óptimo dependerá en gran medida del valor del parámetro; pasar 1910-01-01 (que no devuelve ninguna fila) seguramente causará un plan diferente al 2100-12-31 (que devuelve todas las filas).

Cuando el valor se especifica como un literal en la consulta, el servidor SQL sabe qué valor usar durante la generación del plan. Cuando se usa un parámetro, SQL Server generará el plan solo una vez y luego lo reutilizará, y si el valor en una ejecución posterior difiere demasiado del original, el plan no será óptimo.

Para remediar la situación, puede especificar OPTION(RECOMPILE) en la consulta. Agregar la consulta a un procedimiento almacenado no lo ayudará con este problema en particular , a menos que cree el procedimiento WITH RECOMPILE.

Otros ya han mencionado esto ("sniffing de parámetros"), pero pensé que una simple explicación del concepto no dolería.

Answer 8

Me doy cuenta de que OP no menciona el uso de procedimientos almacenados, pero hay una solución alternativa a los problemas de detección de parámetros cuando se usan procedimientos almacenados que son menos elegantes pero me han funcionado cuando OPTION(RECOMPILE) no parece hacer nada.

Simplemente copie sus parámetros a las variables declaradas en el procedimiento y utilícelos en su lugar.

Ejemplo:

ALTER PROCEDURE [ExampleProcedure]
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN

--reassign to local variables to avoid parameter sniffing issues
DECLARE @MyStartDate datetime,
        @MyEndDate datetime

SELECT 
    @MyStartDate = @StartDate,
    @MyEndDate = @EndDate

--Rest of procedure goes here but refer to @MyStartDate and @MyEndDate
END

Answer 9

Parece que no estás cerrando el lector de datos, ya que puede comenzar a sumarse en varias iteraciones ...

Answer 10

Puede ser problemas de conversión de tipo. ¿Todos los ID son realmente SqlDbType.Int en el nivel de datos?

Además, ¿por qué tener 4 parámetros donde 2 hará?

cmd.Parameters.Add("@TrustAccountID1", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID1", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountID2", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID2", SqlDbType.Int).Value = userId;

Podría ser

cmd.Parameters.Add("@TrustAccountID", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = userId;

Ya que a ambos se les asignó la misma variable.

(Esto podría estar causando que el servidor haga un plan diferente, ya que espera cuatro variables diferentes como op. To. 4 constantes, lo que hace que 2 variables puedan hacer la diferencia para la optimización del servidor).

Answer 11

Si esto es olfateo de parámetros, intente agregar la option(recompile) al final de su consulta. Yo recomendaría crear un procedimiento almacenado para encapsular la lógica de una manera más manejable. También estoy de acuerdo: ¿por qué pasas 5 parámetros si solo necesitas tres, a juzgar por el ejemplo? ¿Puedes usar esta consulta en su lugar?

select TrustAccountValue from
(
 SELECT MAX (tal.trustaccountlogid), tal.TrustAccountValue
 FROM  TrustAccountLog AS tal
 INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID
 INNER JOIN Users usr ON usr.UserID = ta.UserID
 WHERE usr.UserID = 70402 AND
 ta.TrustAccountID = 117249 AND
 tal.TrustAccountLogDate < ''3/1/2010 12:00:00 AM''
 group by tal.TrustAccountValue
) q

Y, por lo que sea, está usando un formato de fecha ambiguo, según la configuración de idioma de la consulta que ejecuta el usuario. Para mí, por ejemplo, este es el 3 de enero, no el 1 de marzo. Mira esto:

set language us_english
go
select @@language --us_english
select convert(datetime, ''3/1/2010 12:00:00 AM'')
go
set language british
go
select @@language --british
select convert(datetime, ''3/1/2010 12:00:00 AM'')

El enfoque recomendado es usar el formato ''ISO'' aaaammdd hh: mm: ss

select convert(datetime, ''20100301 00:00:00'') --midnight 00, noon 12

Answer 12

Tenía el mismo problema en un entorno de prueba, aunque el sistema en vivo (en el mismo servidor SQL) funcionaba bien. Agregar OPCIÓN (RECOMPRA) y también OPCIÓN (OPTIMIZAR POR (@ p1 DESCONOCIDO)) no ayudó.

Utilicé SQL Profiler para captar la consulta exacta que enviaba el cliente .net y encontré que esto se exec sp_executesql N''select ... con exec sp_executesql N''select ... y que los parámetros se habían declarado como nvarchars; las columnas que se comparaban eran simples varchar.

Poner el texto de consulta capturado en SSMS confirmó que se ejecuta tan lentamente como lo hace desde el cliente .net.

Descubrí que al cambiar el tipo de parámetros a AnsiText se solucionó el problema:

p = cm.CreateParameter() p.ParameterName = "@company" p.Value = company p.DbType = DbType.AnsiString cm.Parameters.Add(p)

Nunca podría explicar por qué los entornos de prueba y en vivo tuvieron una diferencia tan marcada en el rendimiento.

Answer 13

Tuve un problema con una causa raíz diferente que coincidía exactamente con el título de los síntomas de esta pregunta.

En mi caso, el problema era que el conjunto de resultados se mantenía abierto por el código .NET de la aplicación mientras se hacía un bucle en cada registro devuelto y se ejecutaban otras tres consultas contra la base de datos. Más de varios miles de filas hicieron que la consulta original pareciera que había tardado en completarse según la información de tiempo de SQL Server.

Por lo tanto, la solución era refactorizar el código .NET realizando las llamadas para que no mantenga abierto el conjunto de resultados al procesar cada fila.