FIX: Higher Cost Executing Query on View with Subquery
Last reviewed: December 31, 1998
Article ID: Q170803
|
|
The information in this article applies to:
- 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.
|
Additional query words: performance
Keywords : kbbug6.50 SSrvTran_SQL kbfix6.50.sp5 kbenv kbusage
Version : 6.5
Platform : WINDOWS
Issue type : kbbug
Solution Type : kbfix
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
|
Last reviewed: December 31, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.
