SQL query slow from .NET code, but not interactively

Question

We are using an ORM that is executing a call from .NET to SQL Server's sp_executesql stored procedure.

When the stored proc is called from .NET, we receive a timeout exception.

Looking at Profiler, I can see that the query is indeed taking a long time to execute.

The query is essentially:

exec sp_executesql N'SELECT DISTINCT
FROM [OurDatabase].[dbo].[Contract] [LPLA_1] ) [LPA_L1]
LEFT JOIN [OurDatabase].[dbo].[Customer] [LPA_L2]  ON [LPA_L2].[Customer_ID]=[LPA_L1].[CustomerId] AND [LPA_L2].[Data]=[LPA_L1].[Data])
WHERE ( ( ( ( ( [LPA_L1].[DealerId] = @DealerId1)) 
AND ( [LPA_L2].[Last_Name] = @LastName2))))',N'@DealerId1 varchar(18),@LastName2 varchar(25)',@DealerId1='1234',@LastName2='SMITH'

The confusing part for me is this: If I copy and paste the query that's timing out into SQL Management studio and execute it interactively, it executes just fine.

Does anyone know why the same query would take significantly longer when executed via .NET code? (I'm able to reproduce this -- the query executed from code consistently times out, and the query executed interactively consistently works fine.)

Any help is appreciated. Thanks!

Answer 1

One thing I've seen a few times is if you have a mismatch between nvarchar and varchar types for a query parameter on an indexed field. This can happen if you use varchar in your database and don't explicitly set the type of your parameter in .Net, which will assume nvarchar by default.

In that case, Sql Server chooses the more correct option rather than the better performing option. Rather than just convert your parameter to varchar, which would be a narrowing conversion that could potentially lose information, the database will be forced to convert every value for that column in the table to an nvarchar (which is guaranteed to succeed without loss of information). Not only is that slow, but Sql Server won't be able to use the index any more. Needless to say, the query will take much longer to run.