Linq Query slow on host but not on dev machine

Question

I'm running ASP.NET MVC 2 Preview 2 (With VS 2010 Beta 2) using Entity Framework.

Earlier yesterday, for some unknown reason, a single page in my app started to load very slowly on my host.

The problem is, this only occurs on my host and I haven't changed anything for it to load slow.

Here is the action that is loading very slow:

public ActionResult Index()
{
    MyEntitiesContext db = new MyEntitiesContext();
    IEnumerable<City> cities = db.Cities.Where(x => x.Orders.Count != 0).OrderBy(x => x.Name);
    return View(cities);
}

and this is my Index.aspx

       <ul>
         <% foreach (City g in Model){ %>
            <li>
              <%= Html.ActionLink(g, "View", "Cities", new { CityID = g.CityID}, null)%>(<%= g.City %>)
           </li>         
         <% } %>
       </ul>

Now this works perfectly fine on my dev machine, and it worked fine on my host until late last night.

Here are some diagnostics I tested:

1. The code works/loads fine on my dev machine using my dev machine's SQL database
2. When I switch the connection string on my dev machine app to my hosts sql server, the code takes forever to load
3. When I run the LINQ against my dev machine SQL server using LINQ Pad it runs quick (.3 seconds) and when I run it against my host's SQL server it also runs quick (.3 seconds)

4. One time I used my host's SQL server for the connection string and ran the app in VS 2010 debug, after a some time, I received this error

   Execution of the command requires an open and available connection. 
   The connection's current state is broken.
   

The table Cities has over 7000 rows, with (as of now) only about 4-5 rows that actually have Orders (so those are the ones that will be displayed).

I talked to my host, they said they don't see anything wrong with the server (Which makes sense because other pages on the website that query the server run fine). They restarted it, but I'm still getting the same slow load times.

This is a weird problem, I have no idea what could be causing this, any help would be greatly apprecaited.

UPDATE 1:

Here is the stop watch results

        Stopwatch s = new Stopwatch();
        s.Start();
        IEnumerable<City> cities = db.Cities.Where(x => x.Orders.Count != 0).OrderBy(x => x.Name);
        s.Stop();
        long t = s.ElapsedTicks;
        return View(cities);

When I place a break point at return View(cities), t had the value of 387 which makes sense since cities is just making the statement, which also means the issue lies in the foreach that executes the statement in the view.

Since I can't put break points into the view, I went ahead and did the following:

        s.Start();
        List<City> list = cities.ToList();
        s.Stop();
        long q = s.ElapsedTicks;

to mimic executing the statement (from my understanding, running a foreach on an IEnumerable is equivalent to calling .ToList())

After a very long time (note: I'm still using my shared host's SQL server in the connection string), q's value was 890489194. So the issue is executing the query. Is this an obvious indication to an issue with the SQL database/server?

Update 2:

If I rewrite the query as such:

  db.Orders.Select(x => x.City).Distinct();

This runs fast and the page loads fine. My only concern is scaling. Which query would be better for a large database.

Again, each Order has a single City associated with it, and a City can have many Orders.

Answer 1

I am going to just come out and say you need an index for the join to your Order table.