Brent Gardner's Blog

Pre-Loading vs. Lazy Loading

Well, it has been a while since my last post. No, I wasn't in Turkish prison, I just got busy with, well you know, my real job. So I apologize to my regulars (if I have any at this point ;-). But hopefully this post will make up for the delay.

I've noticed lately (especially with LINQ coming soon), that lazy-loading of data from the database has become the de-facto method for many applications. While this works relatively well when you application has a low-latency connection to the database (i.e. webapps), it is not ideal for client-based applications, where data has to be sent over the Internet and cached on a local machine, until it is synchronized again with the server. Lazy loading also introduces a few issues even when connection latency is low, foremost being that since loading occurs unpredictably throughout your code execution; data may have changed during the load process.

Pre-loading data is arguably a cleaner approach, since it can always be born out into a simple set of steps:

1. Load the record you are after, and all its related records (ideally in one transaction)
2. Cache the data in memory while it is displayed and manipulated by the user
3. Persist the data back to the server, wrapped in a transaction, with optimistic concurrency checks

This three step process always ensures that data is read and written at the same time, reducing over-head, and ensuring that all data is from a particular snapshot in time. Unfortunately it can be extremely cumbersome to write the necessary SQL to load not only a record, but all of its parents, pertinent children, and type-table entries necessary for a given web page or windows form to work properly. This rather difficult step has lead the way to the domination of the lazy-loading approach -- but it doesn't have to be so. If there was a convent way (i.e. not involving hundreds of lines of SQL) to load all the records related to a particular target record (or records), then step #1 would become simple.

All of which are reasons why I wrote a class to perform automated related record loading. Its called FriendlyLoader, and it can take a simple SQL query, for a single record (or more), and parse the relationships in the DataSet, loading all parent records and (optionally) child records as well. This allows you to easily pull back an entire chunk of the database, bring it to the client-side, manipulate it as need be, then persist it back again. But why stop with making the retrieval of data convenient? FriendlyLoader also has a companion class, Persistor, which persists data in appropriate order, inserting parents first, deleting children first, etc. It's a one-stop shop for convenient loading and persistence of DataSets!

So, on to syntax; basically the FriendlyLoader class simply consists of one public method (with various overrides). The most basic of these just accepts a DataSet to fill, a string containing the SQL command to fill it, a SqlConnection to the DB, and the names of the tables (in order) that it will be filling.

So, if you’re select command is:

select * from Customers

select * from Employees

Then your TableNames parameter should be “Customers,Employees”.

No matter which method you call, your SQL command will be executed, then all parent rows will be loaded that are necessary to enable constraints. The various other methods simply include different ways to load optional child rows:

' Load only parent rows

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String)

' Load all posible child rows

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal LoadAllChildren As Boolean)

' Load only child rows that reside in tables named in the ChildTableNames parameter

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal ChildTableNames As String)

' Load only child rows that reside in tables that are related to parent tables by a relation in the RelationNames parameter

    Public Shared Sub ClientFillByRelation(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal RelationNames As String)

' Load only child rows that reside in tables named in the list of tables

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal ChildTables As List(Of DataTable))

' Load only child rows that reside in tables that are related to parent tables by a relation in the list of relations

   Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal ChildRelations As List(Of DataRelation))

If you are still paying attention at this point, you may wonder why the LoadAllChildren=True doesn’t just load the entire database. The answer lies in the execution order. First your SQL statement is run, after that all children are loaded, then children’s children, and so on.  Lastly all necessary parent rows are loaded. So theoretically, if all the records in your DB are descended from one parent record, then yes, it could load your whole database, but if your query is fairly specific, then relatively few rows will be loaded.

This is definitely something to watch out for when loading tables that are high in the genealogy of the DB. “select * from Customers” with LoadAllChildren=True would load all Invoices and InvoiceItems as well. So when loading a table that is known to have hundreds of thousands of children, it is probably best to use one of the methods that lets you more specifically identify which children should be loaded.

One other thing to note:  type tables are treated differently than all other tables (object tables, link tables, etc). If a Table has its IsTypeTable property set to True, than all of its contents will be loaded with every query. This is done to accommodate constraints, and also to provide appropriate data to fill DropDown boxes.

Ok, that about does it for this post. There is a demonstration project in a zip file below, feel free to download it and play around. You will need a copy of the Northwind DB and a SQL server of some kind. Also, as usual, this code is provided as-is and without warranty. Use it if you like, but be sure to test thoroughly first!

Until next time,

Brent

Published Thursday, June 14, 2007 9:54 PM by Brent