View Hints

View definitions can affect performance in two ways: during SQL view generation and when Microsoft® SQL Server™ 2000 compiles a SQL view before executing it. The following hints can help you achieve better performance along these two dimensions.

Choose an unresolved view (a view that does not support version resolution) if version information is unimportant (for example, when you know that all objects are version one). The repository engine does not perform version resolution if the SQL view is flagged as unresolved.
When you have a choice between using a class-based view or an interface-based view, choose the class-based view. Interface-based views have an extra join that determines which classes implement the interface. Using a class-based view avoids the performance hit of processing the extra join.
Choose an interface-based view over a class-based view when querying a small set of interfaces where the key (IntID) is specified. This choice is often preferable because the compilation time can be so much smaller for interface-based views.
When navigating a relationship, performing the query on a junction view often runs faster than when you represent the relationship as a foreign key on a class or interface view. Using a junction view yields faster performance on average.
If you have a view that includes a text field, and you reference the text field in a SELECT clause, then you are not allowed to use SELECT DISTINCT. As a result, the query optimizer cannot eliminate certain redundant joins. A solution is to use a nested query on two interface-based views. The inner query uses DISTINCT and includes IntID in the SELECT clause, but does not reference the text field. This causes the inner query to reference a presumably smaller number (specifically, the IntID), which then joins with the interface-based view that contains the text field.

View Hints

See Also