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

WHERE DEST.StopSequence = STP1.StopSequence
OR STP1.StopSequence = 1
) OUTERQRY
GROUP BY TripID
By moving the destination and origination fields up to the TRIP entity as well as the STOP, the query
will get much easier, reduce the need for three joins, and decreases I/O requirements. This change is
equivalent to using the denormalized version of the relationships in Figure 14-4. The query is now simple.
SELECT TripId, OriginationId, DestinationId FROM TRIP
This change will also not be a big deal for the software since the logic was already inserting all the stops
at one time. The Trip will now additionally need to be updated when the stops are inserted. The big
consideration here is what this extra update costs and how that compares with the decrease in the cost of
traversing through those relationships. These types of determinations need to be made before too much
denormalization occurs.
Performance Effect of Functions inViews
Views provide great abstractions for development activity. The same convenience they provide can
become a performance nightmare when developers don't look behind the view and begin to do things
like building views on top of views or embedding user-defined functions in views. If you have a standard
to name views with a preceding v or similar marking, you can find the instances where views are stacked
by searching with wildcards in the sysComments table like this:
Select * from sysComments where text like '%from v%' and type = 'V'
When views have embedded user-defined functions, you may not realize that you are incurring heavy
costs for running them. If you are just looking at the XML query execution plan, the optimizer minimizes
the cost by showing low percentage scalar operators. Don't neglect to look at the full query execution plan
to see the detailed and broken out costs. You can see an example using the AdventureWorks user-defined
function ufnGetProductListPrice in a view to calculate the current product list price across all the
products with today's date like this.
CREATE VIEW vGetProductListPriceForToday
AS
Select ProductId, dbo.ufnGetProductListPrice(ProductId, getdate()) as CurrentPrice
FROM Production.Product
When you simply select from the new view, the query plan looks like Figure 14-5.
Figure 14-5