¿Por qué sp_executesql se ejecuta más lento cuando los parameters se pasan como arguments

Consulta 1: (rayo rápido)

sp_executesql "select * from tablesView where Id = 1" 

vs.

Pregunta 2: (demasiado lento)

 sp_executesql "select * from tablesView where Id = @Id", N"@Id int", @Id=1 

tablesView - a view containing multiple joins

LINQ siempre convierte las consultas a la forma Query2 y, por lo tanto, el performance es realmente malo.

Preguntas: necesito razones para la lentitud de query2 y cualquier resolución si hay una. Y una resolución para LINQ.

—-Comentarios adicionales:

El golpe de performance es definitivamente debido a las 2 columnas que están utilizando las funciones de sorting (row_number) pero no puedo evitarlas. Las necesito.

Voy a dar un paso aquí y asumir que tienes muchas filas donde ID = 1.

Si no es así, por favor, corríjanme.

Una posible razón por la que SQL Server está procesando su consulta es que revisa la consulta y dice:

Hmm, me pregunto qué va a pasar por ese parámetro.
va a ser 1? donde tengo alnetworkingedor de un montón de filas?
o quizás 1742, donde tengo solo 3
Simplemente no sé, será mejor que haga una exploración de la table para asegurarme de producir un plan de ejecución que cubra todas mis bases

Si una columna, o un set de columnas, tiene baja selectividad (es decir, el número de valores únicos es mucho menor que el número de filas), SQL Server algunas veces revertirá a una tabla o similar, solo para get todas las filas de manera determinista.

Al less esa ha sido mi experiencia. En particular, he visto el mismo comportamiento cuando selecciono el range de dates en tablas con datos de time determinado, haciendo un WHERE dt <= @dt AND dt >= @dt para get todas las filas donde @dt está dentro de un período de time en esa fila, revierte a un escaneo de tabla, y luego cuando coloco la date real en el SQL como literal, se ejecuta mucho más rápido.

El problema aquí es la selectividad, SQL Server no sabe cómo atender mejor todos los escenarios al build un plan de ejecución para su extracto, por lo que intentará adivinar.

Intente agregar una sugerencia de consulta para especificar un valor típico para el parámetro, es decir .:

 sp_executesql "select * from tablesView where Id = @Id option (optimize for (@id = 1742))", N"@Id int", @Id=1 

Esto podría ser un problema de olfateo de parameters . Intenta include la línea:

 OPTION (RECOMPILE) 

al final de su consulta SQL.

Hay un artículo aquí que explica qué es el rastreo de parameters: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

  1. Evite usar SELECT *
  2. ADO.NET 3.5 hay "cuestionario de parameters" en Linq 1 = TINYINT 2345 = SMALLINT 76357242 = INT .. en ADO.NET 4.0 el cuestionario de parameters se reemplaza por el tipo de datos INT INT por defecto 1 = INT, 2335 = INT, 76357242 = INT)