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!

Preciosa

How many lines of data are you returning? If there's thousands and thousands of lines then that could take time to push across the wire to the computer expecting a result back. – Clinquant

Does the query return lots of data? Is there any data sending between server and client involved in the program case which does not happen in the interactive mode? – Cyaneous

Three in our test case. In addition, DealerID and Last_Name have indexes. – Preciosa

Does't it takes a while in interactive mode too? As far as I know, if you run a batch script in SQL Mgmt Studio, by default it doesn't ever timeout, while running from .NET code it has a default timeout of 30 (if I remember correctly). – Skysweeper

@Florin, no, it executes in < 1 second in interactive mode. – Preciosa

Are you sure it's a query timeout and not a connection timeout? – Highwrought

@madcolor: the <1 second from interactive mode isn't going to hit either timeout – Awad

Here's the error: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." But I can see the query taking 30+ seconds in Profiler. – Preciosa

I meant solely from .Net i.e. the interactive conn. has the proper credentials, and .Net conn. doesn't.. If you increase the timeout in your conn string, do you get your results? – Highwrought

@madcolor, if I set Connection and Command Timeout to 300, I get a response. – Preciosa

Then I'm barking up the wrong tree.. Keep us posted. – Highwrought

Could you try to create a small .NET console application that only runs the query to see if it's the application (ORM) to blame or the SQL Server. You can try a slightly modified query that hardcodes all the parameters inside the query, so no 'sp_executesql'. See if that still takes > 30 s? – Skysweeper

I have seen similar problems / symptoms in a vendor application (.NET) we have. We will have queries timeout in the application after running for 5+ minutes, but when I execute the same query captured by the Profiler using SSMS, it completes in 30 seconds or less. We have never been able to find the difference, and we really can't look at the vendor code to see the .NET side. – Aquatint

Looks like a good time to setup a simple example of this and work it into what you have now until you hit the issue. – Highwrought

Still investigating. I have a C# app that executes a hard-coded string query, a parameterized query and a query via sp_executesql, and the database performs differently at different times. It's not a caching issue, and it's not an ORM issue. It may be a parameter sniffing issue, but we haven't figured out if it is, or how to fix it. I will post when we know more. – Preciosa

Have you timed it in SQL Profiler to see the difference in performance between the two environments. Also, did you break it at the last call in code to time the execution? Sounds like a connection has to be wound up. – Mausoleum

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.

Awad

Why would it matter that the query is run from .NET rather than from SQL Mgmt Studio? – Skysweeper

Did you read my post? When you create parameters for your sqlcommand object in .Net (even if that part is hidden by an orm) and don't explicitly tell it what the types of those parameters are, .Net will choose for you and it might choose wrong. The result is that it's not exactly the same query. – Awad

Yes, but AlexWalker is running in SQL Mgmt Studio the query taken from the profiler ==> that's exactly the same query the SQL Server will run eventually regardless of the originator (.NET or Mgmt. Studio). Unless, the connection is configured differently... I have a hunch that the connection is what makes the difference. – Skysweeper

I ran into the same scenario and verified that the problem was a varchar/nvarchar mismatch in parameters. I ran my query with nvarchar parameters (the field I am querying on is a varchar) and it ran in 20 seconds. I changed the parameters to varchar and it took 1 second. – Dislodge

Answer 2

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.

Candlemaker

Answer 3

Just had the same issue.

Rebuilding indices solved the problem.

Perhaps the problem lies in the type of the parameters being nvarchar vs index that is on a varchar column...?

Rea

Answer 4

I think it's because sp_executelsql is meant to re-use compiled query plans, so it doesn't re-sniff parameters when the same query again hits it so it ends up in using a plan which might be very slow(will tell you why a slower query plan) with the current parameter values.it appears to be that sp_executesql uses a different method for choosing indexes and apparently it is a broken method compared to a plain text query.

What differs is the Table Update for 'Company(a table)' under a sp_executesql is fed via a chain of nested loops while the text query is fed via a chain of hash matches. The construction of the views appears identical between the two version (which I would expect). Unfortunately, the remainder is very complex and they appear to be doing radically different things in the middle; even pulling an "actual" execution plan doesn't give actual execution times for the various sub components of the query. Really, I can't see any reason for sp_executesql to be choosing anything differently, but it reliably constructs a significantly slower plan.

Parameter sniffing is a solution to this, so you should rename the parameters names or you might even swap the column names in where clause that cause sp_executesql to recreate a query plan instead of using an old slow plan, of course this is not the solution but it won't cache a slower plan for so long.

Regards.

Injudicious

Answer 5

Here is what I found. I have a VERY complex stored proc that always counts info and puts the data in a 8 column 17 row matrix as it is called/run by Crystal Reports every month. The prod database was on a 96 GB crazy fast server! Recently it was downsized to a 32 GB virtual machine. While scaled down - it did make the app run slower in many ways - until a few indexes were added.

Then, the time of the month came to run this 17 row matrix monthly report...and as you can imagine, it timed out!

The proc call was pretty simple - 3 parameters. A Start Date, End Date, and a district to filter on - null equals ALL. The 2 dates were passed in from Crystal Reports as a character and these stored PROC parameters were then used all over the place throughout this crazy stored proc.

Each of the 17 rows - basically uses WITH statements and crazy joins to find rows of data before counting/pivoting into the results...which is NOT important in this article.

So here it is simplified....

CREATE PROCEDURE [dbo].[prcMonthlyStats]
@bDate          datetime
,@eDate         datetime
,@districtStr   varchar(120)
AS
BEGIN
SET NOCOUNT ON;

...

--the @bDate and @eDate params were DIRECTLY used throughout the 2000 lines of SQL,
--to filter data inside and out of WITH statements and various other selects!
--
--TIMES OUT!

...

CREATE PROCEDURE [dbo].[prcMonthlyStats]
@bDateStr       datetime
,@eDateStr      datetime
,@districtStr   varchar(120)
AS
BEGIN
SET NOCOUNT ON;

--FIX!  Declare 2 date time variables and simply assign the 2 date time parameters to them.
DECLARE @bDate      datetime
DECLARE @eDate      datetime
DECLARE @district   varchar(120)

--SET THE VARIABLES FROM THE PARAMETERS PASSED IN!  
SET @bDate = @bDateStr
SET @eDate = @eDateStr
SET @district = @districtStr
.....

--PRESTO! The optimizer could once again use indexes as it should.

So moral of the story is - the optimizer was able to do its thing by using the DECLARED datetimes.

Courbet

Answer 6

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.

Palmitin

No, they are both varcars in the database, unfortunately. – Preciosa