2

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?

2
  • Your question would probably have been clearer if you hadn't abbreviated everything to three letters or less.. :-P Commented Oct 18, 2011 at 19:57
  • (Without knowing anything about the framework...) Is there some reason you can't do multiple joins or includes in your framework query? Also, what about doing a single view that you can then create your entities off of? If you're not updating anything, that might simplify a fair bit... Commented Oct 18, 2011 at 20:05

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

4 Comments

I need all the fields, i cant lazyload beacause i have to return the data to my application. The code you see is in the WCF service, if i run the code servals times, it reduce from about 0.2 seconds but its not constant.
Try Compiled Query on your LINQ/EF and see if that helps. Part of your perf issue may be the query evaulation by EF and query plan evaulation by SQL. Afraid there may be no silver bullet here and you'll need to do a fair amount of profiling on this one.
il will sure try Compiled Query and do more profiling, thnx for the hint ^^
good call on the compiled querry, it improved my performances. Bad thing, it still take about 1.7 seconds loading time so still slow but much better

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.