The SuperSub Database Schema
Next, let's look at the same leasing-company scenario, this time using the supertype-subtype implementation in the SuperSub database, which Figure 6 shows. Notice how I've removed the three M:N relationships and replaced them with a single M:N relationship between the Leases and Items tables. Three subtype tables—Furniture, Trucks, and Computers—reference Items, the new supertype table. Also, the common attributes Manufacturer (Make), Model, and LeaseRate have moved to the Items supertype table. This design makes the schema much more efficient because the lease-detail information now resides in the Items table, not in the subtype tables. Consequently, the stored procedure to generate the lease-detail report, GetInvoiceDetails2, is much smaller, as Listing 2 shows, and less resource-intensive.
To demonstrate the difference in performance, let's execute GetInvoiceDetails2 against the SuperSub database:
USE SuperSub
GO
EXEC GetInvoiceDetails2 1
As the code in GetInvoiceDetails2 shows, you now need to query only three tables to obtain the same lease report that the first procedure returns. The first block returns item information; the second block returns the total monthly amount billed. The following query produces the same report:
SET STATISTICS IO ON
EXEC GetInvoiceDetails2 1
However, the STATISTICS IO numbers have dropped:
Table 'Items'. Scan count 26, logical
reads 52, physical reads 0, read-ahead reads 0.
Table 'Leases_Items'. Scan count 2, logical
reads 4, physical reads 0, read-ahead reads 0.
Table 'Leases'. Scan count 1, logical reads 2,
physical reads 0, read-ahead reads 0.
GetInvoiceDetails2 produces 29 scans and 58 logical reads on three tables. That's 6 scans, 12 reads, and 4 tables fewer than GetInvoiceDetails—a significant savings, but you can still do better.
The Icing on the Cake
With the previous two stored procedures, you had to use an additional query to return the total monthly amount billed. Using GetInvoiceDetails in the ManyToMany database, you needed a sum of columns from three tables. Running GetInvoiceDetails2 in the SuperSub database, you needed similar logic because of the GROUP BY statement. However, in the SuperSub database schema, you can now obtain the monthly amount by summing the LeaseRate column in one table, Items. Therefore, you can omit the separate query and simply use the GROUP BY clause's ROLLUP operator. You use SQL Server's ROLLUP operator with the GROUP BY clause in situations such as this, to return grand-total information.
Let's look at the final version of the lease-detail stored procedure, which Listing 3 shows. There's a lot less code than in GetInvoiceDetails, but also note how you can now generate the entire report by using a single SELECT statement that uses the ROLLUP operator, instead of by unioning multiple result sets. ROLLUP produces more summary information than you need, so you can use the HAVING clause to eliminate unnecessary rows from the result set.
For performance comparison, let's look at the STATISTICS IO output from the final stored procedure. Run GetInvoiceDetails3 with STATISTICS IO turned on:
SET STATISTICS IO ON
EXEC GetInvoiceDetails3 1
and you get the following statistical output:
Table 'Items'. Scan count 13, logical reads 26,
physical reads 0, read-ahead reads 0.
Table 'Leases_Items'. Scan count 1, logical
reads 2, physical reads 0, read-ahead reads 0.
Table 'Leases'. Scan count 1, logical reads 2,
physical reads 0, read-ahead reads 0.
From the GetInvoiceDetails original numbers of 35 scans and 70 logical reads of 7 tables, the statistics have dropped dramatically to 15 scans and 30 logical reads of 3 tables—less than half the original total. That's a major performance savings for a common operation.
Now that you've seen how implementing a less-common relationship can generate real performance improvements, I hope you'll begin to question whether the typical and seemingly correct way to implement your database design is the only way—or the most efficient way. Changing the schema of an operational database is a major undertaking and sometimes impractical, but your future database implementations can only benefit from some exploration into the realms of less-common relationships. The "Big 3" relationships are just part of the database-implementation picture; I encourage you not to get a false sense of completion once you've mastered them. And next time you're reading up on database relationships, don't be so fast to put down the book when you come across an unfamiliar one. Your processors will thank you for it.
End of Article
Prev. page
1
2
[3]
next page -->
