Tag: O/R Mappers
Continuing our discussions regarding Performance Profiling O/R Mapper Calls to the Database and Quick Examples on LINQ To SQL Performance Tuning - Performance Profiling Your O/R Mapper, let's look at another way of choosing better performance through eager loading using Mindscape's LightSpeed O/R Mapper. If you have any experience with LINQ to SQL, you should find LightSpeed very easy to understand given the familiar LightSpeed Visual Design Surface and Unit Of Work concept which is similar to the LINQ To SQL Design Surface and DataContext Class.
LightSpeed O/R Mapper Design Surface
Let's pretend we have an online ecommerce store which has a 1 to Many mapping between the Category and Product Tables. Just like we do with LINQ To SQL, we can drag and drop the Category and Product Tables from the Server Explorer in Visual Studio 2008 to the LightSpeed Design Surface and it figures out the relationships.
Eager Loading Collection is False By Default
As with most O/R Mappers, LightSpeed turns off eager loading by default. This means that if you fetch a category and then iterate through its collection of Products you will end up with a flood of queries against your database. Using the TraceLogger in LightSpeed shows the results for the following code:
using (var uow = UnitOfWork.Create())
{
foreach (var category in uow.Categories)
Console.WriteLine(string.Format("ID: {0}, Total Products:{1}",
category.Id, category.Products.Count));
}
As we expected, we have 1 query to fetch all the categories and then a query executed each time we access the Count property of each Category's Products Collection. This is less than ideal when it comes to performance.
Eager Loading in LightSpeed
LightSpeed has at least two ways that I know of to handle eager loading.
One way is to set Eager Loading Collection to True via the Properties Window. This is embarassingly simple:
As one would expect this fetches all the Categories and Products in a single batched query with far better performance when there are a lot of categories and products. Again via the TraceLogger:
Unfortunately, setting Eager Loading Collection to True always eager loads the collection of Products, which may actually hurt performance in situations when you don't need the Products returned for each Category. Here is where Aggregates come in. Aggregates allow you to specify per request whether or not to demand load a collection in this case ( aside: aggregates can be more broadly thought of as named fetching strategies ). Hence, if I set Eager Load Collection back to False and add an aggregate of “AllProducts” to the collection, I can cause the Products to be eager loaded via the WithAggregate Extension Method:
using (var uow = UnitOfWork.Create())
{
foreach (var category in uow.Categories.WithAggregate("AllProducts"))
Console.WriteLine(string.Format("ID: {0}, Total Products:{1}",
category.Id, category.Products.Count));
}
The result of the TraceLogger is the same with categories and products being loaded in a single batched query. The WithAggregate Extension Method is very similar to the DataLoadOptions of LINQ To SQL, but more flexible because DataLoadOptions can only be set once per DataContext whereas you can use WithAggregate as needed on each query.
Very slick ;)
Conclusion
Most O/R Mappers, including LightSpeed, have various ways to increase performance through eager loading when necessary. If you are new to O/R Mapping, check to make sure your O/R Mapper is not being too chatty with the database. Many times you can improve performance with minor changes in your code. Only use of logging or various performance profilers will help you better understand what is happening under the covers.
David Hayden
Related Posts: LINQ TO SQL Tutorials