FIX: Higher Cost Executing Query on View with Subquery

• Microsoft SQL Server version 6.5

SYMPTOMS

For SQL Server version 6.5 U.S. Service Pack 1 and later, execution time is slower for executing a query with a conditional clause on a view created with a subquery than for the comparable query run directly against the underlying tables. SET STATISTICS IO ON shows a higher scan count value when the query is executed against the view rather than the underlying tables.

CAUSE

The cost of creating the worktable for the subquery is not affected by the conditional clause when executed against the view.

• Without the condition, the worktable creation cost is identical for the view and the query.
• With the condition:

      - The worktable creation cost is reduced for the query as a result of
        the reduction in applicable rows.
  

      - The worktable creation cost for the view is the same as without the
        condition; there is no reduction in cost.
  

WORKAROUND

To improve performance, do either of the following:

• Execute the query on the underlying tables rather than on the view.

  -or-

• Create a view that does not contain a subquery.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 U.S. Service Pack 1 and later. This problem has been corrected in the latest U.S. Service Pack for SQL Server version 6.5. For more information, contact your primary support provider.