0

I'm working on an ASP.NET MVC 4 application in which I'm trying to create a dashboard that reads from 2 database views; one view lists buildings with counts and goals, and another lists floors of those buildings with counts and goals. I'm trying to combine them on the dashboard, so I created a datamodel called DashboardView which holds the buildings with the floors in a Children property.

It works fine enough locally, but deployed on a production IIS server it runs incredibly slowly, and I'm not sure if it's due to inefficiencies in the way I'm accessing the data:

using (var db = new MyDBContext())
{
    var views = (from building in db.BuildingViews.OrderBy(x => x.BuildingName) select new { building })
                .AsEnumerable()
                .Select(bldgView => new DashboardView
                {
                    ViewType = "Building",
                    ViewLabel = bldgView.building.BuildingName,
                    CurrentCount = bldgView.building.Count,
                    GoalCount = bldgView.building.Goal,
                    Children = (from floors in db.FloorViews.Where(v => v.BuildingId == bldgView.Building.BuildingId) select new { floor })
                                .AsEnumerable()
                                .Select(floorView => new DashboardView
                                {
                                    ViewType = "Floor",
                                    ViewLabel = floorView.floor.FloorName
                                    CurrentCount = floorView.floor.Count,
                                    GoalCount = floorView.floor.Goal
                                }).ToList()
                }).ToList();

    double totalBldgCount = db.BuildingViews.Select(x => x.Count).Sum();
    double totalGoalCount = db.BuildingViews.Select(x => x.Goal).Sum();
}

Is there a better way to create the data package I'm trying to achieve here, or is the issue possibly related to multiple people hitting the database at the same time?

::EDIT::

I understand now that using .AsEnumerable() could be a culprit of poor performance; however I'm struggling to understand how I can properly construct my DashboardView with child DashboardView objects without it, as this does not work:

var query = (from buildings in db.BuildingViews
            join floors in db.FloorViews on buildings.BuildingId equals floors.BuildingId
            select new DashboardSprinklerView
            {
                ViewType = "Building",
                ViewLabel = building.BuildingName,
                CurrentCount = building.Count,
                GoalCount = building.Goal,
                Children = (floor from floors select new DashboardSprinklerView
                {
                    ....
                }).ToList()
            }).ToList();
Camilo Terevinto
  • 31,141
  • 6
  • 88
  • 120
jessikwa
  • 750
  • 1
  • 8
  • 23
  • How different are the dataset sizes? Your call in the Children section will get run as many times as there are BuildingViews. You may want to break this call out or do a join instead. If prod is substantially larger, this could explode the number of db calls being made. – Ryan Bennett Oct 09 '18 at 19:47
  • @RyanBennett There will be many more floors than buildings. My local db matches the production db. – jessikwa Oct 09 '18 at 19:55
  • Clearly the more efficient way to do this is to have a FK between BuildingViews/FloorViews where you could do a join but I can understand that you may not have control over that. If it's running significantly more slowly on one box than another all things being equal - its probably the hardware itself isn't up to the job. – Ryan Bennett Oct 09 '18 at 20:10
  • Why the `.AsEnumerable()` calls? Are you aware what that does to the query execution? – David Browne - Microsoft Oct 09 '18 at 20:12
  • @RyanBennett if I could do something like `join floors in db.FloorViews on bldgs.BuildingId equals floor.BuildingId` how would I select a child object from this? `Children = (from floor in floors select new DashboardSprinklerView` doesnt work – jessikwa Oct 09 '18 at 20:33
  • @DavidBrowne-Microsoft can you elaborate? – jessikwa Oct 09 '18 at 20:33
  • @jessikwa - See [Why is Entity Framework's AsEnumerable() downloading all data from the server?](https://stackoverflow.com/q/31837028/1260204). Whenever you append that in your method chain it will take everything before it and retrieve it from the database. What you need is a proper query that uses joins and gets executed at the database level at the end. – Igor Oct 09 '18 at 20:39
  • You should open up a query profiler (*using sql server? use Sql Server Profiler*). Then you can see what gets executed database side, when, what is retrieved, and at what cost. – Igor Oct 09 '18 at 20:41
  • Question updated – jessikwa Oct 09 '18 at 20:46
  • You should next probably add a BuildingView Navigation Property to your FloorView Entity to make query easier. It doesn't matter if there's no real Foreign Key in the database. – David Browne - Microsoft Oct 09 '18 at 21:01

0 Answers0