Entity Framework 4.1/4.2 ，普通查询导致超时，未能解决问题。

开发环境 ASP.NET MVC3 + Entity Framework 4.1 + SQL Server 2005 sp4

项目中DAL层最近用到了新出 Entity Framework 4.1，开始用的时候基本还没发现什么问题，但部署到服务器，运行一段时间后，产生了一个很奇怪的问题，一句很简单的Linq查询一直会报超时，调试了好些天，最终还是一点解决办法都没有，现在总结一下出现的症状和过程中的一些假设，期待以后有人遇到之后能够解决。

1. Linq 语句

using(var db = new DataEntities)
{
var model = db.WF_DAT_QA_BAS_QA_VIEW.Where(p => p.QAID == QAID ).SingeOrDefault();
}

很简单的一句查询，关键是报超时的情况很特殊，他只针对特定某1,2数据运行超时，其他数据调试运行直接就查出来了。

2.SQL排错

我开始以为是Linq生成的T-SQL出了性能问题，于是开启了Profilter进行SQL语句追踪，将最终得到的T-SQL语句放到查询分析器执行，结果1s就完成了。

生成的SQL

exec sp_executesql N'SELECT TOP (2) 
[Extent1].[QAID] AS [QAID], 
[Extent1].[TITLE] AS [TITLE], 
[Extent1].[QATYPE] AS [QATYPE], 
[Extent1].[SUBMITDATE] AS [SUBMITDATE], 
[Extent1].[SUBMITUSERNAME] AS [SUBMITUSERNAME], 
[Extent1].[USERCARDTYPE] AS [USERCARDTYPE], 
[Extent1].[USERCARDNUMBER] AS [USERCARDNUMBER], 
[Extent1].[USERPHONE] AS [USERPHONE], 
[Extent1].[USERADRESS] AS [USERADRESS], 
[Extent1].[CONTENT] AS [CONTENT], 
[Extent1].[REPLY] AS [REPLY], 
[Extent1].[ISPUBLIC] AS [ISPUBLIC], 
[Extent1].[DUEDATE] AS [DUEDATE], 
[Extent1].[ATUONUMBER] AS [ATUONUMBER], 
[Extent1].[SOURCEID] AS [SOURCEID], 
[Extent1].[TASKINSTANCEID] AS [TASKINSTANCEID], 
[Extent1].[CARDTYPENAME] AS [CARDTYPENAME], 
[Extent1].[QATYPENAME] AS [QATYPENAME], 
[Extent1].[DUTEDATEYPENAME] AS [DUTEDATEYPENAME], 
[Extent1].[SOURCENAME] AS [SOURCENAME], 
[Extent1].[DEPARTMENTNAME] AS [DEPARTMENTNAME], 
[Extent1].[FLOWINSTANCEID] AS [FLOWINSTANCEID], 
[Extent1].[FLOWID] AS [FLOWID], 
[Extent1].[STARTTIME] AS [STARTTIME], 
[Extent1].[ENDTIME] AS [ENDTIME], 
[Extent1].[TASKUSER_ID] AS [TASKUSER_ID], 
[Extent1].[TASKALLOWUSER] AS [TASKALLOWUSER], 
[Extent1].[TASKID] AS [TASKID], 
[Extent1].[TASKSTATE] AS [TASKSTATE], 
[Extent1].[PRETASKINSTANCE] AS [PRETASKINSTANCE], 
[Extent1].[NEXTTASKINSTANCE] AS [NEXTTASKINSTANCE], 
[Extent1].[ISPOSTBACK] AS [ISPOSTBACK], 
[Extent1].[TASKMESSAGE] AS [TASKMESSAGE], 
[Extent1].[FLOWINTANCENAME] AS [FLOWINTANCENAME], 
[Extent1].[FLOWCURRENTTASKID] AS [FLOWCURRENTTASKID], 
[Extent1].[CREATEUSER] AS [CREATEUSER], 
[Extent1].[ENDUSER] AS [ENDUSER], 
[Extent1].[CREATEDATE] AS [CREATEDATE], 
[Extent1].[ENDDATE] AS [ENDDATE], 
[Extent1].[ISSUSPEND] AS [ISSUSPEND], 
[Extent1].[SUSPENDTIME] AS [SUSPENDTIME], 
[Extent1].[ISCANCEL] AS [ISCANCEL], 
[Extent1].[CANCELTIME] AS [CANCELTIME], 
[Extent1].[STARTTASKINSID] AS [STARTTASKINSID], 
[Extent1].[MOVEDEPT] AS [MOVEDEPT], 
[Extent1].[STARTTASKUSERNAME] AS [STARTTASKUSERNAME], 
[Extent1].[ISPUBLICPHONE] AS [ISPUBLICPHONE], 
[Extent1].[SN] AS [SN], 
[Extent1].[ISREVISIT] AS [ISREVISIT], 
[Extent1].[LastDay] AS [LastDay]
FROM (SELECT 
      [WF_DAT_QA_BAS_QA_VIEW].[QAID] AS [QAID], 
      [WF_DAT_QA_BAS_QA_VIEW].[TITLE] AS [TITLE], 
      [WF_DAT_QA_BAS_QA_VIEW].[QATYPE] AS [QATYPE], 
      [WF_DAT_QA_BAS_QA_VIEW].[SUBMITDATE] AS [SUBMITDATE], 
      [WF_DAT_QA_BAS_QA_VIEW].[SUBMITUSERNAME] AS [SUBMITUSERNAME], 
      [WF_DAT_QA_BAS_QA_VIEW].[USERCARDTYPE] AS [USERCARDTYPE], 
      [WF_DAT_QA_BAS_QA_VIEW].[USERCARDNUMBER] AS [USERCARDNUMBER], 
      [WF_DAT_QA_BAS_QA_VIEW].[USERPHONE] AS [USERPHONE], 
      [WF_DAT_QA_BAS_QA_VIEW].[USERADRESS] AS [USERADRESS], 
      [WF_DAT_QA_BAS_QA_VIEW].[CONTENT] AS [CONTENT], 
      [WF_DAT_QA_BAS_QA_VIEW].[REPLY] AS [REPLY], 
      [WF_DAT_QA_BAS_QA_VIEW].[ISPUBLIC] AS [ISPUBLIC], 
      [WF_DAT_QA_BAS_QA_VIEW].[DUEDATE] AS [DUEDATE], 
      [WF_DAT_QA_BAS_QA_VIEW].[ATUONUMBER] AS [ATUONUMBER], 
      [WF_DAT_QA_BAS_QA_VIEW].[SOURCEID] AS [SOURCEID], 
      [WF_DAT_QA_BAS_QA_VIEW].[CARDTYPENAME] AS [CARDTYPENAME], 
      [WF_DAT_QA_BAS_QA_VIEW].[QATYPENAME] AS [QATYPENAME], 
      [WF_DAT_QA_BAS_QA_VIEW].[DUTEDATEYPENAME] AS [DUTEDATEYPENAME], 
      [WF_DAT_QA_BAS_QA_VIEW].[SOURCENAME] AS [SOURCENAME], 
      [WF_DAT_QA_BAS_QA_VIEW].[DEPARTMENTNAME] AS [DEPARTMENTNAME], 
      [WF_DAT_QA_BAS_QA_VIEW].[FLOWINSTANCEID] AS [FLOWINSTANCEID], 
      [WF_DAT_QA_BAS_QA_VIEW].[FLOWID] AS [FLOWID], 
      [WF_DAT_QA_BAS_QA_VIEW].[STARTTIME] AS [STARTTIME], 
      [WF_DAT_QA_BAS_QA_VIEW].[ENDTIME] AS [ENDTIME], 
      [WF_DAT_QA_BAS_QA_VIEW].[TASKUSER_ID] AS [TASKUSER_ID], 
      [WF_DAT_QA_BAS_QA_VIEW].[TASKALLOWUSER] AS [TASKALLOWUSER], 
      [WF_DAT_QA_BAS_QA_VIEW].[TASKID] AS [TASKID], 
      [WF_DAT_QA_BAS_QA_VIEW].[TASKSTATE] AS [TASKSTATE], 
      [WF_DAT_QA_BAS_QA_VIEW].[PRETASKINSTANCE] AS [PRETASKINSTANCE], 
      [WF_DAT_QA_BAS_QA_VIEW].[NEXTTASKINSTANCE] AS [NEXTTASKINSTANCE], 
      [WF_DAT_QA_BAS_QA_VIEW].[ISPOSTBACK] AS [ISPOSTBACK], 
      [WF_DAT_QA_BAS_QA_VIEW].[TASKMESSAGE] AS [TASKMESSAGE], 
      [WF_DAT_QA_BAS_QA_VIEW].[FLOWINTANCENAME] AS [FLOWINTANCENAME], 
      [WF_DAT_QA_BAS_QA_VIEW].[FLOWCURRENTTASKID] AS [FLOWCURRENTTASKID], 
      [WF_DAT_QA_BAS_QA_VIEW].[CREATEUSER] AS [CREATEUSER], 
      [WF_DAT_QA_BAS_QA_VIEW].[ENDUSER] AS [ENDUSER], 
      [WF_DAT_QA_BAS_QA_VIEW].[CREATEDATE] AS [CREATEDATE], 
      [WF_DAT_QA_BAS_QA_VIEW].[ENDDATE] AS [ENDDATE], 
      [WF_DAT_QA_BAS_QA_VIEW].[ISSUSPEND] AS [ISSUSPEND], 
      [WF_DAT_QA_BAS_QA_VIEW].[SUSPENDTIME] AS [SUSPENDTIME], 
      [WF_DAT_QA_BAS_QA_VIEW].[ISCANCEL] AS [ISCANCEL], 
      [WF_DAT_QA_BAS_QA_VIEW].[CANCELTIME] AS [CANCELTIME], 
      [WF_DAT_QA_BAS_QA_VIEW].[STARTTASKINSID] AS [STARTTASKINSID], 
      [WF_DAT_QA_BAS_QA_VIEW].[MOVEDEPT] AS [MOVEDEPT], 
      [WF_DAT_QA_BAS_QA_VIEW].[TASKINSTANCEID] AS [TASKINSTANCEID], 
      [WF_DAT_QA_BAS_QA_VIEW].[STARTTASKUSERNAME] AS [STARTTASKUSERNAME], 
      [WF_DAT_QA_BAS_QA_VIEW].[ISPUBLICPHONE] AS [ISPUBLICPHONE], 
      [WF_DAT_QA_BAS_QA_VIEW].[SN] AS [SN], 
      [WF_DAT_QA_BAS_QA_VIEW].[ISREVISIT] AS [ISREVISIT], 
      [WF_DAT_QA_BAS_QA_VIEW].[LastDay] AS [LastDay]
      FROM [dbo].[WF_DAT_QA_BAS_QA_VIEW] AS [WF_DAT_QA_BAS_QA_VIEW]) AS [Extent1]
WHERE [Extent1].[QAID] = @p__linq__0',N'@p__linq__0 varchar(8000)',@p__linq__0='FBBE8530-46CC-4EF0-B4AD-4768F1AAE95B'

排除性能问题，我怀疑是数据库阻塞或者死锁了，于是去数据库执行阻塞查询，未发现阻塞线程。

在SQL找原因失败了。

3.代码排错

于是我又开始在周边代码查找问题，检查连接池是否有泄漏，数据连接是否没有关闭，未能发现，于是新建一个Controller类，单独写了一个Action,放上上面的Linq代码进行测试，结果一样，大部分数据都能正确查询，但特定的那1条就是读不出来，报 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.。

在调试过程中，有一个奇特的现象，当一段空闲时间，不访问读取那条数据，刷新页面之后正常读取，不超时。但是再次刷新又报超时错误。

最后百般无奈，检查数据库连接字符串，未能发现问题，

但是突发奇想将连接字符串中的 multipleactiveresultsets=True; 改为了Fasle

刷新页面之后，居然正确读取了，而且再次刷新也不会出现问题。但是此属性是Entity Framework延迟加载的必要属性，一旦设为False，就会引发 "已有打开的与此命令相关联的 DataReader，必须首先将它关闭。" 异常。

本来原来实体是直接用的edmx生成的 ObjectContext 与 ObjectQuery，后来实在没辙，尝试过用DbContext代码生成器模板转换成了DbContext 和 DbSet ，效果一样，一样报超时错误。

到此，以我当前能力已然无法解决了。最终只能尝试直接写T-SQL让context执行，结果运行成功。

db.ExecuteStoreQuery<WF_DAT_QA_BAS_QA_VIEW>("select * from WF_DAT_QA_BAS_QA_VIEW where qaid={0}", QAID).SingleOrDefault();

最终还是没能彻底解决这个问题，不知道EF框架的底层代码是怎么写的，为什么会出这么邪门的问题。

这个问题邪门就在，读取超时的情况不是每条数据都会出现，而是偶尔一条会出现，具体出现原因未知。而且就算你不管这条数据，等过1，2天，他又自动恢复了，又能正常读取。

这次是读取单条数据超时。前些天出的问题是读取另外一张视图的列表数据超时，症状一样，都是特定查询条件报超时错误。

    using (var db = new DataEntities())
    {
       var list = db1.WF_BAS_TASKINSTANCE_VIEW.Where(p => p.FLOWINSTANCEID == FLOWINSTANCEID);
    }

那时候也调试了好一会，最后也没能解决，但第二天上班的时候在测试就好了。让人完全没辙。

两次唯一相同的就是都是查询的视图导致超时，难道是EF对View查询有BUG？但又好像不是，因为曾经我对表查询的时候也出现过这样的问题，囧囧囧啊!!!。

 using (var db = new DataEntities())
 {
      var list = db1.Bas_Document.Where(p => p.CategoryId== CategoryId);
 }

以前用Linq to Sql，虽然也有这样那样的问题，但好歹还算稳定，这回用EF，完全要把人逼疯了，这种不稳定的异常，又没办法详细调试，又不知道他什么时候会抽筋冒出来，实在让人烦躁。

现在只能期待有达人遇到这个问题并能彻底解决了。

posted @ 2011-12-08 02:52 ^Fǘメ生萧条阅读(1728) 评论(5) 编辑收藏举报

刷新页面返回顶部

Entity Framework 4.1/4.2 ，普通查询导致超时，未能解决问题。

公告