CODEWITHSUNDEEP
c#sql-serverasp.net-mvcentity-framework
man_luck
man_luck

Reputation: 1656

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

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.

Upvotes: 3

Views: 2574

Answers (2)

man_luck
man_luck

Reputation: 1656

@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

Upvotes: 0

Damo
Damo

Reputation: 91

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.

Upvotes: 1

Related Questions