Entity Framework Query is too slow

.net asp.net-core c# entity-framework entity-framework-core

Question

I have to put a complex query on your database. But the query ends at 8000 ms. Do I do something wrong? I use .net 1.1 and Entity Framework core 1.1.2 version.

var fol = _context.UserRelations
                  .Where(u => u.FollowerId == id && u.State == true)
                  .Select(p => p.FollowingId)
                  .ToArray();

var Votes = await _context.Votes
                          .OrderByDescending(c => c.CreationDate)
                          .Skip(pageSize * pageIndex)
                          .Take(pageSize)
                          .Where(fo => fol.Contains(fo.UserId))
                          .Select(vote => new
                {
                    Id = vote.Id,
                    VoteQuestions = vote.VoteQuestions,
                    VoteImages = _context.VoteMedias.Where(m => m.VoteId == vote.Id)
                        .Select(k => k.MediaUrl.ToString()),

                    Options =  _context.VoteOptions.Where(m => m.VoteId == vote.Id).Select( ques => new
                    {
                        OptionsID = ques.Id,
                        OptionsName =  ques.VoteOption,
                        OptionsCount =  ques.VoteRating.Count(cout => cout.VoteOptionsId == ques.Id),
                    }),
                    User = _context.Users.Where(u => u.Id == vote.UserId).Select(usr => new
                    {
                        Id = usr.Id,
                        Name = usr.UserProperties.Where(o => o.UserId == vote.UserId).Select(l => l.Name.ToString())
                            .First(),
                        Surname = usr.UserProperties.Where(o => o.UserId == vote.UserId)
                            .Select(l => l.SurName.ToString()).First(),
                        ProfileImage = usr.UserProfileImages.Where(h => h.UserId == vote.UserId && h.State == true)
                            .Select(n => n.ImageUrl.ToString()).First()
                    }),
                    NextPage = nextPage
                }).ToListAsync();
1
0
1/7/2018 2:02:13 PM

Popular Answer

Have a look at the SQL queries you generate to the server (and results of this queries). For SQL Server the best option is SQL Server Profiler, there are ways for other servers too.

  • you create two queries. First creates fol array and then you pass it into the second query using Contains. Do you know how this works? You probably generate query with as many parameters as many items you have in the array. It is neither pretty or efficient. It is not necessary here, merge it into the main query and you would have only one parameter.

  • you do paginating before filtering, is this really the way it should work? Also have a look at other ways of paginating based on filtering by ids rather than simple skipping.

  • you do too much side queries in one query. When you query three sublists of 100 items each, you do not get 300 rows. To get it in one query you create join and get actually 100*100*100 = 1000000 rows. Unless you are sure the frameworks can split it into multiple queries (probably can not), you should query the sublists in separate queries. This would be probably the main performance problem you have.

  • please use singular to name tables, not plural

  • for performance analysis, indexes structure and execution plan are vital information and you can not really say much without them

2
1/7/2018 2:03:25 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow