4

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

Community
  • 1
  • 1
Akash
  • 1,716
  • 2
  • 23
  • 43
  • try using `SQL Profiler` might give you a idea where it needs improvement! – huMpty duMpty Oct 18 '13 at 09:58
  • Where does the `exec sp_executesql` come from? And how exactly is the parametrized code you're using? – Lucero Oct 18 '13 at 10:00
  • @huMptyduMpty Already looked there (mentioned it as trace by mistake) Basically I am seeing that the index does not get used when the parameterized query executes and it uses a wrong index – Akash Oct 18 '13 at 10:01
  • @Lucero It comes when I execute the parameterized query from C# – Akash Oct 18 '13 at 10:01
  • What are the datatypes of the parameters? Also why is `Str` both `varchar(8000)` and indexed? It will fail if you attempt to insert a value wider than 900 bytes anyway. – Martin Smith Oct 18 '13 at 10:02
  • @MartinSmith The strings are being passed as nvarchar s, they are stored as varchar(8000). Could this be an issue? Strings (str1,str2,etc) are the only parameters – Akash Oct 18 '13 at 10:05
  • @Akash - Yes the parameters need to be `varchar` not `nvarchar` – Martin Smith Oct 18 '13 at 10:06
  • @MartinSmith I am setting the parameters using command.parameters.addwithvalue() Any idea how I can force it to use varchar instead of nvarchar – Akash Oct 18 '13 at 10:10
  • Create the parameters explicitly and set the correct datatype. See [Parameterised queries–don’t use AddWithValue](http://andrevdm.blogspot.co.uk/2010/12/parameterised-queriesdont-use.html) – Martin Smith Oct 18 '13 at 10:12
  • @MartinSmith That link is exactly what is happening. You may want to have a copy of the link in your answer as well – Akash Oct 18 '13 at 10:24

3 Answers3

4

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

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.

Lucero
  • 59,176
  • 9
  • 122
  • 152
  • I used the one by Mark (the one I linked to) – Akash Oct 18 '13 at 10:07
  • Adding OPTION (RECOMPILE) to the end of my parameterized query worked wonders -- thanks Lucero! All these strange tricks I have to learn now, moving from Oracle to SQL Server :p – IGx89 Nov 26 '15 at 00:40
  • Good article describing OPTION (RECOMPILE) in more depth: http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options – IGx89 Nov 26 '15 at 01:05
0

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

Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44