mysql 查询视图慢,对数据库视图EF查询很慢

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:

but the amount of information is a little bit overwhelming, for me. That at least, so I need some directions.

public List 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 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;

}

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

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

All the time is spent on that .ToList() call, but where exactly?

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 ;-)

So the sloppy performance doesn't seem, to me, related to some "EF magic object's trees materialization and connections behind the scenes"

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

The unfiltered View returns 54.000 records.

解决方案

Ok, stop whining.

I have exported the code on the production server and tested against SQL Server 2005.

No delay anymore, the query gets executed almost instantly.

So, after all, this issue seems only related to my local SQL Server 2008 Express. (what's an annoying problem)