Working with Large Master-Detail Relationships | Applied ADO.NET: Building Data-Driven Solutions

One of the most common issues with OR mapping is how to handle large master-detail relationships without degrading performance. There are several ways to handle these types of relationships. You'll have to decide what works best for the application you're building because the best method depends on how your data will actually be used; also, some of it may require some experimentation.

There are three primary approaches to this problem:

Implementing a lazy loading scheme
Implementing an incremental lazy loading scheme
Loading the detail when the master is loaded

Implementing Lazy Loading

Large master-detail relationships can be problematic in any relationship but even more so with OR mapping. The primary goals of OR mapping is to hide anything even vaguely resembling data access and to enable a clean object-oriented Application Programming Interface (API) that's easily usable by anyone.

With these design points in mind, ideally you would like your API to work something like the following:

 myCategory.Videos(0).Title

Because a group of VideoTape objects belong to a category, you want to be able to access the VideoTape objects that belong to the category using an array. The problem is that you don't always want to load all of the VideoTape objects from the database. In most cases, you load all of the categories and likely only one collection of VideoTape objects belonging to a category.

The solution to this problem is to implement an old programming trick called lazy loading. Lazy loading means not actually loading the detail data until the program tries to use it the first time. This is most advantageous in a situation such as the one in Listing 19-9, namely when you're only displaying the detail table on one or two of the master items.

Listing 19-9: The Addition of the Videos() Array to the VideoCategory Object

 Dim vids As VideoTape()         Public ReadOnly Property Videos() As VideoTape()             Get                 If (vids Is Nothing) Then                     Dim vDAC As New VideoTapeDataAccess()                     vids = vDAC.GetAllVideoTapesInCategory(Me.CategoryID)                 End If                 Return vids             End Get         End Property

A VideoTape array called vids is added to the object. Because this array hasn't been initialized, its default value is Nothing. The read-only property Videos provides access to the VideoTape objects that belong in this category.

When the calling program accesses the Videos array, the VideoCategory object checks to see if vids hasn't been initialized. If it hasn't, then the VideoCategory creates an instance of the VideoTapeDataAccess DAC and initializes vids with the array of VideoTapes that belong in this category. Listing 19-10 shows the HTML side of the LazyLoading page.

Listing 19-10: The HTML Page of the LazyLoading Page

 <%@ Page Language="vb" AutoEventWireup="false" Codebehind="LazyLoading.aspx.vb" Inherits="LazyLoadingWeb.WebForm1"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD>      <title></title>      <meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR">      <meta content="Visual Basic 7.0" name="CODE_LANGUAGE">      <meta content="JavaScript" name="vs_defaultClientScript">      <meta content=http://schemas.microsoft.com/intellisense/ie5  name="vs_targetSchema"> </HEAD> <body> <form  method="post" runat="server"> <table cellSpacing="0" cellPadding="0" width="100%" border="0"> <tr>      <td vAlign="top" align="left">      <asp:table  runat="server"></asp:table> </td> <td vAlign="top" align="left" width="100%">      <asp:DataGrid  runat="server" AutoGenerateColumns="False" Width="100%">      <Columns>      <asp:BoundColumn DataField="VideoTapeID"></asp:BoundColumn>      <asp:BoundColumn DataField="Title"></asp:BoundColumn>      </Columns>      </asp:DataGrid> </td> </tr> </table> </form> </body> </HTML>

The HTML of this page consists of two main elements. The first is a server-side table that renders the category tree, and the second is a DataGrid that displays the VideoTapes that belong in the category.

Server-side table generation in the code-behind object is obviously not the best solution for rendering a tree control. When implementing these techniques on an actual system, please be a responsible programmer and implement your tree views using a Web custom control or subclass Microsoft's Internet Explorer TreeView object. Listing 19-11 shows the code-behind object of the LazyLoading page.

Listing 19-11: The Code-Behind Object of the LazyLoading Page

 Imports VideoStoreDataModel.EnterpriseVB.VideoStore.Data Public Class WebForm1     Inherits System.Web.UI.Page     Protected WithEvents VideoTapesGrid As System.Web.UI.WebControls.DataGrid     Protected WithEvents CategoryTree As System.Web.UI.WebControls.Table     Public Property SelectedCategoryID() As Integer         Get             If (("" + ViewState("SelCatID")) = "") Then                 Return -1             End If             Return Convert.ToInt32("" + ViewState("SelCatID"))         End Get         Set(ByVal Value As Integer)             ViewState("SelCatID") = "" + Value.ToString()         End Set     End Property     Private Sub Page_Load(ByVal sender As System.Object, ByVal e As _ System.EventArgs) Handles MyBase.Load         DisplayCategoryTree()     End Sub     Private Sub DisplayCategoryTree()         CategoryTree.Rows.Clear()         Dim dac As New VideoCategoryDataAccess()         Dim category As VideoCategory         category = dac.GetCategoryTree(1)         Dim depth As Integer         depth = GetDepth(category, 0)         RenderTree(category, depth, 0)         If (SelectedCategoryID <> -1) Then             Dim cCat As VideoCategory             cCat = cCat.FindCategoryByID(SelectedCategoryID)             VideoTapesGrid.DataSource = cCat.Videos             VideoTapesGrid.DataBind()         End If     End Sub     Public Function RenderTree(ByRef cat As VideoCategory, _ ByVal depth As Integer, _ ByVal currentDepth As Integer)         Dim tr As New TableRow()         Dim i As Integer         For i = 0 To currentDepth             Dim spacerCell As New TableCell()             spacerCell.Text = "&nbsp;"             spacerCell.Width = Unit.Pixel(10)             spacerCell.Height = Unit.Pixel(10)             spacerCell.BorderWidth = Unit.Pixel(0)             tr.Cells.Add(spacerCell)         Next         Dim descCell As New TableCell()         descCell.ColumnSpan = (depth - currentDepth) + 1         Dim lb As New LinkButton()         lb.Text = cat.Description         lb.ID = "CAT" + cat.CategoryID.ToString()         AddHandler lb.Click, AddressOf Category_Selected         descCell.Controls.Add(lb)         If (Me.SelectedCategoryID = cat.CategoryID) Then             descCell.BackColor = Color.Yellow         Else             descCell.BackColor = Color.White         End If         descCell.Style.Add("white-space", "nowrap")         tr.Cells.Add(descCell)         CategoryTree.Rows.Add(tr)         currentDepth = currentDepth + 1         For i = 0 To cat.CountSubCategories() - 1             RenderTree(cat.GetSubCategory(i), depth, currentDepth)         Next     End Function     Public Sub Category_Selected(ByVal sender As Object, ByVal e As EventArgs)         Me.SelectedCategoryID = _ Convert.ToInt32((CType(sender, LinkButton)).ID.Substring(3))         Me.DisplayCategoryTree()     End Sub     Public Function GetDepth(ByVal cat As VideoCategory, _ ByVal depth As Integer) As Integer         Dim tDepth As Integer         Dim deepest As Integer         deepest = depth         Dim i As Integer         For i = 0 To cat.CountSubCategories() - 1             tDepth = GetDepth(cat.GetSubCategory(i), depth + 1)             If (tDepth > deepest) Then                 deepest = tDepth             End If         Next         Return deepest     End Function End Class

You can break the code-behind object of the LazyLoading page into two primary functional parts: the tree rendering portion and the VideoTape listing.

The DisplayCategoryTree() method delegates the task of building the table to RenderTree() and GetDepth() methods after loading the data from the database. The GetDepth() method is needed so you know how deep the deepest element is in the tree to determine what the ColSpan needs to be for each category to achieve a nice tabbed look for the tree. As the tree is being rendered, it checks each VideoCategory to see if it matches the SelectedCategoryID property. If it does, it highlights that category.

At the end of the DisplayCategoryTree() method, it checks to see if SelectedCategoryID has a value provided. If it does, the program makes a call to the FindCategoryByID() method of the VideoCategory object to get the category that the user has selected. It then sets the DataSource of the VideoTapesGrid to the Videos array of the VideoCategory object and calls DataBind() to update the display.

As the Videos array is accessed for the first time, behind the scenes the VideoCategory object loads the array of VideoTapes from the database and returns it. Subsequent attempts to the Videos collection of the VideoCategory object will use the version already in memory without having to make another round trip to the database. Figure 19-8 shows the LazyLoading page in action.

click to expand
Figure 19-8: The output of the LazyLoading page

Implementing an Incremental Lazy Loading Scheme

Lazy loading as described in the previous section works for most purposes, but if you're in a situation where the detail set is potentially hundreds or thousands of records, then it becomes necessary to implement an incremental lazy loading technique.

In a nutshell, when the information is first requested from the detail set, a small number of records will be retrieved along with the count of the total number of records. If a request is made for one of the records that's out of range of what was loaded, the rest of the records will be loaded.

An example of where this technique is useful is on a "summary page" that displays the first 10 records on the front page and allows the user to click for more information. Most of the time the user will not click the link to see the additional records, so it doesn't make any sense to load them and incur the network overhead between your Web server and database to fetch the entire result set if it's not necessary.

To implement the incremental lazy loading scheme, you must modify both the stored procedure and the VideoCategory component. The VideoCategory object needs to get the first 10 records from the database, and it also needs to know how many records there are total in the database, so the VideoCategory object will know if and when an additional round trip to the database is necessary. Listing 19-12 shows the VideoTapeLoadByCategoryIncrID stored procedure, which loads the first 10 video tapes for a given category.

Listing 19-12: The Incremental Lazy Loading Stored Procedure

 CREATE PROCEDURE dbo.VideoTapeLoadByCategoryIncrID @CategoryID int, @TotalRows int out AS BEGIN SELECT @TotalRows = (SELECT count(*) FROM VideoTape WHERE CategoryID=@CategoryID) SELECT  TOP 10 VideoTapeID,  CategoryID, Title, Description FROM VideoTape WHERE CategoryID=@CategoryID END

The VideoTapeLoadByCategoryIncrID stored procedure first gets the total number of records that are in the result set and stores it in an output variable. The second Select statement actually loads the first 10 rows from the database.

To put this stored procedure to work, you need a custom indexer smart enough to transparently know when it needs to return to the database to get the remaining records. Listing 19-13 shows the VideoCategoryIndexer.

Listing 19-13: VideoCategoryIndexer

     Public Class VideoCategoryIndexer         Private vids As ArrayList         Private cnt As Integer         Public Sub New(ByVal catID As Integer, ByRef conn As SqlConnection)             vids = New ArrayList()             Dim sda As New SqlDataAdapter()             sda.SelectCommand = New SqlCommand(_ "VideoTapeLoadByCategoryIncrID", conn)             sda.SelectCommand.CommandType = _ CommandType.StoredProcedure             sda.SelectCommand.Parameters.Add(_ "@CategoryID", SqlDbType.Int, 0, "CategoryID")             sda.SelectCommand.Parameters.Add(_ "@TotalRows", SqlDbType.Int)             sda.SelectCommand.Parameters("@TotalRows").Direction = _ ParameterDirection.Output             sda.SelectCommand.Parameters("@CategoryID").Value = catID             Dim data As New VideoTapeData()             sda.Fill(data)             cnt = Convert.ToInt32(sda.SelectCommand.Parameters("@TotalRows").Value)             vids.AddRange(VideoTapeDataAccess.GetVideoTapeArrayFromData(data))         End Sub         Public Function Count() As Integer             Return cnt         End Function         Public Property Videos(ByVal index As Integer) As VideoTape             Get                 If (index >= vids.Count) Then                     FullLoad()                 End If                 Return CType(vids(index), VideoTape)             End Get             Set(ByVal Value As VideoTape)                 vids(index) = Value             End Set         End Property         Public ReadOnly Property LoadedCount() As Integer             Get                 Return vids.Count             End Get         End Property         Private Function FullLoad()             Dim dac As New VideoTapeDataAccess()             vids.Clear()             vids.AddRange(dac.GetAllVideoTapes())         End Function     End Class End Namespace

VideoCategoryIndexer immediately loads the first 10 records in the constructor using an inline call to the VideoTapeLoadByCategoryIncrID stored procedure. The stored procedure also returns a count of the number of records that existed in the database in the @TotalRows output variable, which VideoCategoryIndexer then stores in the member variable cnt.

The Videos() indexed property accesses the internal ArrayList to get the requested VideoTape object. If the calling program requests one of the VideoTapes within the rage that's loaded, it returns it immediately. If the calling program requests one of the VideoTape objects that has not been loaded yet, it transparently loads the entire collection.

VideoCategoryIndexer would not normally include a public LoadedCount property simply because the point of an object such as this is to make the incremental lazy loading transparent to the calling program. It's only included in this example to provide "proof" to the test case that it's indeed working. Listing 19-14 shows the test case for VideoCategoryIndexer.

Listing 19-14: The VideoCategoryIndexer Test Case

 Imports VideoStoreDataModel.EnterpriseVB.VideoStore.Data Module IncLoadTestCase     Sub Main()         Console.WriteLine("Starting IncLoadTestCase")         Dim iLoadCat As New VideoCategoryIndexer(3, _ New SqlClient.SqlConnection(VideoCategoryDataAccess.connectionString))         Console.WriteLine("Loaded " + iLoadCat.LoadedCount.ToString() _ + " out of " + iLoadCat.Count().ToString() + ".")         Console.WriteLine("Displaying First 10")         Dim i As Integer         For i = 0 To iLoadCat.LoadedCount - 1             Console.WriteLine("Item:" + iLoadCat.Videos(i).Title)         Next         Console.WriteLine("Displaying All")         For i = 0 To iLoadCat.Count() - 1             Console.WriteLine("Item:" + iLoadCat.Videos(i).Title)         Next         Console.WriteLine("Test Complete.")         Console.WriteLine("Press enter key to quit.")         Console.ReadLine()     End Sub End Module

The incremental lazy loading test case first creates an instance of VideoCategoryIndexer. Second, it displays the number of items loaded as well as the total count of items to be found in the database. Next, it displays all of the VideoTape objects currently loaded. Finally, it displays all of the items in the collection. As it does this final step, it reaches out into the database and loads the remaining VideoTape objects as soon as the requested index is outside of the loaded range. Figure 19-9 shows the output of the incremental lazy loading test case that tests the LazyLoading component.

click to expand
Figure 19-9: The incremental lazy loading test case output

The amazing thing about this test case is that the object's lazy loading functionality is completely transparent. That said, you can do certain things to enhance the functionality of this system.

If you were to implement IEnumerable, this collection would be capable of being data bound to any of .NET's many controls. You would also be able to use it transparently as you would any other array.

Another thing you can do to enhance the functionality of VideoCategoryIndexer is to integrate it into the VideoCategory object to use it as its internal means of loading the VideoTape objects that belong in the category. This, combined with implementing the IEnumerable interface, would allow you to use incremental lazy loading without modifying any code you may have written for the VideoCategory object.

You'll want to consider your own problem carefully before using lazy loading. If parts of your application will always need the whole table, you need to keep VideoCategoryIndexer as a separate object only to be used when you truly intend to do incremental lazy loading; otherwise you'll have code that calls two stored procedures instead of one.

Loading the Detail When the Master Is Loaded

The third main tool at your disposal when working with potentially large master-detail relationships is loading the detail table when the master table is loaded. The advantages of doing this are minimizing the round trips required to the database to load and work with your objects.

The situation where this strategy is beneficial is when you need to be able to display the master as well as most, or all, of the detail. A good example is a page that displays 10 orders per page, and each order has 1 to 20 line items. The master table Order will always display the detail table Selection every time the master is displayed throughout the application.

The solution is to implement a GetOrdersByDay() method (or whatever method of choosing the orders you need to provide) that automatically calls the stored procedures to load the Orders and the Selections for that day and then assemble them into your mapped objects in memory.

Loading the detail automatically in situations where it's not needed increases the round trips to the database and increases application latency. Always make sure that this is what your users will be doing most of the time when using this technique.

In situations where you're loading more detail records than you'll be displaying, but you know that the user will be asking to see them fairly quickly, makes this technique a good fit if you have a simple high-performance caching mechanism.