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:
Performance Considerations for 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;
}
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.
