SQL查询在代码中超时，但在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;

这将抛出一个超时错误，而如果我从profiler获得查询并在management studio中运行它，只需要3秒;

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.

由于一些帖子已经确认，我已经确保将datatime作为datetime而不是string传递，但这并没有帮助，调用仍然超时。

2 个解决方案

#1

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.

SQL Server management studio和您的应用程序以不同的方式连接到SQL Server。我将在.net应用程序中检查您的连接字符串，看看是否有任何时髦的连接选项。此外，应用程序用于解析SQL服务器的网络路由也可能是超时的原因。

#2

@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.

@MicrosoftDN。谢谢你的联系。这有点奇怪，但是当我设置了算术中止时，查询在management studio中仍然需要3秒，但是在代码中要快得多，而且不会出现超时。我在sp开始时设置了算术，并在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

MSDN说:“将计算结果设置为OFF可以接收到不同的查询计划，这使得很难对性能低下的查询进行故障排除。也就是说，同一个查询可以在management studio中快速执行，但在应用程序中执行速度较慢。我不知道为什么会这样