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?

Answer 1

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?