BUG: Higher Cost Executing Query on View with Subquery

Last reviewed: July 3, 1997
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 onward. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.


Additional query words: performance
Keywords : kbbug6.50 kbenv kbusage SSrvTran_SQL
Version : 6.5
Platform : WINDOWS
Issue type : kbbug
Resolution Type : kbworkaround


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: July 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.