Microsoft KB Archive/163082

= FIX: Views Containing a UNION Resolve with Materializing Model =

Article ID: 163082

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q163082



BUG#: 16522 (6.5)



SYMPTOMS
When you SELECT from a view that contains one or more UNION clauses, the view is resolved using a materializing model. In other words, the view is resolved into a worktable first, and then the SELECT statement's conditional clauses are applied to the intermediate table. This procedure may have a significant impact on the performance of a query against a view.



WORKAROUND
Avoid using UNIONs inside of views and avoid requiring the use of views for queries which must span partitioned tables.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.



MORE INFORMATION
If the view does not contain any UNION clauses, a reformulating model is used. This means that the conditional expressions in the SELECT statement outside the view are effectively merged with any conditional expressions within the view, and applied directly to the underlying table(s) for the purposes of the query plan. If there are any suitable indexes on the underlying table, this procedure may result in far fewer pages being fetched.

For example, consider the following statements: Create view reformulating_view as

Select * from table_1

Create view materializing_view as

Select * from table_1 Union Select * from table_2 Assume the following:


 * Each table has 10,000 rows.
 * You write a SELECT statement containing a WHERE clause that restricts the number of rows returned to 1.
 * There is a suitable index on the column(s) specified in the WHERE clause.

In the case of the reformulating_view, the query plan uses your conditional clause to ensure that the index is used. Therefore, a few index pages and one data page are fetched from the disk or cache, and a single row is returned.

In the case of the materializing_view, 10,000 rows from TABLE_1 are inserted into an intermediate worktable; another 10,000 rows from TABLE_2 are appended to the worktable; and the worktable is scanned to restrict the result set to the single row desired. No indexes are used because table scans are required.

To observe the "showplan" and "statistics I/O" output that results from the behavior described above, use the following script: create table t1 (col1 int, col2 char(255), col3 char(255), col4 char(255)) go create table t2 (col1 int, col2 char(255), col3 char(255), col4 char(255)) go create table t3 (col1 int, col2 char(255), col3 char(255), col4 char(255))

set nocount on go

-- populate the tables with some data declare @c int select @c = 1 while (@c <= 500) begin insert t1 values (@c, 'a', 'b', 'c') insert t2 values (@c, 'a', 'b', 'c') insert t3 values (@c, 'a', 'b', 'c') select @c = @c + 1 end go

update t3 set col2 = 'abb' where col1 between 100 and 150

create index idx1 on t1 (col1,col2) create index idx2 on t2 (col1,col2) create index idx3 on t3 (col1,col2) go

create view union_view as select * from t1 union all select * from t2 union all select * from t3 go

create view union_view_ind as select * from t1(index=idx1) union all select * from t2(index=idx2) union all select * from t3(index=idx3) go

set statistics io on set showplan on go

print 'No view -- Indexes used' go select * from t1 where col1 = 125 and col2 = 'abb' union all select * from t2 where col1 = 125 and col2 = 'abb' union all select * from t3 where col1 = 125 and col2 = 'abb' go

print 'Using a view -- Indexes NOT used optimiser chooses table scan' go

select * from union_view where col1 = 125 and col2 = 'abb' go

print 'using a view created with indexes' go select * from union_view_ind where col1 =125 and col2='abb' go

Additional query words: prodsqloptimizer

Keywords: kbbug kbfix kbusage KB163082

-

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

© Microsoft Corporation. All rights reserved.