Re: Bookmark lookup (Why)

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 21:49:50 +0100

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)

Relevant Pages

  • Re: IS NULL on field is not using index placed on that field
    ... > criteria in your WHERE clause the query engine needs to look through 2 ... > NULL" you get a nice quick plan using your nonclustered index too. ... > efficiently by just scanning the whole clustered index until it's got ... > even took out the null values and made the DeliverDate column NOT NULL ...
    (microsoft.public.sqlserver.programming)
  • Re: Clustered index vs. nonclustered index for GUID primary key
    ... The table, or the clustered index ... If we now add a nonclustered index on column D, ... It may, however, store the data from some columns ... >>the query could use a clustered index. ...
    (microsoft.public.sqlserver.server)
  • Re: Clustered index vs. nonclustered index for GUID primary key
    ... The table, or the clustered index ... If we now add a nonclustered index on column D, ... It may, however, store the data from some columns ... >>the query could use a clustered index. ...
    (microsoft.public.sqlserver.programming)
  • Re: Index chosen dependant on result columns
    ... The result columns have a great deal to do with the optimal query ... with column A, the key of the clustered index of the table, A, to refer ... The cost to use the nonclustered index will include 10000 lookups in the ... instead chooses the plan that simply scans the entire clustered index, ...
    (microsoft.public.sqlserver.programming)