Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 511

is to denormalize selective data by moving it up into higher levels of domain hierarchy so you can reduce
the need to traverse multiple JOIN levels for the information. You should have a good reason before you
go down the denormalization road. It should be something that you do selectively.

If you see some abnormal I/O usage during the development process, dig into those procedures to see
what manipulations are being performed to find specific instances for denormalization. An example is
a model where there are trips that have many stops. Each stop is given an order sequence so that it can
be determined in which order the stops are encountered. Trip entities are inserted first and then all the
stops are added at once and related to the trips. During your benchmarking process, you may notice
some queries that have logic to determine the ultimate origination and destination of a trip from the stop
records. Figure 14-4 shows an example of a normalized and denormalized approach to modeling this trip
and stop relationship.

Normalized Denormalized

Tr i p Tr i p

PK Tripld PK Tripld

Stop Stop

PK Stopld PK Stopld

Originationld
Destinationld

Originationld
Destinationld
StopSequenceld
Tripld

Originationld
Destinationld
StopSequenceld
Tripld

FK1 FK1

Figure 14-4

If you are using the normalized model version, your query logic first aggregates the join between the trip
and stop to find the last StopSequenceId for each trip. This stop should be the Ultimate Destination. The
Ultimate Origin of a trip is the stop with the StopSequenceId of one (1). With both StopSequenceIds, you
just need to join both back to the trip and summarize to get the two rows into one result. The query is
messy and looks something like this:

--Summarize to One Row Per Trip
SELECT TripID, MAX(UltOrigId) As UltOrigId, MAX(UltDestId) as UltDestId
FROM (
--Gets One row by Trip For Origin / One row by Trip for Destination
SELECT TRP.TripId, UltOrigId = Case when STP1.StopSequence = 1
then STP1.OriginId else null end,
UltDestId = case when STP1.StopSequence = DEST.StopSequence
then STP1.DestinationId else null end

FROM TRIP TRP
INNER JOIN (

--Get the Last Sequence
SELECT T.TripId, MAX(StopSequence) as StopSequence
FROM STOP STP
INNER JOIN TRIP T
ON STP.TripId = T.TripId
Group By T.TripId
) DEST
ON TRP.TripId = DEST.TripId
INNER JOIN STOP STP1
ON DEST.TripId = STP1.TripId