Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
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
Updated 19-Apr-19 9:19am
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

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.
 
Share this answer
 
Comments
Maciej Los 16-May-19 5:14am    
5ed!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900