FIX: Views Containing a UNION Resolve with Materializing Model

• Microsoft SQL Server version 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 

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

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