FIX: Non-Optimal Performance for Views Defined with a Subquery

ID: Q175097

The information in this article applies to:

Microsoft SQL Server version 6.5 Service Pack 3 and later

BUG #: 16803 (6.5)

SYMPTOMS

If you run a query on a view defined with a subquery in its select list, you may experience impaired performance. However, a direct query for the equivalent select with no view (with or without a predicate against the view) yields much faster performance.

WORKAROUND

To resolve this problem, obtain and install the latest Service Pack for SQL Server 6.5. For more information, see the STATUS section of this article.

NOTE: To use the fix in the latest Service Pack, you must re-create the view with trace flag 349 enabled. For additional information, see the MORE INFORMATION section of this article.

To work around this problem and improve performance, do either of the following:

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

-or-

Create the view so that it does not contain a subquery for the select list.

NOTE: Views created under versions of SQL Server earlier than 6.5 Service Pack 1 with a subquery in the select list will continue to work, even under SQL Server 6.5 Service Pack 1 and later, provided that they are not re-created under these later versions of SQL Server.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Packs 1, 2, and 3. This problem was corrected in the latest Microsoft SQL Server 6.5 U.S. Service Pack. For information on obtaining the service pack, query on the following word in the Microsoft Knowledge Base (without the spaces):

S E R V P A C K

MORE INFORMATION

To use the fix for this bug, you must enable trace flag 349 (which was newly added). To ensure that the proper queries take advantage of this fix, you may consider starting the server with the trace flag enabled, so that all connections have the trace flag enabled. The SQL Server Setup program (in Server Options) provides a means to add startup parameters. Add the parameter -T349 to the list. You can enable the trace flag for a specific connection by executing DBCC TRACEON(349). Doing this is required to re-create the views(s) in question to invoke the new behavior.

Additional query words: sp sp1 sp2 sp3


Keywords          : kbusage SSrvTran_SQL kbbug6.50.sp1 kbbug6.50.sp2 kbbug6.50.sp3 kbfix6.50.sp4 
Version           : 6.5 SP1 SP2 SP3
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 17, 1999