FIX: Views Containing a UNION Resolve with Materializing Model

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q163082

BUG#: 16522 (6.5)

Expand all | Collapse all

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.

Back to the top

WORKAROUND

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

Back to the top

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 (http://support.microsoft.com/kb/197177/ ) INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

Back to the top

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

Back to the top

APPLIES TO

Microsoft SQL Server 6.5 Standard Edition

Back to the top

kbbug kbfix kbusage KB163082

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top