BUG: Views Containing a UNION Resolve with Materializing Model

• Microsoft SQL Server, version 6.5

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 Microsoft SQL Server 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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

• 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 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

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