Why is EF so slow to get data?

Question

I'm still learning how to use EF but I'm not sure why it's taking me so long to pull data from the database.

My EF statement:

    IEnumerable<DOC_TRACK_MAIN> data;
    Stopwatch st = new Stopwatch();
    st.Start();

    data = context.TABLE1.AsNoTracking().Include(x => x.TABLE2).AsNoTracking().Where(x => startDate <= DbFunctions.TruncateTime(x.LAST_UPDATED_DATE) && endDate >= DbFunctions.TruncateTime(x.LAST_UPDATED_DATE));            

    var result = data.Where(x => x.TABLE2.MAP_TYPE_CODE.Trim().ToUpper() == reportCode.Trim().ToString()).ToList();
    st.Stop();
    var cnt = result.Count();
    var tm = st.Elapsed;
    return result;

When I test a similar query in TOAD it takes about 5 seconds..

How long does the EF query take to execute? Also, did you try to log actual query produced by EF? — Zoran Horvat, Aug 27 '16 at 23:40
It's taking minutes to execute, if it doesn't give me an out of memory exception. — John Doe, Aug 27 '16 at 23:45
Refer to this post http://stackoverflow.com/questions/23804783/log-queries-executed-by-entity-framework-dbcontext - you can use `DbContext.Database.Log` to sniff queries as they go to the database. See if this query is truly joining with `TABLE2` or it ignores the Include call - that is what may have happened. — Zoran Horvat, Aug 27 '16 at 23:49
OK I'm still playing with that to get the output. I'm not sure about ignoring the include call. When I do get data it is the TABLE1 with a nav property of TABLE2 with its data in it. Problem is that it's taking 1:14 to return 478 items back into a list. — John Doe, Aug 28 '16 at 00:01
It's an object. Should I be seeing the same object over and over again as I go through the nav properties? ie. table1.table2 then if I look at table2 there is a table1 property listing all those fields etc... — John Doe, Aug 28 '16 at 00:11
-- Executing at 8/27/2016 8:31:20 PM -04:00 -- Completed in 4139 ms with result: OracleDataReader Does this mean my issue could be with the generating of the list from the query? — John Doe, Aug 28 '16 at 00:32

Sampath · Accepted Answer · 2016-08-28T02:23:52.717

1

Performance Improvement :

OP's Original Query : 1:14 Min

Solution below : 0.06 Min

Improved % : 1800 %

Please rewrite your EF Query as shown below and let us know about the performance now.

context.Configuration.LazyLoadingEnabled = false;//to remove lazy loading for this query only

data = context.TABLE1.Where(x => startDate <= 
DbFunctions.TruncateTime(x.LAST_UPDATED_DATE) && endDate >= 
DbFunctions.TruncateTime(x.LAST_UPDATED_DATE)).Include(x => x.TABLE2);   

var result = data.Where(x => x.TABLE2.MAP_TYPE_CODE.Trim().ToUpper() == 
reportCode.Trim().ToString()).AsNoTracking().ToList();

edited Aug 28 '16 at 02:23

answered Aug 28 '16 at 00:22

Sampath

63,341
64
307
441

I want to return all of the contents of table1 and table 2. – John Doe Aug 28 '16 at 00:28
Updated.Please see now. – Sampath Aug 28 '16 at 00:31
is that fast now ? – Sampath Aug 28 '16 at 00:33
Getting syntax error on last line. I will need to take a look at that. – John Doe Aug 28 '16 at 00:36
Now I need to understand what you did so I can do that moving forward. – John Doe Aug 28 '16 at 00:39
This couldn't be true. OP is using eager loading, so lazy loading has nothing in common with the issue. Most likely the problem is in this declaration `IEnumerable data`. All you need is to change `IEnumerable` to `IQueryable`. – Ivan Stoev Aug 28 '16 at 08:13
@IvanStoev We cannot say it by looking at OP's query.B'cos he didn't put the models.Let's say if `TABLE1` has lot of `navigational properties`.Then what will happen ? All the properties will load with the data if we didn't disable the `LazyLoading` for that query.That is the case here I think.That's why this much of performance gain. – Sampath Aug 28 '16 at 08:19
And may I ask **why** will they load? **Lazy** means they will load when some code tries to access the property, but there is no such code. No, this is not a good direction for EF learners. The key is `IQueryable` (db query) vs `IEnumerable` (in memory query). It's obvious that OP thinks his code is executing a single db query retrieving 478 records, while in reality it's taking much more records from the database and filtering them in memory (the second `Where`). – Ivan Stoev Aug 28 '16 at 08:39
@IvanStoev OK,then can you tell me how above solution gave over `1800%` gain ? And thanks a lot for the detailed explanation. – Sampath Aug 28 '16 at 09:04
The only logical explanation is that `Include` somehow has been ignored in the original query, so the second query `Where` was causing lazy load of the `TABLE2` navigation property. So the fix could be actually how you rewrote the query (moving the `Include` to a different place in the chain, removing `AsNoTracking` etc.), not turning the lazy load off. The full EF log would easily show what was the case. In any case, what OP claims doesn't change the [Cargo cult programming](https://en.wikipedia.org/wiki/Cargo_cult_programming) style of the answer. Btw, your code does not even compile :) – Ivan Stoev Aug 28 '16 at 10:10
hmm..who will expect to compile SO editor code snippets ? :D But OP got the idea and sorted out his issue.But I'm really appreciate your feedback here.We have to think so many ways even though we have a good result. Which is very good :) Have a nice day to you @IvanStoev :) – Sampath Aug 28 '16 at 11:04
1

Same to you @Sampath :) – Ivan Stoev Aug 28 '16 at 11:25
So that I understand I had several things wrong with my original query. 1. I used IEnumerable instead of IQuerable this means memory vs db query processing? 2. I would still like to understand how see if the include is missing? – John Doe Aug 28 '16 at 12:31
1

FYI for the record I went back to my original query. Around 6 secs if I change IEnumerable to IQueryable and set LazyLoading to false. 9 seconds if I only change IEnumerable to IQueryable. – John Doe Aug 28 '16 at 12:41

Why is EF so slow to get data?

1 Answers1