You can improve the user experience with this application. Let's say that for expanding and collapsing these elements, you're going to use a Dynamic HTML (DHTML) function that relies on each element's ancestry (i.e., the hierarchy of parent and child relationships up to the top of the data structure). Here's an example message that has two levels of replies:
- SQL Server - entered 6/28/00 3:15 PM by cbehrens
- Stored Procedures - entered 6/28/00 4:15 PM by mjohnson
- Recursion - entered 6/28/00 5:15 PM by hsmith
For each message, you need to generate an ancestry code consisting of the IMessageID values of all the messages that came before. This ancestry code, which the DHTML functions will use, allows for the locating and sorting of messages that belong to each top-level message.
(100) - SQL Server - entered 6/28/00 3:15 PM by cbehrens
(100_101) - Stored Procedures - entered 6/28/00 4:15 PM by mjohnson
(100_101_102) - Recursion - entered 6/28/00 5:15 PM by hsmith
Each line begins with an ancestry code; the message titled "SQL Server" has nothing but its own ID (100) because it's a base-level message. The message titled "Stored Procedures" is a child of the first message, so it carries a code of 100_101: the message ID of its parent plus its own message ID. And, the message titled "Recursion" has a code of 100_101_102—namely, the IDs of each message in its ancestry plus its own message ID.
Table 6 shows the structure of the cache table that you use to pregenerate content, as I discussed earlier. Table 7 lists each of the query elements and their definitions.
The first thing you need to do in this process is to clear the old cache. To do this, simply delete the old data:
DELETE FROM tblForumCache
Next, you need to use a separate procedure to perform the recursion. Let's name this procedure sp_RecurseMessages and give it two parameters: IMessageID and IDepthLevel. To call the procedure, run this command:
EXEC sp_RecurseMessages 0, 0
These parameters tell the procedure to start with a depth level and message ID of 0. You need to specify a 0 value so that the procedure can pass the information back and forth between the levels of recursion. The pseudocode for this procedure is similar to that for the previous procedure, with a few additions, as Listing 4 shows.
The finished recursive code, which Listing 5 shows, opens the cursor initially by selecting the children of the designated parent (initially the parent will be the top-level message because the parent ID is 0). After the procedure enters the record loop, it must update the parent record with an additional reply count that corresponds to the parent message. The code then runs a second procedure to obtain the ancestry of the message and inserts the ancestry values into the cache table.
Next, the stored procedure searches for children of the current message, after incrementing the depth variable to show that the code is proceeding down a level. Then the original procedure calls itself, proceeding through the same steps and perhaps down to the children's children. After the children recordsets are exhausted, the procedure that was called recursively goes up a level, returning control to the calling procedure, and decrements the depth count to reflect that change in level.
Note that the code initializes the @szAncestry variable before passing it to the sp_GetAncestry function. You could optimize the sp_GetAncestry procedure in various ways, such as getting the reply count all at once rather than running a query each time, but this method is a good and simple way to present this stored procedure.
Finally, you need to create the function that actually yields the ancestry. In this case, you don't want a recordset result; a simple string will suffice. To get the string value of the procedure, you declare the @szAncestry variable that you're passing in as an OUTPUT parameter:
CREATE PROCEDURE sp_GetAncestry
@szPath varchar(500) OUTPUT,
@lMessageID int
The rest of the stored procedure, which Listing 6 shows, essentially repeats the simple recursion function. Notice that the procedure again declares the cursor as local to avoid scoping problems. The code then processes down the hierarchy and back up, picking up the message ID with an underscore along the way, to form the ancestry code that the procedure needs.
You can implement this procedure by placing a trigger on UPDATE, INSERT, and DELETE events in the tblMessage table. Then, when a message is updated, inserted, or deleted, the trigger clears the cache and executes the cache-rebuilding function. Although this approach will cause a slight hiccup when the user creates a message, 90 percent of the users, who won't post a message anyway, will enjoy greatly improved performance because of the forward caching.
To optimize this flow even further, you could try the following three tricks:
- Generate ancestry gradually. You could use a series of stored procedures to build a history string as the procedure traverses the hierarchy, trimming and adding on where necessary. Doing so would save the overhead of the sp_GetAncestry call.
- Generate ReplyCount all at once. You could run one query to determine how many children a message has, thus avoiding the multiple calls to update the record.
- Pregenerate the forum content. You could further develop the trigger so that after it creates the cache table, it transforms the table's contents into an HTML file that would serve as the forum. Figure 3 shows a finished forum screen. This approach would save the Web server the overhead of dynamically generating the file, and it would save the database server an open connection and the overhead of querying the cache table.
Recursion can be a powerful tool for dealing with hierarchical data. With the new LOCAL keyword and a little cleverness, you can leverage recursion in SQL Server 7.0 with terrific results.