FIX: Views Containing a UNION Resolve with Materializing Model

ID: Q163082

The information in this article applies to:
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 version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

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:

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          : kbusage SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 21, 1999