CODEWITHSUNDEEP
c#sqlentity-framework.net-4.0
Gab
Gab

Reputation: 5784

Entity-Framework multiple table query

My code is taking about 3 seconds to execute for 60 employes which is horrible performance. I would like my code to run in about 0.5 seconds max. I have a method that require 5 tables in my database. Since you can only .include("AdjescentTable") in your queries, I have to make 3 queries, take their result and add them to my Employee.

var feuilleDeTemps = from fdt in context.FT.Include("FTJ") where
(fdt.ID_Employe == employe.ID_Employe) &&
(fdt.DateDepart <= date) &&
(fdt.DateFin >= date)
select fdt;

var horaireEmploye = from h in context.HR
where h.ID_Employe == employe.ID_Employe
select h;

var congeCedule = from cc in context.CC.Include("C")
where (cc.ID_Employe == employe.ID_Employe &&
cc.Date <= dateFin &&
cc.Date >= dateDebut)
select cc;

Employe.FeuilleDeTemps = feuilleDeTemps;
Employe.horaireEmploye = horaireEmploye;
Employe.congeCedule = congeCedule;

return Employe;

Its taking about 0.7 seconds per 60 execution of the 3 query above and my database doesn't have a lot of rows. For a set of theses 3 query I return 1 FT 7 FTJ, 5 HR, 0-5 CCand 0-5 C. There are about 300 rows in FT, 1.5k row in FTJ, 500 row in HR, 500 row in CC and 500 row in C.

Of course these aren't the real names but I made em shorter for clearer text.

I used DateTime.Now and TimeSpans to determine the time of each query. If I run the 3 queries directly on SQL Server they take about 300 milliseconds.

Here are my SQL queries:

Select e.ID_Employe, ft.*, ftj.* FROM Employe e
INNER JOIN FeuilleDeTemps ft
ON e.ID_Employe = ft.ID_Employe
INNER JOIN FeuilleDeTempsJournee ftj
ON ft.ID_FeuilleDeTemps = ftj.ID_FeuilleDeTemps
WHERE ft.DateDepart >= '2011-09-25 00:00:00.000' AND ft.DateFin <= '2011-10-01 23:59:59.000'

Select e.ID_Employe, hr.* FROM Employe e
INNER JOIN HoraireFixeEmployeParJour hr
ON hr.ID_Employe = e.ID_Employe

Select e.ID_Employe, cc.* FROM Employe e
INNER JOIN CongeCedule cc
ON cc.ID_Employe = e.ID_Employe
INNER JOIN Conge c
ON c.ID_Conge = cc.ID_Conge

We use WCF, Entity Framework and LINQ

Why is this taking so much time on Entity Framework and how can I improve it?

Upvotes: 2

Views: 2254

Answers (1)

Jim Wooley
Jim Wooley

Reputation: 10418

A bunch of questions with no answers:

Are you sure you need all of the fields you are selecting to do the work you are wanting? Are there any children that you could lazy load to reduce the number of up-front queries?

What happens if you run this code several times during a session? Does it increase in performance over time? If so, you may want to consider changing some of your queries to use Compiled Query so that EF doesn't need to repeatedly parse the expression tree into TSQL each time (note: With 4.2, this will be done for you automatically).

I assume you have profiled your application to make sure that no other queries are being run that you aren't expecting. Also, I expect that you have run the profile trace through the query analizer to make sure the appropriate indexes exist on your tables.

Upvotes: 2

Related Questions