Microsoft KB Archive/170803

-

{| The information in this article applies to:
 * width="100%"|
 * Microsoft SQL Server version 6.5

BUG #: 17054 (NT: 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.
 * }

-

Last reviewed: December 31, 1998

© 1998 Microsoft Corporation. All rights reserved. Terms of Use.