Bookmark lookup (Why)

I can't figure out why i have a bookmark lookup cost on ValidVendorPackages
on the below proc:
Select PriceViewHotelPrice.price as Totalprice, PriceViewHotelPrice.Docid
from price_view PriceViewHotelPrice Inner Join
(
select C1.Priceid as Pid,C1.VendorPackageId from
(
select distinct(HA.PriceId), HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where (Vendor = @.Vendor or @.Vendor = '')
and (Sitecode = @.Sitecode or @.Sitecode = '')
and (PackageType = @.PackageType or @.PackageType = '')
and (RequiredItems = @.RequiredItems or @.RequiredItems = -1)
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.Destination
and LengthOfStay = @.LengthOfStay
and Ages = @.Ages
and ComponentType = 'H'
and (ValidItemType = @.HotelValidItemType_1 or @.HotelValidItemType_1 = '' )
and (ItemValue = @.HotelItemValue_1 or @.HotelItemValue_1 = '' )
)
)
) C1
) HotelCriteriaPriceId on PriceViewHotelPrice.priceid =
HotelCriteriaPriceId.Pid
and PriceViewHotelPrice.keydate between @.KeyDateMin and @.KeyDateMax
and ((PriceViewHotelPrice.price) between @.PriceLow and @.PriceHigh or
@.PriceLow = -1)
and (PriceViewHotelPrice.Volatility between @.VolatilityMin and
@.VolatilityMax or @.VolatilityMin = -1)
and (PriceViewHotelPrice.AvgMinsUpdateInterval between
@.AvgMinsUpdateIntervalMin and @.AvgMinsUpdateIntervalMax or
@.AvgMinsUpdateIntervalMin = -1)
Order by TotalPrice
can anyone see why?
will
On Wed, 15 Dec 2004 11:55:04 -0800, we7313 wrote:

>I can't figure out why i have a bookmark lookup cost on ValidVendorPackages
>on the below proc:
Hi we7313,
Without knowing your table structure, indexes, etc, I can't do anything
but guess. Peruse at your own risk :-)
Looking at this part of the query:
>select distinct(HA.PriceId), HA.VendorPackageId from Criteria HA Inner Join
>(
>select VendorPackageId from ValidVendorPackages
>where (Vendor = @.Vendor or @.Vendor = '')
>and (Sitecode = @.Sitecode or @.Sitecode = '')
>and (PackageType = @.PackageType or @.PackageType = '')
>and (RequiredItems = @.RequiredItems or @.RequiredItems = -1)
>)HB on HA.VendorPackageId = HB.VendorPackageId
>and
>(
>CriteriaId in
>(
>select CriteriaID from ValidItemCriteria
>where Destination = @.Destination
>and LengthOfStay = @.LengthOfStay
>and Ages = @.Ages
>and ComponentType = 'H'
>and (ValidItemType = @.HotelValidItemType_1 or @.HotelValidItemType_1 = '' )
>and (ItemValue = @.HotelItemValue_1 or @.HotelItemValue_1 = '' )
>)
>)
I think that the optimizer has decided to start with reducing the number
of matching rows in Criteria by checking the CriteriaId in (...)
condition. Criteria rows that match are then used in the join against
ValidVendorPackages (note: the derived table is easily transformed in a
simple join; the optimizer will probably execute it as a simple join).
Obviously, you have a nonclustered index on the column VendorPackageId in
ValidVendorPackages. In order to check the other conditions (on Vendor,
Sitecode, PackageType and RequiredItems) as well, SQL Server has to fetch
the complete row. The nonclustered index used to find the VendorPackageId
will also contain the values for the clustered index on this table: they
serve as a pointer to the complete row. SQL Server will now take these
values and use them to navigate the clustered index down to the row that
corresponds to the index entry. This action (using the clustered index
values stored in the nonclustered index to find the row) is called a
bookmark lookup.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)