sp_executesql es lento con parámetros

dapper sql-server

Pregunta

Estoy usando dapper-dot-net como ORM y produce el siguiente código SQL de ejecución lenta (1700ms).

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (@id1,@id2) AND SensorId = @sensor AND SensorValue != -32768 AND SensorValue != -32767',N'@id1 bigint,@id2 bigint,@sensor int',@id1=139,@id2=726,@sensor=178

Cuando modifico este código eliminando los parámetros, la consulta se ejecuta increíblemente rápido (20 ms). ¿La falta de estos parámetros realmente hace esta gran diferencia y por qué?

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (139,726) AND SensorId = 178 AND SensorValue != -32768 AND SensorValue != -32767'

Respuesta aceptada

Añadir OPCION (RECOMPILE) al final

... AND SensorValue != -32767 OPTION (RECOMPILE) 

Sospecho que estás experimentando "olfateando parámetros"

Si ese es el caso podemos dejarlo con la OPCIÓN o considerar alternativas.

Actualización 1

El siguiente artículo lo presentará a "parámetro sniffing" http://pratchev.blogspot.be/2007/08/parameter-sniffing.html

Le aconsejo que conozca los entresijos y las salidas, ya que lo hará mucho mejor para comprender los aspectos internos de los servidores SQL (lo que puede morder).

Si lo comprende, sabrá que la compensación con la opción de recompilación puede ser una disminución del rendimiento si la sentencia se ejecuta muy a menudo.

Personalmente agrego la recompilación de opciones después de saber que la causa raíz es la detección de parámetros y la dejo a menos que haya un problema de rendimiento. La reescritura de una declaración para evitar el mal olfatear los parámetros conduce a la pérdida de la intención y esto reduce el mantenimiento. Pero hay casos en que la reescritura está justificada (utilice buenos comentarios cuando lo haga).

Actualización 2

La mejor lectura que tuve sobre el tema fue en el capítulo 32 llamado "Detección de parámetros: tu mejor amigo ... excepto cuando no es por" por GRANT FRITCHEY

Se recomienda.

SQL Server MVP Deep Dives, Volumen 2


Respuesta popular

Recientemente encontré el mismo problema. Lo primero que hice fue agregar un índice de cobertura no agrupada en las columnas de mi declaración where.

Esto mejoró el tiempo de ejecución en SQL, pero cuando Dapper estaba realizando la consulta, aún era lento, de hecho, se estaba agotando.

Entonces me di cuenta de que la consulta generada por dapper estaba pasando en el parámetro como nvarchar (4000) donde como mi columna de la tabla db era varchar (80) esto hizo que realizara un escaneo de índice en lugar de una búsqueda (sugiero que leas en índices si eso no tiene sentido para ti). al darme cuenta de esto, actualicé mi dapper donde la declaración sería así:

WHERE Referencia = convertir (varchar (80), @ Referencia)

Ejecutar el con la instrucción where anterior resultó en una búsqueda de índice y una mejora del 100% en el rendimiento.

Solo para Agregar: La Opción (Recompilar) no funcionó para mí.

Y después de toda esta canción y baile, hay una manera de decirle a dapper que haga esto por usted de manera predeterminada:

Dapper.SqlMapper.AddTypeMap (typeof (cadena), System.Data.DbType.AnsiString);

Esto asignará por defecto los parámetros de cadena a un varchar (4000) en lugar de un nvarchar (4000). Si necesita una comparación de cadenas Unicode, entonces puede hacer explícitamente la conversión en el parámetro.



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow