Issue with .NET LINQ query preparation

Question

1.00/5 (1 vote)

See more:

, +

We are having an issue in linq query. In select query multiple tables are associated with joins. Where record filters were applied on join section. This query was running fine and giving proper result within time since past 8 to 9 months.
Suddenly it took time around 2 minute to retrieve the record with the same join and consuming 99% CPU.

This query is being used always while user login so its sure that it was running fine in past till issue moment.
Can any one help why this issue occurred suddenly?

var entityList = (from x in db.TableA
                           join y in db.TableB
                           on x.Id equals y.TableC.TableAId into g
                           from d in g.DefaultIfEmpty()
                           where (d.EndDateTime == null && (d == null || d.TableE.PersonID == personId))
                                 && x.EndDateTime == null
                                 && d.TableC.EndDateTime == null
                                 && x.TableE.PersonID == personId
                           select new
                           {
                             Col1 = d != null ? d.TableC.TableD.Id : 0,
                             Col2 = d != null ? d.TableC.Id : 0,
                           }).AsQueryable();

What I have tried:

We have checked indexes of tables.

Posted 19-Apr-19 5:53am

Bhargav Software

Updated 19-Apr-19 9:19am

Add a Solution

Comments

[no name] 19-Apr-19 12:11pm

You're guessing. Only by counting and timing can you point at a particular section of code. Somebody loaded your db with a million duplicates for all we know.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Answer 1 · 2019-04-19T09:19:00

You need to see what the underlying SQL being generated by entity framework is doing. Then, take that same sql and run it through sql server (assumingly) with query analyzer turned on to see where the bottle necks or missing indexes (if any) are.

To log the sql being generated by entity framework you'd need to do something like this. Assuming you are using log4net for example.

var log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
db.Database.Log = log.Info;
// or you could do
db.Database.Log = Debug.WriteLine;

This would then output the generated sql to a log file or the debug output window.

From there, take that sql, run it in management studio, anaylze the query and adjust your linq query from there. Entity framework/linq is notorious for creating some nasty sql that doesn't make sense so I have a feeling a lot of your null checking you are doing in your query is generating a gigantic sql statement that is eating at your CPU.