SQL query extremely slow when using a parameterized query

Question

I have a query of the form

SELECT DISTINCT Str,Score 
FROM Tab 
WHERE Str in ('Str1', 'Str2', 'Str3') AND Type = 0

Table schema is

  Str - varchar(8000)  
  Score - int  
  Type - bit  

I also have an index on Str which includes Type and Score

The number of strings in the IN vary

When I construct a direct query from C#, it's virtually instantaneous

When I use a parametrized query (using the method here https://stackoverflow.com/a/337792/508593 ), it becomes extremely slow -- the original query takes less than a second. This is timing out

Looking into SQL profiler and SSMS, the slowness seems to be due to the statement being wrapped in exec sp_executesql which causes an index scan instead of a seek. The direct query uses the index mentioned. With sp_executesql, the index does not

Is my suspicion correct and is there a way to resolve this?

---

In addition to the root cause specified by Martin, the solution was to explicitly set the parameter type using

command.Parameters[i].DbType = DbType.AnsiString;

Which forces varchar instead of nvarchar

Answer 1

The parameters need to be varchar not nvarchar.

Otherwise the query will be effectively

WHERE IMPLICIT_CAST(Str AS NVARCHAR(4000)) in (@P1,@P2,@P3) AND Type = 0

Which hurts index usage.

Answer 2

It's unclear from your question what approach to parametrization you took; the question you're referring to shows different methods.

If you went for the Table-Valued Parameter solution, you may be suffering from the cached query plan which is created by SQL Server without knowing the number of items in the TVP parameter. By default, IIRC, it assumes 10'000 items, which would explain the index scan instead of seek.

That being said, try to add a OPTION (RECOMPILE) hint at the end of the parametrized query, which will enable SQL Server to re-compile the query with the (then known) item counts.

Answer 3

The issue isn't with parametrized query.

When specifying the values hardcoded in your IN clause, according to MSDN, you better have a good estimation of #values:

Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table