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

• Microsoft SQL Server versions 6.5 Service Packs 1, 2, and 3

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 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.

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.