Bad performance in rendering the Web page with EF6+MVC5. Why?

Question

I developed a Web application using .NET framework 4.5.1, MVC 5 and EF 6. The data are stored into a MySQL database. The problem is that the performances are really bad and I can't find the reason. Some pages take several seconds (up to 10 seconds) to load completely!

I have tried to implement some suggestions found on the Web, but they gave little improvement.

As instance, the following Controller gets the list of users from the database and passes it to the View.

public class UserController : Controller
{
    // GET: /User/
    public async Task<ActionResult> Index()
    {
        using (AuthorDbContext db = new AuthorDbContext())
        {
            return View(await db.Users.OrderBy(u => u.Surname)
                                        .ThenBy(u => u.Name)
                                        .Include(u => u.Company)
                                        .Include(u => u.Department)
                                    .ToListAsync());
        }
    }

    // [...]
}

The View prints the users' details into an HTML table (which uses the DataTable plugin of jQuery).

@model IEnumerable<Author.Models.User>
@using Author.Helpers

<div class="geco-button-container">
    @ButtonHelper.CreateButton(Url.RouteUrl("New User"), "Add a new user")
</div>

<table id="longTable" class="longTable">
    <thead>
        <tr>
            <th></th>
            <th>
                @Html.DisplayNameFor(model => model.Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Surname)
            </th>
[...]
        </tr>
    </thead>
    <tbody>
    @foreach (var item in Model) {
        <tr@(item.QuittedOn != null && item.QuittedOn.Value < DateTime.Now ? " class=user_dismissed" : "")>
            <td class="tableButton">
                @ButtonHelper.ViewButton(Url.RouteUrl("View User", new RouteValueDictionary {{ "id", item.id }}), "", "Details")
                @if (item.canBeEdited)
                {
                    @ButtonHelper.EditButton(Url.RouteUrl("Edit User", new RouteValueDictionary {{ "id", item.id }}), "", "Edit")
                    @ButtonHelper.DeleteButton(Url.RouteUrl("Delete User", new RouteValueDictionary {{ "id", item.id }}), "", "Delete")
                }
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Surname)
            </td>
[...]
            <td>
                @if (item.Department != null)
                {
                    @Html.RouteLink(item.Department.Code.ToString(), "View Department", new RouteValueDictionary { { "id", item.DepartmentId } }, new Dictionary<string,object> { { "title", item.Department.Description.ToString() } })
                }
            </td>
        </tr>
    }
    </tbody>
</table>
@section Scripts {
    @Scripts.Render("~/Scripts/DataTables-1.9.4/media/js/jquery.dataTables.min.js")
    @Scripts.Render("~/Scripts/longTable.js")
}

When I visit this page, it takes from 5 to 10 seconds to finish loading the list of 698 users.

I've installed Glimpse, which says that the problem seems to be the rendering of the resulting page.

As you can see, I implemented some tips that should improve the performance, like:

• using RouteLink and RouteUrl instead of ActionLink and ActionUrl
• using async calls
• disabling all view engines except RazorViewEngine (in Global.asax)

I am running the app with the debug mode on my local machine. Someone suggests setting the option debug="false" into web.config, but I have also published it on a Web server where it runs with that option and the problem still remains.

Many people complain about the slowness of the Entity Framework, but they usually talk about hundreds of milliseconds, while in my case it takes several seconds - which is really unacceptable in a Web scenario!

What is the problem? What am I doing wrong?

---

UPDATE

I have installed dotTRACE and this is the result on the page showing the users list.

As you can see, the bottleneck lies into the Microsoft framework.

What is the MappingDataUtilities class? What is its goal? What about its functions referring to stuff like Json, Css, TagName, Selectors, ...?

My code never calls these stuff explicitly. How can I improve their bad performance?

Answer 1

I would strongly recommend decoupling your ViewModel from your DataModel. When those two are the same thing it means exposing everything to your ViewModel even though you only want a handful of fields. I recommend creating a new ViewModel that only contains the field you're interested, and then update your EF query to only select the fields you need instead of doing SELECT * on many tables. I believe that will help a great deal with performance.