The Items_PackagesChanges trigger, which Listing 7 creates on Items_Packages, is almost identical to the trigger in Listing 6 and covers all remaining relationship changes that can occur to the member Items of Packages (e.g., updates, deletes, or inserts to the Products_Items table). Now that all the parts are in place, let's test the triggers. First, use the following code to get the current price of PackageID 1, a 500MHz computer with 128MB of RAM:
EXEC GetPackageInfo2 1
This command returns a price of $1080. To test the first trigger, change the price of the Pentium III processor from $500 to $600 in the Products table:
UPDATE Products
SET ProductPrice = 600 WHERE ProductId = 6
GO
EXEC GetPackageInfo2 1
The price of Package 1 now returns a price of $1170, exactly $90 ($100 minus 10 percent) more than before. Next, modify the Items table so that RAM is unavailable:
UPDATE Items
SET ItemIsAvailable = 0 WHERE ItemId = 3
GO
EXEC GetPackageInfo2 1
The price of the computer displays $180 ($200 minus 10 percent) less, at $990. Now modify an Item's member Products to test the third trigger. If hard drives start shipping with integrated mounting kits, you'll need to remove the mounting kit Product from the 6GB hard drive Item:
DELETE Products_Items
WHERE ItemId = 1 AND ProductId = 2
GO
EXEC GetPackageInfo2 1
The Package price has dropped by the price of the mounting kit, $13.50 ($15 minus 10 percent), to $976.50. To test the final trigger, suppose the computer now ships with dual processors. Add another Pentium III processor Item to the computer Package:
UPDATE Items_Packages
SET Quantity = 2 WHERE PackageID = 1 AND ItemId = 4
GO
EXEC GetPackageInfo2 1
The final price on the computer is $1516.50, or $540 ($600 minus 10 percent) more than before. You can be confident that the PackagePrice column will remain consistent through any pertinent data changes.
Mission Accomplished
Now let's look at the performance results of all the modifications and see what kind of performance gain we've achieved. I took the STATISTICS IO that Figure 2 shows from the original stored procedure, which used a five-table join. The physical reads will drop off after the data pages are loaded, but 14 scans and 33 logical reads is significant, especially considering that this query will be executed thousands of times an hour. Contrast the statistics in Figure 2 with the statistics output from the new stored procedure, which Figure 3 shows. What a difference! The load on the server has dropped from 14 scans and 33 logical reads to 1 scan and 2 logical reads. Clearly our performance-enhancing mission has been accomplished.
But couldn't you realize another performance gain by following the same procedures and implementing an ItemPrice column in the Items table? Absolutely, and I encourage you to use the preceding example as a template to do just that.
As you can see, the introduction of redundant data into a database demands thorough planning for maintaining that data. But in a high-traffic Web site, the benefit of eliminating a five-table join from one of the most frequently accessed pages far outweighs the overhead added during data modification. If any of your applications fit a similar profile with infrequent updates and heavy query traffic, you might want to consider this underused technique to boost your users' speed. And happy users just might boost your bottom line.
End of Article
Prev. page
1
2
[3]
next page -->
