Why does sp_executesql run slower when parameters are passed as arguments

Question

Query 1: (lightning fast)

sp_executesql "select * from tablesView where Id = 1"

vs.

Query 2: (too slow)

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

tablesView - a view containing multiple joins

LINQ always converts queries to Query2 form and hence the performance is really bad.

Questions: I need reason for query2 slowness, and any resolution if there's one. And a resolution for LINQ.

----Additional comments:

The performance hit is definitely because of the 2 columns which are using ranking functions(row_number) but I can't avoid them I need them.

Answer 1

This could be a parameter sniffing problem. Try including the line:

OPTION (RECOMPILE)

at the end of your SQL query.

There is an article here explaining what parameter sniffing is: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Answer 2

I'm going to go out on a limb here and assume that you have a lot of rows where ID = 1.

If not, please correct me.

One possible reason that SQL Server is processing your query slow is that it looks at the query and goes:

hmm, I wonder what he's going to pass for that parameter.
is it going to be 1? where I have about a gazillion rows?
or perhaps 1742, where I have just 3
I just don't know, I better do a table scan to be sure to produce an execution plan that will cover all my bases

If a column, or a column set, has low selectivity (ie. the number of unique values is far less than the number of rows), SQL Server will sometimes revert to a tablescan or similar, just to get all rows deterministically.

At least that's been my experience. In particular I've seen the same behavior when doing date range selects on tables with time-bound data, doing a WHERE dt <= @dt AND dt >= @dt to get all rows where @dt is inside a period of time in that row, reverts to a table-scan, and then when I place the actual date into the SQL as a literal it runs far faster.

The problem here is the selectivity, SQL Server doesn't know how to best cater for all scenarios when building an execution plan for your statement, so it'll try to guess.

Try adding a query hint to specify a typical value for the parameter, ie.:

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