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

Are dealerId or Lastname nvarchar (differently typed than the varchar parameters)?

This can cause conversion of entire indexes. If you find this to be the case, leave a comment and I will explain in more detail.

Answer 2

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.

Answer 3

Did you ever receive a solution to this problem? I am experiencing the exact same problem.

Answer 4

I have the same trouble, a procedure executed from .net that takes too much time (and does not return a lot of rows). I send a string to sql: "execute stored_procedure @parameter1 = value1" and I copy this and run on sql management studio but there everything runs fine. The corious of this case is that in my query I just add or remove a LETTER from a parameter value for cause it. I'm very confused.

For info, I'm using full text index and temp tables por paging, but like i said, the SAME QUERY (and I'm sure) runs perfectly in sql management studio.