sp_executesql with N arguments is causing slow performance

Question

I am having a performance problem with a Rails activerecord object using the activerecord_sqlserver_adapter. Here is what the sql looks like that is generated and runs very slow;

EXEC sp_executesql N'SELECT COUNT(*) FROM [constituents] WHERE [constituents].[constituent_id] IN (N''10016125'', N''483663'', N''530657'', N''535217'')'

The following runs very fast;

EXEC sp_executesql N'SELECT COUNT(*) FROM [constituents] WHERE [constituents].[constituent_id] IN (''10016125'', ''483663'', ''530657'', ''535217'')'

The adapter is putting N in front of each item in the where clause that is slowing things down. The execution plan suggests I add an index, but that seems unnecessary and this is a legacy database.

Does anyone have a suggestion how I could speed this up?

I answered a similar question [here](http://stackoverflow.com/a/15477956/11808). — Christoffer Lette, Mar 18 '13 at 13:33

score 3 · Accepted Answer · answered Jan 25 '12 at 01:34

I highly recommend reading How Data Access Code Affects Database Performance and Slow in the Application, Fast in SSMS? Understanding Performance Mysteries. Both articles cover this topic, and much more, in great detail.

The summary is that the rules of Data Type Precedence dictate that an operation involving both an VARCHAR and an NVARCHAR operands must occur by converting the VARCHAR (precedence rank 27) to NVARCHAR (precedence rank 25). Therefore your query is really like I am having a performance problem with a Rails activerecord object using the activerecord_sqlserver_adapter. Here is what the sql looks like that is generated and runs very slow;

SELECT COUNT(*) 
FROM [constituents] 
WHERE CAST([constituents].[constituent_id] as NVARCHAR(...))
IN (N''10016125'', N''483663'', N''530657'', N''535217'');

This is non-sargable, meaning an index on constituent_id will be ignored and a table scan will be performed instead.

But the real question here is why do you use strings for what walks and quacks like an int? Shouldn't the constituent_id column be int, along with the parameters passed in?

The datatype of constituent_id is set in a legacy system, so it is what it is. It looks like I'm at the mercy of the adapter in this case. I think it puts the Ns in the sql to help with execution plan caching, but it's hurting me in this case. Thanks for your help! — SteveO7, Jan 25 '12 at 14:12

score 1 · Answer 2 · edited May 23 '17 at 11:57

Is constituent_id a string or a numeric? If it is a string, is there no way to instruct your adapter whether it is varchar or nvarchar? What is probably killing you is the resulting implicit conversion.

Have you thought about inserting the values into a table first and then performing a join? This can easily be done by passing in a comma-separated string and then using a UDF or XML to "split" the list into a table with the matching data type. Possibly see SQL query to match keywords?

score 1 · Answer 3 · answered Jan 25 '12 at 00:21

The 'N' indicates a Unicode string. I'm guessing the types in SQL Server are not Unicode (i.e. VARCHAR rather than NVARCHAR) and thus this code is generating an implicit conversion. Look at the execution plan and see if you can see convert_implicit in the operators. If this is the case, you'll need to figure out why the code generator is assuming the variables are Unicode. There may be some way to declare the variables as a different type that doesn't get translated to Unicode.

score 0 · Answer 4 · answered Jan 25 '12 at 00:18

select counts do a full table scan, an in clause is slow as well, the query is going to do a full table scan because of select count.

break up the query into small one line querys. store the count or use partioning to get better performance.

indexs, will help.

sp_executesql with N arguments is causing slow performance

4 Answers4

Linked