对数据库视图EF查询很慢 [英] EF query against Database View is very slow
问题描述
我有一个简单的Web应用程序，使用MVC和EF4.0。我使用VS 2010和SQL Server 2008前preSS我的工作站上本地安装。
I have a simple web application, using MVC and EF4.0. I use VS 2010 and SQL Server 2008 Express installed locally on my workstation.
我有这种缓慢的功能，我尝试过的一些技巧，以加快步伐，但没有成功。我一直有一个一目了然这个链接：
I have this slow function, I have tried some tricks to speed it up, but with no success. I have had a glance to this links:
<一个href=\"http://www.asp.net/web-forms/tutorials/continuing-with-ef/maximizing-performance-with-the-entity-framework-in-an-asp-net-web-application\"相对=nofollow>最大化实体框架4.0 ASP.NET Web应用程序性能：微软官方ASP.NET站点
为EF5 性能注意事项
但信息量有点势不可挡，对我来说。至少，所以我需要一些方向。
but the amount of information is a little bit overwhelming, for me. That at least, so I need some directions.
public List<VRapportiCT> ViewRapportiToList(string codArticolo, DateTime startDate, DateTime endDate)
{
// RapportiEntities : ObjectContext
RapportiEntities context = new RapportiEntities();
//context.VRapportiCT.MergeOption = MergeOption.NoTracking; // No improvement
// startDate = 01/01/2013
// endDate = 31/12/2013
List<VRapportiCT> myList = context.VRapportiCT
.Where(r => r.DCodArt == codArticolo && r.DStorico >= startDate && r.DStorico <= endDate)
.OrderBy(r => r.DStorico).ToList();
// 1° query Elapsed time: 8 sec.
myList = context.VRapportiCT
.Where(r => r.DCodArt == codArticolo && r.DStorico >= startDate && r.DStorico <= endDate)
.OrderBy(r => r.DStorico).ToList();
// 2° query Elapsed time: 8 sec.
return myList;
}
的SQL查询，直接在数据库中执行是非常快的，其实小于0.5秒（我与SQL Server事件探查测量它）。
The SQL query, executed directly on the DB is very fast, actually less than 0,5 second (I have measured it with SQL Server Profiler).
我用VS 2010在调试模式下目视检查性能（也编译在Release模式应用程序和浏览器直接测试调用不会带来任何改善）。
I use VS 2010 in debug mode to "visually check" the performance (but also compiling the app in Release mode and testing directly the call in the browsers doesn't bring any improvements).
所有的时间都花费在 .ToList（）电话，但究竟在何处？
All the time is spent on that .ToList() call, but where exactly?
PS。该查询只返回一个非常小数量的记录，说10条记录，从一个观点，即（未过滤）当然包含了更多的记录; - ）
PS. The query returns only a very small number of records, say 10 records, from a view that (unfiltered) contains of course a lot more records ;-)
所以马虎的表现似乎并没有对我来说，相关的一些的EF神奇对象的树木，物化与幕后连接的
So the sloppy performance doesn't seem, to me, related to some "EF magic object's trees materialization and connections behind the scenes"
修改
数据库视图T-SQL code：
EDIT The DB View T-SQL code:
SELECT TOP (100) PERCENT L.KLinea, LTRIM(RTRIM(L.DLinea)) AS DLinea, LTRIM(RTRIM(R.DCodArt)) AS DCodArt, LTRIM(RTRIM(R.DDescArt)) AS DDescArt, N.KNota, N.DNota,
T.DStorico, CAST(DATEPART(day, T.DStorico) AS varchar) + '/' + CAST(DATEPART(month, T.DStorico) AS varchar) + '/' + CAST(DATEPART(year, T.DStorico) AS varchar)
AS Data, REPLACE(LTRIM(RTRIM(U.DTurno)), 'Turno', 'Lettera') AS Lettera, U.KTurno,
FROM dbo.TRapportiCT AS T INNER JOIN
dbo.TPersonale AS P ON T.KPersona = P.KPersona INNER JOIN
dbo.TTurni AS U ON T.KTurno = U.KTurno INNER JOIN
dbo.RRapNotCod AS R ON T.KRapporto = R.KRapporto INNER JOIN
dbo.TLinea AS L ON R.KLinea = L.KLinea INNER JOIN
dbo.TNoteCT AS N ON R.KNota = N.KNota
WHERE (P.KRuolo = 2)
ORDER BY T.DStorico, N.KOrdine
未过滤视图返回54.000的记录。
The unfiltered View returns 54.000 records.
推荐答案
好吧，停止抱怨。
我已经出口生产服务器上的code和针对SQL Server 2005进行测试。
I have exported the code on the production server and tested against SQL Server 2005.
没有延迟了，查询被执行几乎瞬间。
No delay anymore, the query gets executed almost instantly.
所以，毕竟，这个问题似乎只与我的本地 SQL Server 2008中前preSS 。 （什么是一个恼人的问题）
So, after all, this issue seems only related to my local SQL Server 2008 Express. (what's an annoying problem)
这篇关于对数据库视图EF查询很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
