SQL query timing out in code but takes a few seconds in management studio

Question

I know similar questions have been asked here before and there are a few with entity framework as well but I havent got any of them working for me.

I have a legacy code which uses entity framework data first approach and calls the stored procedure like;

var result = context.Database.SqlQuery<VoidEvent>("p_VoidEventSearchList @UserId, @EventTypeId, @StartDate, @EndDate, @ManagementArea, @ManagementArea2 "
                , new SqlParameter("@UserId", UserId)
                , new SqlParameter("@EventTypeId", EventTypeId)
                , new SqlParameter("@StartDate", Convert.ToDateTime(StartDate))
                , new SqlParameter("@EndDate", Convert.ToDateTime(EndDate).AddDays(1))
                , new SqlParameter("@ManagementArea", ManagementArea)
                , new SqlParameter("@ManagementArea2", ManagementArea2)
                ).ToList();
            return result;

this is throwing a timeout error whereas if i get the query from profiler and run it in management studio it takes only 3 seconds;

exec sp_executesql N'p_VoidEventSearchList @UserId, @EventTypeId, @StartDate, @EndDate, @ManagementArea, @ManagementArea2 ',N'@UserId nvarchar(36),@EventTypeId int,@StartDate datetime,@EndDate datetime,@ManagementArea nvarchar(4000),@ManagementArea2 nvarchar(4000)',@UserId=N'e91a860e-e04a-421c-8b0b-a4602aca1856',@EventTypeId=0,@StartDate='1753-01-01 00:00:00',@EndDate='9999-12-30 23:59:00',@ManagementArea=N'',@ManagementArea2=N''

The stored procedure parameters are:

ALTER PROCEDURE [dbo].[p_VoidEventSearchList]
    @UserId NVARCHAR(40) = ''
    , @EventTypeId INT = 0
    , @StartDate datetime--nvarchar(10) = ''
    , @EndDate datetime--nvarchar(10)  = ''
    , @ManagementArea NVARCHAR(10) = ''
    , @ManagementArea2 NVARCHAR(10) = '' 
AS
BEGIN

As some of the posts have sugeested I have made sure that the datatime is passed as datetime and not as string but this has not helped and the call is still timing out.

http://stackoverflow.com/questions/250713/sqldataadapter-fill-method-slow — Microsoft DN, Sep 10 '15 at 14:05
Here's a good article by Erland Sommarskog: [Slow in the Application, Fast in SSMS?](http://www.sommarskog.se/query-plan-mysteries.html) It's a long read, but worth your time. It covers the `SET` options that are mentioned in the link from @MicrosoftDN, plus a lot of other stuff. — Dave Mason, Sep 10 '15 at 14:41
@MicrosoftDN. thanks for the link. its a bit weird but when i SET ARITHABORT OFF, the query still takes 3 secs in management studio but is actually a lot quicker in code and no timeouts happen. I am setting ARITHABORT OFF at the start of the sp and setting it ON at the end of sp. — man_luck, Sep 11 '15 at 14:56
MSDN says "setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application" but in this case whats happenning is that when I set it OFF it actually runs quicker than before in code. I dont know why this is happenning — man_luck, Sep 11 '15 at 14:56
@DMason. Unfortunately the link is blocked by macfee filters at work so I could not see what it contains. — man_luck, Sep 11 '15 at 14:57

score 1 · Answer 1 · answered Sep 10 '15 at 14:11

SQL Server management studio and your application connect to SQL Server in different ways. I would check your the connection string in your .net Application to see if there are any funky connection options. In addition, the network route that that your application takes to resolve the SQL server may be the cause of the timeout too.

score 0 · Accepted Answer · answered Sep 21 '15 at 06:14

@MicrosoftDN. thanks for the link. its a bit weird but when i SET ARITHABORT OFF, the query still takes 3 secs in management studio but is actually a lot quicker in code and no timeouts happen. I am setting ARITHABORT OFF at the start of the sp and setting it ON at the end of sp.

MSDN says "setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application" but in this case whats happenning is that when I set it OFF it actually runs quicker than before in code. I dont know why this is happenning

SQL query timing out in code but takes a few seconds in management studio

2 Answers2