optimizing a sql2005 union view

Giganews Newsgroups
Subject: optimizing a sql2005 union view
Posted by:  Roger (lesperanc…@natpro.com)
Date: Wed, 31 Aug 2011

I=92ve got a table, with an itemType field
The value of this field is 1-6, and I=92ve created 6 views based on this
field, returning the same data set but from different fields in the
table
Then I create a view which is the union of these 6 views, see below

When I execute this in the sql 2005 EM without the =91description=92
field, the 9 rows return in < 1sec
If I include the description field (max len is 62 chars), it takes 4
secs to return the same 9 rows

Analyizing with the DTA suggests nothing

How can I  optimize this view

select * from (
select partsQuoteId, lineNum, seq, itemType, item, description from
qryPartsQuoteDetail1
union
select partsQuoteId, lineNum, seq, itemType, item, description from
qryPartsQuoteDetail2
union
select partsQuoteId, lineNum, seq, itemType, item, description from
qryPartsQuoteDetail3
union
select partsQuoteId, lineNum, seq, itemType, item, description from
qryPartsQuoteDetail4
union
select partsQuoteId, lineNum, seq, itemType, item, description from
qryPartsQuoteDetail5
union
select partsQuoteId, lineNum, seq, itemType, item, description from
qryPartsQuoteDetail6
) AS a
where partsQuoteId =3D 2546

Replies