title: ASP.NET MVC speed up query on larger table
body: <p>I am making a booking &amp; invoicing web app in ASP.NET MVC5.
  On my index page for bookings (main page of app) I have a search form and a table displaying the contents of a PagedList.</p>
  
  <p>There are about 100.000 records in the bookings table at the moment. Most of them have an Invoice with status DONE. Most queries are for bookings without invoice or with ivoice in status PENDING. 
  Loading the index page takes about 8 seconds. (empty filter => query all bookings) Which is way too long...</p>
  
  <p>Most of the time is spend on fetching the data from the DB. What are my options to speed this query up ?</p>
  
  <p>Repo:</p>
  
  <pre><code>public IQueryable&lt;Booking&gt; GetByFilter(SearchBookingViewModel model)
  {
      var bookings = _context.Bookings.Include(b =&gt; b.Invoice).Include(b =&gt; b.Customer).Include(b =&gt; b.Origin).Include(b =&gt; b.Destination);
      if (!model.IncludeInvoiced)
      {
          bookings = bookings.Where(b =&gt; b.InvoiceID == null || b.Invoice.Status == InvoiceStatus.PENDING);
      }
      if (!string.IsNullOrWhiteSpace(model.Awb))
      {
          bookings = bookings.Where(b =&gt; b.Awb.ToUpper().Contains(model.Awb.ToUpper()));
      }
      if (!string.IsNullOrWhiteSpace(model.ClientRef))
      {
          bookings = bookings.Where(b =&gt; b.ClientRef.ToUpper().Contains(model.ClientRef.ToUpper()));
      }
      if (!string.IsNullOrWhiteSpace(model.CustomerCode))
      {
          bookings = bookings.Where(b =&gt; b.Customer.CustomerCode == model.CustomerCode);
      }
      if (!string.IsNullOrWhiteSpace(model.OriginCode))
      {
          bookings = bookings.Where(b =&gt; b.Origin.AirportCode == model.OriginCode);
      }
      if (!string.IsNullOrWhiteSpace(model.DestinationCode))
      {
          bookings = bookings.Where(b =&gt; b.Destination.AirportCode == model.DestinationCode);
      }
      if (model.FromDate.HasValue)
      {
          bookings = bookings.Where(b =&gt; b.BookingDate &gt;= model.FromDate);
      }
      if (model.ToDate.HasValue)
      {
          bookings = bookings.Where(b =&gt; b.BookingDate &lt;= model.ToDate);
      }
  
      return bookings.OrderByDescending(b =&gt; b.BookingID);
  }
  </code></pre>
  
  <p>Booking:</p>
  
  <pre><code>public class Booking
  {    
      public int BookingID { get; set; }
      public int? InvoiceID { get; set; }
      public int CustomerID { get; set; }  
      public int OriginID { get; set; }    
      public int DestinationID { get; set; }
      public BookingType BookingType { get; set; }
      public string Awb { get; set; }
      public string ClientRef { get; set; }
      public DateTime BookingDate { get; set; }
      public int NumberOfPieces { get; set; }
      public int GrossWeight { get; set; }
      public int? VolumetricWeight { get; set; }
      public decimal? Rate { get; set; }
      public decimal TotalCharges { get; set; }
      public string Info { get; set; }    
      public string Location { get; set; }
  
      // Navigation
      public virtual Airport Origin { get; set; }
      public virtual Airport Destination { get; set; }
      public virtual ICollection&lt;BookingDetail&gt; BookingDetails { get; set; }
  
      public virtual Customer Customer { get; set; }
      public virtual Invoice Invoice { get; set; }
      public virtual Pod Pod { get; set; }
  }
  </code></pre>
  
  <p>Controller:</p>
  
  <pre><code>public ActionResult Search(Filter filter)
  {
      filter.Bookings = _db.Bookings.GetByFilter(filter).ToPagedList(filter.Page, filter.NumberPerPage);
  
      return PartialView("_SearchResultsPartial", filter);
  }
  </code></pre>
  
  <p>ToPagedList does .Count() + .Skip().Take() on the IQueryable</p>