How to reduce Entity Framework Linq query execution time?

Question

I am using Entity Framework in my ASP.NET MVC application, and I am facing an issue in loading data from SQL Server via LINQ. My query returns the result in 4 seconds, but I need to get in less time, still searching for better solution.

Here is my linq query:

var user =
    context.CreateObjectSet<DAL.ProductMaster>()
        .AsNoTracking()
        .Include("Product1").AsNoTracking()
        .Include("Product2").AsNoTracking()
        .Include("Product3").AsNoTracking()
        .Include("Product4").AsNoTracking()
        .Include("Product5").AsNoTracking()

        .Where(x => x.Id == request.Id)
         ).FirstOrDefault();

Check if you have created indexes on Id and OrganizationId. Indexes can speed up this situations. — Tony, Apr 20 '17 at 13:26
Are you sure that you need _ALL_ of those other tables and data as that is going to generate quite a lot of JOIN operations, which can be expensive. Additionally, you can replace your `Where()` clause with `FirstOrDefault()` to clean things up a bit and you likely don't need the `AsNoTracking()` call for each individual Include(). — Rion Williams, Apr 20 '17 at 13:27
Try writing the sql that you want an executing it against the db manually. If that runs well try to optimize your linq — ste-fu, Apr 20 '17 at 13:27
Consider using the `.Select()` operator to grab *exactly* what you want, instead of this hodgepodge of what looks like a large proportion of your database. — Kirk Woll, Apr 20 '17 at 13:29
Get the data in multiple queries + remove `organizationsList.Count()!= 0` (and, not performance-related: remove all but one AsNoTracking calls). — Gert Arnold, Apr 20 '17 at 13:40
after splitting queries into 3 parts, and have no idea on merging the query results correctly. — Surendar, Apr 20 '17 at 13:59
Please update your question with your new code. You probably should have used the `Load` statement. — Gert Arnold, Apr 20 '17 at 14:04

Fredrik · Answer 1 · 2017-04-20T14:30:40.923

0

No need to get multiple entities fulfilling the predicate in Where() only to then select the first one; simply call FirstOrDefault() directly.

if (request == null || organizationsList == null || !organizationsList.Any())
    return; 

var user = context.CreateObjectSet<DAL.User>()
                  .AsNoTracking()
                  .Include("UsersinPrograms.FollowUps")
                  .Include() ...
                  .FirstOrDefault(x => x.Id == request.userId && organizationsList.Contains(x.OrganizationId)));

Also, remove the Include()'s that you don't need.

edited Apr 20 '17 at 14:30

answered Apr 20 '17 at 14:24

Fredrik

2,247
18
21

thanks for the code,hard luck am getting the same result like my previous result. – Surendar Apr 20 '17 at 14:34
`context.CreateObjectSet().AsNoTracking()` ... seems weird, don't you want something like `context.Users..AsNoTracking()` ... ? you're users should already be created and now you're querying/filtering existing data (not created new dbsets). Or perhaps this is only a badly named method? – Fredrik Apr 20 '17 at 14:35
seems its must to initiate the table structure, so can't – Surendar Apr 20 '17 at 14:39

ΩmegaMan · Answer 2 · 2017-04-21T15:05:58.517

0

For this and any large scale business logic operations one should create Stored Procedures instead of basing them in Linq. Then in EF one creates a DTO object to handle the result and consumes the procedure in EF.

At that point the speed gained or lost is up to how the procedure's sql has been structured, with an eye on indexes used within the database to possibly provide an enhancement on speed.

Linq SQL is really boilerplate and not designed for speed.

I have done this with multiple EF projects.

Also one can build their linq query in Linqpad and then switch to view the SQL generated. That would provide an idea on how to structure one's SQL in a stored proc.

Also you could look into using SQL CTEs (Common Table Experssions) to build your query one step at a time until the data is just as needed.

edited Apr 21 '17 at 15:05

answered Apr 20 '17 at 14:31

ΩmegaMan

29,542
12
100
122

Thanks for the instruction, have previously tried in Stored procedure and views but due to over number of joins can't able to get the expecting result.thats why looking to fix in EF. – Surendar Apr 20 '17 at 14:37
@Surendar I would build my linq query in Linqpad and then switch to view the SQL generated. That would give me an idea on how to structure my SQL in a stored proc. Also you could look into using SQL CTEs to build your query one step at a time until the data is just as needed. HTH – ΩmegaMan Apr 20 '17 at 14:41
OmegaMan, This makes sense, will try this in Linqpad, thanks. – Surendar Apr 20 '17 at 14:51
1

@Surendar If you do a `.ToString()` on a `IQueryable` object generated from EntityFramework it also will give you the query that will be sent to the server if you would like to see the the query your LINQ statement is generating. You will need to replace the `.FirstOrDefault()` in your example query with the `.ToString()` for it to work. – Scott Chamberlain Apr 20 '17 at 15:24
-1 because both generated queries and stored procedures go to the query optimizer and get a query execution plan. That plan determines whether a table's index is used or not and in which order the tables are accessed and combined. The optimizer's choices are the primary performance consideration. "how the procedure has been structured with indexes" is completely incorrect. – Amy B Apr 21 '17 at 13:16
@DavidB So you imply that any gosh darn SQL is equal to the finest crafted SQL? With that reasoning being based on the opinion that everything that goes through a SQL Optimizer makes all equal in the end. Wow... – ΩmegaMan Apr 21 '17 at 13:57
The boilerplate sql that EF or LtS generates is better than 99% of the "finest crafted" SQL humans are writing. However, to your question: no - my explicit statement (not implication) is that the text of the sql matters very little for performance if the choices of the optimizer (which are based on the table and index definitions) are ignored. And also, putting text into a stored procedure doesn't solve performance problems. And lastly, stored procedures are not "structured with indexes". – Amy B Apr 21 '17 at 14:24
@DavidB I have updated the sentence to not have such lexical ambiguity concerning the important of indexes. I stand behind my post as a helpful answer, if not suggestion to having the OP investigate the issue. – ΩmegaMan Apr 21 '17 at 15:07

How to reduce Entity Framework Linq query execution time?

2 Answers2