Three Different Ways of Relating Children to Parents

[Previous] [Next]

As you've already seen from the client code in the test form, we've used three different ways to relate parent and child records to each other. Each of the three ways results in different ADO behavior, and it's important for you to understand these behaviors to be able to select the right way to relate parent and child to each other when using hierarchical recordsets.

Relating Through Parameters

The first way uses one or more parameters in the command object responsible for getting child records. Each parameter in the child record relates to one field in the parent recordset. We've created two Data Environment command objects for this part of the test. Their respective names, GetRacesForDayParam and GetEntrantsForRaceParam, indicate their use of parameters. Figure 19-7 shows what's in the recordsets created by these two command objects. The figure also shows that the two recordsets are related to each other in a hierarchical fashion; the recordset created by GetRacesForDayParam contains the parents, and the recordset created by GetEntrantsForRaceParam contains the children.

Figure 19-7. A field in the GetRacesForDayParam command object connects to a parameter in the GetEntrantsForRaceParam command object.

Let's take a look at the two SQL SELECT statements needed by the command objects to get the right information from the database. The first of them, following here, fetches data about all the races run on a particular day:

 SELECT RaceNmbrRunning, RaceDayNmbr, RaceNumber, Distance, RaceType, TrackType, RaceName FROM Races WHERE (RaceDayNmbr = ?)   ORDER BY RaceNumber 

The code printed in boldface type determines that only races belonging to a specific race day are returned. It's the question mark that specifies a certain race day. The Data Environment object automatically creates an ADO Parameter object to represent each question mark in such a SELECT statement, so in a Data Environment, all you have to do to create a parameter object is use a question mark in your SELECT statement. Figure 19-8 shows the Parameters tab of the Properties dialog box of our command object.

Figure 19-8. Each question mark in the SELECT statement—in this case one only—automatically results in a parameter object like this one.

The SELECT statement used to fetch the race entrants—the children—of the races selected in the previously mentioned SELECT statement also uses a parameter. This parameter filters the child recordset so that only entrants of the selected races slip through. Here follows the child-fetching SELECT statement:

 SELECT RaceEntrants.RaceNmbrRunning, RaceEntrants.StartNumber, Horses.HorseName, RaceEntrants.FinishPlace, RaceEntrants.Weight, RTRIM(Jockeys.FirstName) + ' ' + RTRIM(Jockeys.LastName) AS Jockey, RaceEntrants.FormIn, RaceEntrants.FormIn - RaceEntrants.Weight AS FormRel FROM RaceEntrants INNER JOIN Horses ON RaceEntrants.Horse = Horses.HorseId INNER JOIN Jockeys ON RaceEntrants.Jockey = Jockeys.JockeyId WHERE (RaceEntrants.RaceNmbrRunning = ?)   ORDER BY RaceEntrants.FinishPlace 

While not exactly simple, this SELECT statement isn't overly complicated. The SELECT clause specifies several ordinary database columns from different database tables. Most of the columns belong to the RaceEntrants table, but the names of the horse and the jockey, respectively, belong to the Horses and Jockeys tables. The clause also contains two calculated result columns. The first is the Jockey column, calculated from the first and last names of the jockey. The second is the FormRel column, calculated from the FormIn and Weight columns. If you're curious, a sidebar below explains the meaning of different weight and form aspects of horse racing. If you're not interested, just skip the sidebar and read on. (The sidebar doesn't add a thing to the technical content of this chapter or book.)

To help you avoid flipping through this book's pages looking for the code snippets we talk about, here's a new copy of the SELECT statement we're talking about right now:

 SELECT RaceEntrants.RaceNmbrRunning, RaceEntrants.StartNumber, Horses.HorseName, RaceEntrants.FinishPlace, RaceEntrants.Weight, RTRIM(Jockeys.FirstName) + ' ' + RTRIM(Jockeys.LastName) AS Jockey, RaceEntrants.FormIn, RaceEntrants.FormIn - RaceEntrants.Weight AS FormRel FROM RaceEntrants INNER JOIN Horses ON RaceEntrants.Horse = Horses.HorseId INNER JOIN Jockeys ON RaceEntrants.Jockey = Jockeys.JockeyId WHERE (RaceEntrants.RaceNmbrRunning = ?)   ORDER BY RaceEntrants.FinishPlace 

As you can see, the recordset represents a join of three tables: the RaceEntrants table, the Horses table, and the Jockeys table. More interestingly, the WHERE clause (printed in boldface type) makes the statement retrieve from the database only those RaceEntrants rows that represent horses that run in a specific race. The question mark, as you know, makes ADO create a parameter object for this command.

Two parameter objects

We now have two parameter objects for this hierarchical recordset, one for the parent command and another for the child command. The parent parameter specifies a certain race day, filtering out all the races not run on that particular day. The child parameter specifies a certain race, filtering out all the entrants not running in that race.

The relation

To relate the two commands to each other, making two ordinary recordsets into a single hierarchical recordset, you use the Relation tab of the Properties dialog box for the child recordset. (This is a convenient way, even if not the only way, of doing it.) Figure 19-9 shows that the RaceNmbrRunning field of the parent command is now related to the first parameter of the child command.

Weight and Form in Horse Racing

In Europe, the quality of a racehorse is judged by a handicapper. This person sets a value on the horse, representing the handicapper's opinion of how good the horse is according to the performance of the horse to date. It's not unlike the handicapping of golfers. This value is expressed in terms of weight. In England and Ireland, this weight is expressed in pounds; in the rest of Europe, it is expressed in kilograms. A horse with a form value of 100 is said to be 2 kilograms better than a horse with a form value of 98.

In races, horses are assigned weights. This is especially true in so-called handicap races, in which the handicapper tries to assign the weights so that all the horses could theoretically cross the finish line at the same time. This, of course, isn't possible, but it's the ostensible goal of the handicapper to achieve parity among the horses. In the case of the two horses handicapped with 100 and 98 respectively, the handicapper might assign 58 kilos to be carried by the first horse and 56 to be carried by the second. The weight-related form value of these horses should be the same with these weight assignments: for the first horse, it's 100 - 58 = 42; for the second one, 98 - 56 = 42.

This is the meaning of the FormRel (or relative form) calculated column in our recordset. Theoretically, a horse with a higher FormRel value than another horse has a greater chance to win the race.

Figure 19-9. The GetEntrantsForRaceParam command object is related to the GetRacesForDayParam command object through its only parameter object.

When you relate two commands to each other in this way, your Data Environment automatically creates an ADO SHAPE command for you. Somewhat simplified, a SHAPE command has the structure shown here.

 SHAPE {SELECT <<Parent SELECT statement>>} AS <<Name of Parent command>> APPEND ({SELECT <<Child SELECT statement>>} AS <<Name of child command>> RELATE '<<Parent Relate field>>' TO PARAMETER 0) AS <<Name of child command>> 

The APPEND and RELATE statements can be repeated several times, creating multiple hierarchical levels, in which each level can contain one or more siblings. This means that the SHAPE command can set up quite complex structures for you. The most interesting elements of the preceding SHAPE command are those printed in boldface type. They're interesting because they are what's particular to the first way of relating children to parents, which we're studying right now. The elements of command code printed in boldface type also control the behavior of the command. In this particular case, in which we want to relate race entrants to the races run on a particular day on a particular track, the Data Environment generated the following SHAPE command for us:

 SHAPE {SELECT RaceNmbrRunning, RaceDayNmbr, RaceNumber, Distance, RaceType, TrackType, RaceName FROM Races WHERE (RaceDayNmbr = ?) ORDER BY RaceNumber} AS GetRacesForDayParam APPEND ({SELECT RaceEntrants.RaceNmbrRunning, RaceEntrants.StartNumber, Horses.HorseName, RaceEntrants.FinishPlace, RaceEntrants.Weight, RTRIM(Jockeys.FirstName) + ' ' + RTRIM(Jockeys.LastName) AS Jockey, RaceEntrants.FormIn, RaceEntrants.FormIn - RaceEntrants.Weight AS FormRel FROM RaceEntrants INNER JOIN Horses ON RaceEntrants.Horse = Horses.HorseId INNER JOIN Jockeys ON RaceEntrants.Jockey = Jockeys.JockeyId WHERE (RaceEntrants.RaceNmbrRunning = ?) ORDER BY RaceEntrants.FinishPlace} AS GetEntrantsForRaceParam RELATE 'RaceNmbrRunning' TO PARAMETER 0) AS GetEntrantsForRaceParam 

Behavior of parameter-related hierarchical recordsets

Recordsets created by a SHAPE command such as this work fine in a two-tier environment. In fact, they work extremely fine. In a three-tier environment, they practically don't work at all, for the following reason: When you relate parent and child by way of a parameter, the parent recordset is filled with all the records specified by the parent SELECT statement. The child recordset, in contrast, contains only the records that relate to the very first record in the parent recordset.

As you move through the parent recordset from one record to another, ADO goes to the database to fetch the child records for each parent record as you move to it. Only when you move back to a parent record you've visited before does ADO refrain from going to the database to fetch its child records because these records are already present in the child recordset.

This means that a hierarchical recordset created in this way must be connected to the database at all times. You can't make it move from one process to another or from one computer to another. The recordset will always stay in the place where it was created. Even if the parent recordset contains one record only, the recordset must stay connected. ADO absolutely refuses to disconnect such a recordset. Since disconnecting is a requirement for the moving of recordsets, you can't move it. And since the recordset must stay where it was created, you can't deactivate the object that created it; the object must stay active to hold on to the recordset for its clients. In practice, this means that you can't use such a recordset in a distributed application that you want to be scalable.

The SQL Server Profiler is one way of proving it

You can easily study this behavior with a tool such as the SQL Server Profiler in Microsoft SQL Server 7.0. Such a tool allows you to see what the database really does, and when it does it. It allows you to filter database activities so that the profiler reports only the actions you are particularly interested in. Let's begin our study by taking a new look at the GetRacesForDay code in the server object:

 Public Function GetRacesForDay(lngDayNmbr As Long, _ intCommandAlternative As Integer) As Recordset On Error Resume Next Select Case intCommandAlternative Case 0 ' Relate by parameter  deHierarchy.rsGetRacesForDayParam.Close deHierarchy.GetRacesForDayParam lngDayNmbr Set GetRacesForDay = deHierarchy.rsGetRacesForDayParam Case 1 deHierarchy.rsGetRacesForDayField.Close deHierarchy.GetRacesForDayField lngDayNmbr Set GetRacesForDay = deHierarchy.rsGetRacesForDayField Case 2 Set GetRacesForDay = SpecialGetRacesForDay(lngDayNmbr) End Select 'The next code line is new! It disconnects 'the recordset from the database (if it can!)  Set GetRacesForDay.ActiveConnection = Nothing End Function 

Since the last time we showed you this code, we have added one line to it. It's the last line of the method, the one that disconnects (or rather is supposed to disconnect) the recordset from the database. For your convenience, we've also printed all the lines that will be executed in boldface type. As you know, the method recognizes three different ways of relating parent to children, and each of them is represented as one case of the SELECT CASE structure.

The SQL Server Profiler

The SQL Server Profiler captures events in Microsoft SQL Server 7.0. You can ask the profiler to save a list of these events in a file, which allows you to study the series of events whenever you please, to troubleshoot your application. The SQL Server Profiler is a replacement for SQL Server Trace, a tool that was included in earlier versions of Microsoft SQL Server.

For our test, we wanted the profiler to give us as clean a sheet of events as possible. So we selected only one of the many types of events the profiler offers. The Events tab of the Trace Properties dialog box shown in Figure 19-10 reveals that this event is the TSQL RPC—or Transact Structured Query Language Remote Procedure Call—Completed.

Figure 19-10. The trace we did in the SQL Server Profiler was minimal, as you can see from this picture. Of all the options available, we used only TSQL RPC Completed.

With the SQL Server Profiler properly set this way, we began a new session with our test and demo application. Figure 19-11 shows the SQL Server Profiler window directly after the Form_Load event of the client application's only form. As previously described, the IDs and the names of all racetracks entered in the system are selected from the database during Form_Load. In the lower part of Figure 19-11, you can see the SELECT statement issued to SQL Server to return a list of all racetracks to the form.

Figure 19-11. The first SQL statement of the application captured by the SQL Server Profiler. All the tracks are selected at Form_Load.

When the user of the form selects one of the racetracks listed by Form_Load, another call goes to SQL Server. This time the form needs a list of all the dates on which races were run (or will be run in the near future) on that track. Figure 19-12 is quite interesting in the way it shows us how wisely the SQL Server OLE DB Provider uses the new sp_executesql stored procedure to ask for this list.

Figure 19-12. The SQL Server Profiler here reveals that ADO—or more to the point, the SQL Server OLE DB Provider—uses the new stored procedure sp_executesql to fetch race dates for a selected racetrack.

The sp_executesql procedure lets clients send batches that include parameterized queries to SQL Server; as you know from Chapter 17, "Securing Good Database Performance and Scalability," parameterized queries help SQL Server reuse the compiled version of an earlier, similar, statement. This capability, of course, is extremely interesting because the query processor doesn't have to parse, optimize, or compile statements that can reuse existing execution plans. This is one of the main advantages of using stored procedures, and it's now also available to ordinary SQL statements sent from the client. Isn't it nice to see that ADO commands make it easier for SQL Server to reuse execution plans, just as we told you in Chapter 17?

Anyway, let's continue our study of the behavior of hierarchical recordsets in ADO. The next logical application step is to select one of the race days displayed from the foregoing SQL statement. Before taking that step, we cleared the SQL Server Profiler's window, just to make it easier to see what would happen next in SQL Server. We find the result, shown in Figure 19-13, to be extremely interesting.

Figure 19-13. Our application sent two messages to SQL Server. The first one was to prepare a statement for fetching race entrants. This statement hasn't yet been executed. The second one is for selecting the races of the day selected.

As you can see from the text of the figure, ADO, OLE DB, and the SQL Server provider together sent two statements to SQL Server:

• The first statement prepares, but doesn't execute, a SELECT statement for fetching the RaceEntrants recordset of a particular race, represented by the @P1 parameter. The SQL Server Profiler logs the following SQL Server commands:

Statement	Comment
declare @P1 int	Declare the @P1 parameter as integer.
set @P1=NULL	Nullify the value of the parameter declared.
exec sp_prepare @P1 output, N'@P1 int', N'SELECT RaceEntrants.RaceNmbrRunning, RaceEntrants.StartNumber, Horses.HorseName, RaceEntrants.FinishPlace, RaceEntrants.Weight, RTRIM(Jockeys.FirstName) + " " + RTRIM(Jockeys.LastName) AS Jockey, RaceEntrants.FormIn, RaceEntrants.FormIn - RaceEntrants.Weight AS FormRel FROM RaceEntrants INNER JOIN Horses ON RaceEntrants.Horse = Horses.HorseId INNER JOIN Jockeys ON RaceEntrants.Jockey = Jockeys.JockeyId WHERE (RaceEntrants.Race- NmbrRunning = @P1) ORDER BY RaceEntrants.FinishPlace', 1	Prepare the SELECT statement with its @P1 parameter.
select @P1	Return the @P1 parameter.
Go	Execute the entire batch.

• The second statement calls sp_executesql to really select all the races of the selected day. Here's the code for that:

 sp_executesql N'SELECT RaceNmbrRunning, RaceDayNmbr, RaceNumber, Distance, RaceType, TrackType, RaceName FROM Races WHERE (RaceDayNmbr = @P1) ORDER BY RaceNumber', N'@P1 int', 107 

As you can see, this is almost ordinary SQL code. The only differences come from two reasons: ADO doesn't send an SQL statement but calls the sp_executesql procedure, just as we recommended in Chapter 17. And ADO uses a parameterized statement, again just as we recommended in Chapter 17.

Now a hierarchical recordset is returned to our client. Or more correctly, a reference to a hierarchical recordset is returned to our client. You might recall that this way of relating children to a parent doesn't allow the recordset to move; the recordset stays where it was created, and the client gets a reference to it. The recordset contains exactly nine races. It doesn't contain any RaceEntrant records yet, but there's a place for them in a special field of the recordset.

Let's select one of the races. Remember now that our code has in fact made an attempt to disconnect the recordset from the database. But that attempt wasn't successful, which Figure 19-14 proves.

Figure 19-14. Even though we've asked for it, we aren't disconnected from the database.

Figure 19-14 shows through the SQL Server Profiler that SQL Server has acted. You can see that from the two selected profiler lines. You can also see it from the lower part of the screen, which specifies that the earlier prepared SELECT statement has been executed twice: once for the race identified by RaceNmbrRunning 56 and once for race number 57.

If our attempt to disconnect the recordset from SQL Server had been successful, SQL Server wouldn't have received the two procedure calls just mentioned. By showing us that SQL Server in fact received them, the SQL Server Profiler has proved that the recordset wasn't disconnected at all. And yet, our code suggests that it should have been.

When we asked in our code to be disconnected, ADO didn't do it. Furthermore, ADO didn't tell us that we weren't disconnected. We think ADO should have done that, so we send the following message to Microsoft: "When our code asks for an action from ADO (or any other part of our application), and ADO (or that other part) doesn't oblige, we think ADO (etc.) should tell us. Can we have that, please?"

First way of relating parent and child concluded

When you use child parameters to relate children to a parent in a hierarchical recordset, you can't disconnect the recordset from the database. This means that you can't move the recordset from the server to the client as you would in a typical Microsoft Windows DNA application. It also means that this way of relating child to parent is proper only for a two-tier application.

Relating Field to Field

Now let's see what happens when we relate child to parent using fields in both recordsets instead of one or more parameters in the child recordset. We'll start a new SQL Server Profiler trace, skipping the first few fetching operations in order to concentrate on the one (or ones) having to do with the hierarchical recordset. But before we do, just as a reminder, we're going to show you our test form again. It contains the data we're going to use for our SQL Server Profiler test.

Notice the option setting in the Select Method To Relate Recordsets group in the form in Figure 19-15. This setting indicates that we now relate field-to-field rather than parameter-to-field.

Figure 19-15. This is the form content we worked with in our second test run. Please notice that now the Relate To Field option is selected.

Let's take it one step at a time from the selection of the date of a particular race day. In Figure 19-15, the user has selected the races run on Täby Galopp on January 18, 1998. Reacting to the list box click, another Data Environment command object is being called; this command uses a RaceEntrants field rather than a parameter to relate race entrants to races. The behavior of such a hierarchical command object is different from the behavior of a hierarchical command object that uses parameters to relate children to a parent. This time all the children are fetched at once, allowing the recordset to be disconnected and thus moved from client to server. There's no need for the software to go back to the database for the race entrants of any of the races selected—they're already there.

So when the user begins to select races, nothing happens in the SQL Server Profiler because SQL Server isn't involved. Only when the user selects another race day or another racetrack must SQL Server act. Nothing stops ADO from obeying our order to disconnect this recordset from the database, and disconnected it is. Such a recordset can move freely between processes and between computers, and it's well suited for use in distributed three-tier Windows DNA applications.

The downside

As is so often the case with good things, using field-to-field relation between parent and child has a downside too. We know about that downside thanks only to the SQL Server Profiler and the Index Tuning Wizard, but it's also accurate to say that poor performance put us on track (and this time we don't mean the racetrack). When we selected a new date, we didn't get the races back as fast as we had anticipated. So we decided to use the SQL Server Profiler again to see what was going on in SQL Server. And the SQL Server Profiler helped us by telling us exactly what was sent to SQL Server, as the following command list shows:

 sp_executesql N'SELECT RaceNmbrRunning, RaceDayNmbr, RaceNumber, Distance, RaceType, TrackType, RaceName FROM Races WHERE (RaceDayNmbr = @P1) ORDER BY RaceNumber; SELECT RaceEntrants.RaceNmbrRunning, RaceEntrants.StartNumber, Horses.HorseName, RaceEntrants.FinishPlace, RaceEntrants.Weight, RTRIM(Jockeys.FirstName) + '' '' + RTRIM(Jockeys.LastName) AS Jockey, RaceEntrants.FormIn, RaceEntrants.FormIn _ RaceEntrants.Weight AS FormRel FROM RaceEntrants INNER JOIN Horses ON RaceEntrants.Horse = Horses.HorseId INNER JOIN Jockeys ON RaceEntrants.Jockey = Jockeys.JockeyId ORDER BY RaceEntrants.FinishPlace',   N'@P1 int', 6 

Take a look at the second SELECT statement, the one that's supposed to select all the race entrants that run in the races selected by the first SELECT statement. You'll notice that the statement has no WHERE clause; every horse that was ever entered in any race is included in the result set returned. This means that not only the entrants of the selected races but also all other entrants ever entered in the system wound up in the child recordset. In our case, it's only several thousand records, but that was apparently enough to slow down the performance noticeably.

Now consider the case of one of our customers. They have a Customer table with two million rows in it. Connected to that table is a CustomerAttribute table with more than 18 million rows. Imagine a hierarchical recordset, selected to contain, say, 20 customers and all their attributes. The customer attribute recordset should and is probably supposed to contain in the neighborhood of 180 records but in fact contains 18 million. How long will it take to load? How long will it take to marshal? More realistically: how long before the application bombs?

The only conclusion possible is that this is useless, at least if there's any risk at all that the child recordset is too large to handle efficiently. Add that to the uselessness in three-tier applications of the other kind of hierarchical recordsets, those that you relate using parameters to relate children to parents. You must now ask yourself whether hierarchical recordsets are worth it at all in multitier applications. We would definitely say yes. But you must filter the child records so the quantity returned is manageable.

Relating Field to Field with a Special Filter

If the mountain won't come to Mohammed, Mohammed must go to the mountain. If ADO won't filter for you, you must supply the filter yourself. This is what we did in the third example of our test application. To solve the problem of the many child records, we made a little change in the SQL SELECT statement for race entrants. We wanted its SQL statement to filter the records the same way the SQL statement fetching the races does. As you know, we filtered races according to the ID of the race day records; obviously, we should filter race entrant records using the same race day ID value. That value, however, isn't part of the RaceEntrants table. So we had to make the child SQL statement join the RaceEntrants rows to corresponding rows from the Races table. Such a join would allow us to filter child records on the Race ID field joined to the RaceEntrants records. (Alternatively, we could use a subquery to achieve the same result, but in our example we'll use a join.)

We wanted to remain in the Data Environment to do this, but we couldn't. If there's a parameter in the child SQL statement, the Data Environment insists that you use it for connecting the recordset to its parent. It doesn't accept a field-to-field connection if a parameter is available. We need the parameter to set up a filter, but we also need to relate child fields to parent fields; otherwise, we can't disconnect the recordset from the database, which is another must. So the only option remaining is to use our own code rather than take advantage of the simplicity the Data Environment offers.

Let's look at the following code, piece by piece. As usual, we've left out the details that don't help us understand the code, error handling being one of those details.

Public Function SpecialGetRacesForDay(lngDayNmbr As Long) As Recordset Dim strSQLRaces As String, strSQLEntrants As String Dim strShape As String Dim rsRaces As Recordset, strConnect As String strConnect = "<<Your connect string>>" strSQLRaces = "SELECT RaceNmbrRunning, RaceDayNmbr, " _ & "RaceNumber, Distance, RaceType, TrackType, " & _ "RaceName FROM Races " & _  "WHERE (RaceDayNmbr = " & lngDayNmbr & ") " & _ "ORDER BY RaceNumber" strSQLEntrants = "SELECT RaceEntrants.RaceNmbrRunning, " & _ "RaceEntrants.StartNumber, Horses.HorseName, " & _ "RaceEntrants.FinishPlace, " & "RaceEntrants.Weight, " & _ "RTRIM(Jockeys.FirstName) + ' ' + " & _ "RTRIM(Jockeys.LastName) AS Jockey, " & _ "RaceEntrants.FormIn, " & _ "RaceEntrants.FormIn - RaceEntrants.Weight " & _ "AS FormRel " & _ "FROM RaceEntrants " & _ "INNER JOIN Horses ON RaceEntrants.Horse = " & _ "Horses.HorseId " & _ "INNER JOIN Jockeys ON RaceEntrants.Jockey = " & _ "Jockeys.JockeyId " & _ "INNER JOIN Races ON & _ "RaceEntrants.RaceNmbrRunning = " & _ "Races.RaceNmbrRunning " & _ "WHERE (Races.RaceDayNmbr = " & lngDayNmbr & ") " & _ "ORDER BY RaceEntrants.FinishPlace" strShape = " SHAPE {" & strSQLRaces & _ "} AS GetRacesForDayField " & _ "APPEND ({" & strSQLEntrants & _ "} AS GetEntrantsForRaceField " & _ "RELATE 'RaceNmbrRunning' TO " & _ "'RaceNmbrRunning') AS GetEntrantsForRaceField" Set rsRaces = CreateObject("ADODB.Recordset") rsRaces.ActiveConnection = strConnect rsRaces.Source = strShape rsRaces.CursorLocation = adUseClient rsRaces.CursorType = adOpenStatic rsRaces.LockType = adLockBatchOptimistic rsRaces.Open Set SpecialGetRacesForDay = rsRaces End Function 

(As always, the most interesting parts of the code are printed in boldface type.) You'll notice that the child SQL SELECT statement actually joins the RaceEntrants table to the Races table even though not one single column from the SELECT list comes from the Races table. This is to make the filtering of RaceEntrants records to a specific race day possible. You'll also notice that the child WHERE clause is exactly the same as the parent one. Both of them filter on the RaceDayNmbr that equals the lngDayNmbr argument received in the method call.

Running this code renders the records you want, nothing less and nothing more. You'll get the races of the day together with the entrants of all these races. If the customer we mentioned before used this filtering technique, he would get a parent recordset containing 20 of the two million customer records, and that recordset would be hierarchically connected to another recordset of about 180 customer attribute records rather than 18 million of them as returned previously. The filter also solves the performance problem. In our case, it makes SQL Server return tens of child records rather than thousands, and the difference is noticeable.