How would I speed up my asp.net mvc application?

Question

I'm currently working on a project where I must pull data from a database and display them onto tables in my view. I have all this working, but it takes about 10-15 seconds to load up the page. If possible I would like to decrease this time.

I believe the problem lies with obtaining the information from the database. There are many items I pull from the database, and I believe there is possibly a better way of doing so.

Controller:

 public class HomeController : Controller
{
    private RestoreDBEntities db = new RestoreDBEntities();

    public ActionResult Index()
    {
        W6ViewModel viewModel = new W6ViewModel();
        viewModel.engineers = db.W6ENGINEERS.OrderBy(w => w.Name).Include(w => w.W6CALENDARS).ToList();
        viewModel.tasks = db.W6TASKS.ToList();

        return View(viewModel);
    }

    public ActionResult Details(int? id)
    {
        if (id == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }
        W6ENGINEERS w6ENGINEERS = db.W6ENGINEERS.Find(id);
        if (w6ENGINEERS == null)
        {
            return HttpNotFound();
        }
        return View(w6ENGINEERS);
    }

    [HttpPost]
    public ActionResult Filter(FormCollection collection)
    {
        string city = collection["city"];

        W6ViewModel viewModel = new W6ViewModel();
        viewModel.engineers = db.W6ENGINEERS.Where(w => w.City == city).Include(w => w.W6CALENDARS).ToList();
        viewModel.tasks = db.W6TASKS.Where(w => w.City == city).Include(w => w.W6CALENDARS).ToList();

        return View("Index", viewModel);
    }

View:

@model WebApplication1.ViewModel.W6ViewModel

@{
    ViewBag.Title = "Dispatcher";
    Layout = "~/Views/Shared/_Layout.cshtml";
}


<section id="fields">
    <h3 id="field_filter"><strong>Filters</strong></h3>
    <h3 id="field_engineer"><strong>Engineers</strong></h3>
    <h3 id="field_task"><strong>Tasks</strong></h3>
</section>

<section id="filters">
    @using (Html.BeginForm("Filter", "Home", FormMethod.Post))
    { 
        <input name="city" id="city" type="text" maxlength="15" title="City" value    ="City" style="color:#888;" 
         onfocus ="inputFocus(this)" onblur="inputBlur(this)" />
         <input id="submit" type="submit" value="Submit" />`enter code here`
    }
 </section>

 <section id="engineers">
     <table class="table-condensed table-striped">
        <tr>
            <th>
                Name
            </th>
            <th>
                Phone number
            </th>
        <th>
            City
        </th>
        <th>
            Region
        </th>
        <th>
            Availability Factor
        </th>
    </tr>

        @foreach (var item in Model.engineers)
        {
            <tr>
                <td>
                    @Html.ActionLink(item.Name, "Details", new { id = item.W6Key })
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.MobilePhone)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.City)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Region)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.AvailabilityFactor)
                </td>
            </tr>
        }
    </table>
</section>

<section id="work">
    <table class="table-condensed table-striped">
        <tr>
            <th>
                Job ID
            </th>
            <th>
                Skills 
            </th>
            <th>
                Address
            </th>
        </tr>

    @foreach(var item in Model.tasks)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.City)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.IsScheduled)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.IsPartsNotUsed)
            </td>
        </tr>
    }
</table>

Webconfig:

   <?xml version="1.0" encoding="utf-8"?>

<configuration>
<configSections>
<section name="entityFramework"   type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework,  Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-WebApplication1-20140611092404.mdf;Initial Catalog=aspnet-WebApplication1-20140611092404;Integrated Security=True" providerName="System.Data.SqlClient" />
    <add name="RestoreDBEntities" connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=T520-R9K0H1K\SQLEXPRESS;initial catalog=RestoreDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <system.web>
     <authentication mode="None" />
    <compilation debug="true" targetFramework="4.5.1" />
    <httpRuntime targetFramework="4.5.1" />
  </system.web>

  <system.webServer>
    <urlCompression doDynamicCompression="true" doStaticCompression="true" dynamicCompressionBeforeCache="true"/>
  </system.webServer>

  <system.webServer>
    <modules>
      <remove name="FormsAuthenticationModule" />
    </modules>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35"    />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-5.1.0.0" newVersion="5.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.5.2.14234" newVersion="1.5.2.14234" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

Thanks much in advance!

EDIT:

so after installing DotTrace, and using it to diagnose my project, it seems it is my calls to the database that are causing the long load times (my opinion, though I have no experience with DotTrace)

I have added a screenshot, if anyone can confirm/propose a solution that would be great.

Answer 1

One thing you can do, is cache your output. This will save time in subsequent requests. You can use the output cache attribute on whichever action method you want to cache.

[OutputCache(Duration = 3600, Location = System.Web.UI.OutputCacheLocation.ServerAndClient)]

The duration is how long you want to cache the output in seconds, and the location is obv where you want to cache it.

You can also use SqlCacheDependency to cache requests until the data has been updated. Using the output cache attribute with the sql dependency is probably more suited to your problem. Here's a walkthrough on how to do it on msdn - http://msdn.microsoft.com/en-us/library/e3w8402y(v=vs.140).aspx.

There are also other things you can do to speed up your mvc app. I've written a brief post about some of the things you can do. Check them out here - http://www.paulsodimu.co.uk/Post/How-to-speed-up-your-MVC-web-application