Microsoft KB Archive/885715

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

Article ID: 885715

Article Last Modified on 4/29/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-





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 &quot;Symptoms&quot; 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://msdn2.microsoft.com/en-us/library/aa258260(SQL.80).aspx



Steps to reproduce the problem
 Start SQL Query Analyzer, and then connect to an instance of SQL Server 2000.  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   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   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.   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   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:

<pre class="fixed_text"> |--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 &quot;Symptoms&quot; section. </li></ol>

If you work around this problem, and then follow step 5 and step 6, the following output is listed in the Results pane:

<pre class="fixed_text"> |--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.

Keywords: kbprb kbperformance kbview kbquery kbtshoot KB885715

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.