slow entity framework query , but fast Generated SQL

Question

please consider this model

it's for a fitness center management app

ADHERANT is the members table

INSCRIPTION is the subscription table

SEANCE is the individual sessions table

the seance table contain very fews rows (around 7000)

now the query :

  var q = from n in ctx.SEANCES
                select new SeanceJournalType()
                        {
                            ID_ADHERANT = n.INSCRIPTION.INS_ID_ADHERANT,
                            ADH_NOM = n.INSCRIPTION.ADHERANT.ADH_NOM,
                            ADH_PRENOM = n.INSCRIPTION.ADHERANT.ADH_PRENOM,
                            ADH_PHOTO = n.INSCRIPTION.ADHERANT.ADH_PHOTO,
                            SEA_DEBUT = n.SEA_DEBUT
                        };

                var h = q.ToList();

this take around 3 seconds wich is an eternity, the same generated SQL query is almost instantaneous

SELECT 
1 AS "C1", 
"C"."INS_ID_ADHERANT" AS "INS_ID_ADHERANT", 
"E"."ADH_NOM" AS "ADH_NOM", 
"E"."ADH_PRENOM" AS "ADH_PRENOM", 
"E"."ADH_PHOTO" AS "ADH_PHOTO", 
"B"."SEA_DEBUT" AS "SEA_DEBUT"
FROM   "TMP_SEANCES" AS "B"
LEFT OUTER JOIN "INSCRIPTIONS" AS "C" ON "B"."INS_ID_INSCRIPTION" = "C"."ID_INSCRIPTION"
LEFT OUTER JOIN "ADHERANTS" AS "E" ON "C"."INS_ID_ADHERANT" = "E"."ID_ADHERANT"

any idea on what's going on please, or how to fix that ?

thanks

What version of EF are you on? Also note that `ToList` not only executes the query, but also consumes the result (reads the values and populates the objects), so 3 sec for 7K records might be ok. — Ivan Stoev, Mar 07 '17 at 17:49
the thing is that the equivalent generated SQL query is instantaneous ? — user2475096, Mar 07 '17 at 18:49
How do you measure it? Again, executing the query is one, consuming the result (reading the returned records) is another story. — Ivan Stoev, Mar 07 '17 at 19:18
You're not just running a single query here. You're also running queries against Inscription and Inherent because you are accessing the sub-objects. However, each of these queries are run individually. This is called the N + 1 problem. You run the first query, which returns 7000 rows, but when you access n.INSCRIPTION.INS_ID_ADHERANT this generates a query based on the linked table. And, since you have two different sub-queries (INSCRIPTION and INSCRIPTION.ADHERANT) this results in 14,000 additional queries performed one at a time, rather than a single query that returns 7000 rows. — Erik Funkenbusch, Mar 07 '17 at 22:25
You fix this by using .Include() to include all the sub-tables in the same query. This is because you have lazy loading enabled. — Erik Funkenbusch, Mar 07 '17 at 22:27
@ErikFunkenbusch No, this SQL query is the only query. It returns all data the LINQ query requests. — Gert Arnold, Mar 07 '17 at 23:48
@GertArnold - not if lazy loading is enabled, and no specific actions are taken to include the sub-tables. — Erik Funkenbusch, Mar 08 '17 at 19:42
@GertArnold - The SQL query shown above, yes, returns all the data. The LINQ query, does not if using lazy loading. Also, note that the SQL shown pulls records from "TMP_SEANCES" while the Linq query pulls from Seances (assuming this isn't just a name mapping quirk), so they're not quite the same anyways. — Erik Funkenbusch, Mar 08 '17 at 19:52
@ErikFunkenbusch Really, no lazy loading is involved here. The LINQ query is a projection to an unmapped type and it is translated into one SQL statement that produces all required data at once, hence the joins. I agree that the name `TMP_SEANCES` is a bit weird (esp. since the other names match), but I do think the entity `SEANCE` is mapped to it, I mean, why would OP claim they show the generated SQL? — Gert Arnold, Mar 08 '17 at 20:13
sorry guys for the confusion, TMP_SEANCE actually is the same as SEANCE, and yes i disable explicitly the lazyloading and the proxies generation just after the context creation — user2475096, Mar 08 '17 at 23:48

score 0 · Answer 1 · edited May 23 '17 at 11:46

it needs some research to optimize this :

if you neglect the data transfer from the db to the server then as Ivan Stoev Suggested calling the ToList method is the expensive part

as for improving the performance it depends on your needs:

1.if you need add-delete functionality at the server side it is probably best to stick with the list

2.if no need for add-delete then consider ICollection ,, or even better

3.if you have more conditions which will customize the query even more best use IQuerable

customizing the query like selecting a single record based on a condition :

var q = from n in ctx.SEA.... // your query without ToList()
q.where(x=>"some condition") //let`s say x.Id=1

only one record will be transferred from the database to the server

but with the ToList Conversion all the records will be transferred to the server then the condition will be calculated

although it is not always the best to use IQuerable it depends on your business need

for more references check this and this

slow entity framework query , but fast Generated SQL

1 Answers1