2009
Update: an even better solution has been provided by ivowiblo in the comments below making use of DataLoadOptions! Be sure to check that out! – Original post as follows:
It might seem like a sudden change for me to blog about C# and ASP.NET MVC but it’s what I work with at my work and I’m loving it. Plus I haven’t had the inspiration to use django in months. From now on, most posts will be about C#, ASP.NET MVC, HTML, CSS, and many other acronyms…
This may be already have been obvious to you but I had been working with a slow query for a number of days before I really realised what was going on.
Here’s the scenario: You want to show a list of two or more fields from different sql tables on a single page but for some reason the longer the list gets, the page just keeps taking longer and longer to load.
In this example the fields to be demonstrated will be the forename, surname and city of residence of people. The db contains three tables: person, personname and address.
Simply put, you may do something like this in your controller:
public ActionResult ViewAll() {
IQueryable<Person> persons = (from person in this.db.Persons
select person);
return View(persons);
}
and this in your view:
<table>
<% foreach(Person person in Model) { %>
<tr>
<td><%= person.PersonName.Forename %></td>
<td><%= person.PersonName.Surname %></td>
<td><%= person.Address.City %></td>
</tr>
<%}%>
</table>
If you code this up and use Vandermotten’s DebuggerWriter you’ll see that it goes to the db each time it needs to look up the entry for the personname table or the address table. On a very long list, you can imagine that so many db calls (although quick to execute) can take up a huge chunk of time.
This is where we use a custom object to be filled with the specific fields we require. This means as soon as the variable is enumerated, it’s populated with everything we need!
public class MyPerson {
public MyPerson(string forename, string surname, string city) {
this.Forename = forename;
this.Surname = surname;
this.City = city;
}
public string Forename {get; private set;}
public string Surname {get; private set;}
public string City {get; private set;}
}
public ActionResult ViewAll() {
IQueryable<MyPerson> fasterPersons = (from person in this.db.Persons
select new MyPerson(person.PersonName.Forename, person.PersonName.Surname, person.Address.City));
return View(fasterPersons);
}
This new Linq will execute just the once and enumerate the MyPerson object with just the right info for each and every person in the table.
The view will have to be changed just slightly to:
<table>
<% foreach(MyPerson myPerson in Model) { %>
<tr>
<td><%= myPerson.Forename %></td>
<td><%= myPerson.Surname %></td>
<td><%= myPerson.City %></td>
</tr>
<%}%>
</table>
And there you have it. This finished code will just execute the once and so will speed up the page load incredibly! In a development platform I was seeing page load times dropping from ~2secs to 12ms.
Hope this helps anyone out there who just managed to find themselves here
Related posts at athe.la:
- TimeToggle django custom filter It’s always good to keep web pages clean and free...
The problem you have is called SELECT N+1. Linq-to-sql is querying the db twice for each person: one in order to fill ForeName and one to fill City. If you want to avoid creating a new class for each query you need (that also repeat your current model over and over again) you could use LoadWIth in the LoadOptions of your DataContext:
var options = new DataLoadOptions();
options.LoadWith(x => x.City);
options.LoadWith(x => x.PersonName);
db.LoadOptions = options;
If you don’t want to configure eager loading for all the lifetime of your DataContext, you could do this:
db.Select(x => new { Person = x, City = x.City, Name = x.PersonName }).ToList().Select(x => x.Person);
Since it loads City and PersonName for each Person, it solved all the stuff with one single query.
Hey thanks ivowiblo! Nice code samples too, but by x.City you mean x.Address right?
I never knew about DataLoadOptions(). I’ll look into it and use it on my next project. It’s great that it has a function to do this… all this time I’ve been using this workaround as I thought there wasn’t anything provided to achieve the same results.
Good stuff
Dan,