Performance may decrease when you run a query to retrieve rows from a view that is created by using a UNION operation (885715)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)

SYMPTOMS

When you run a query that includes the ORDER BY clause to retrieve rows from a view that is created by using a UNION operation on tables, performance may decrease. This problem occurs if a clustered index is defined on the column that you use in the ORDER BY clause.

CAUSE

The query uses the merge union operation that always requests a sort operation on all the columns that are involved in a union. Therefore, a separate sort that is based on the ORDER BY clause in the query is not required. However, the query optimizer incorrectly selects an execution plan that performs an additional sort based on the ORDER BY clause. This plan is less efficient and causes the problem that is mentioned in the "Symptoms" section.

WORKAROUND

To work around this problem, you must define a unique clustered index instead of the clustered index that is defined on the ORDER BY column. Additionally, make sure that the following conditions are true:
  • The first column of the view must be the column where the unique clustered index is defined.
  • The first column in the unique clustered index must be the ORDER BY column.
For more information about the CREATE UNIQUE CLUSTERED INDEX statement, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_64l4.asp

MORE INFORMATION

Steps to reproduce the problem

  1. Start SQL Query Analyzer, and then connect to an instance of SQL Server 2000.
  2. In a sample database, create a table, and then define a clustered index on a column in the table. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    CREATE TABLE TableA(i int, j int, k int)
CREATE CLUSTERED INDEX TableAIndex on TableA(i, j)
GO
  3. Create another table, and then define a clustered index on a column in the table. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    CREATE TABLE TableB(i int, j int, k int)
CREATE CLUSTERED INDEX TableBIndex on TableB(i, j)
GO
  4. Create a view that uses the UNION ALL operator to select the data from the two tables that you created in step 2 and step 3 as one result set. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    CREATE VIEW  TestView
AS
SELECT i, j, k from TableA
UNION ALL
SELECT i, j, k from TableB
GO
    Note You can also use the UNION operator to select the data from the two tables.
  5. To obtain detailed information about how the Transact-SQL statements are executed, turn on the SET SHOWPLAN_TEXT option. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    SET SHOWPLAN_TEXT ON
GO
  6. Retrieve the rows from the view that you created in step 4 in a SELECT query, and then turn off the SET SHOWPLAN_TEXT option. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    SELECT * FROM TestView ORDER BY i
GO
SET SHOWPLAN_TEXT OFF
    The following output is listed in the Results pane:
      |--Sort(ORDER BY:([Union1004] ASC))
       |--Concatenation
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[TableA].[TableAIndex]))
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[TableB].[TableBIndex]))
    This execution plan is not an optimal execution plan. Therefore, you may notice the problem that is mentioned in the "Symptoms" section.
If you work around this problem, and then follow step 5 and step 6, the following output is listed in the Results pane:
  |--Merge Join(Concatenation)
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[TableA].[TableAIndex]), ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[TableB].[TableBIndex]), ORDERED FORWARD)
This execution plan is a more efficient execution plan for the query.
Modification Type:MajorLast Reviewed:10/21/2004
Keywords:kbprb kbPerformance kbView kbQuery kbtshoot KB885715 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.