Execution plan question

From: we7313 (we7313_at_discussions.microsoft.com)
Date: 12/23/04

  • Next message: we7313: "Read this for a more simple example" 
    Date: Thu, 23 Dec 2004 12:05:01 -0800

    In the proc posted below I should only be hitting the partitions that apply.
    But when I look at the execution plan It show me accessing all of them.

    The Partitioned tables look like this (on Keydate):

    KEYDATE int 4
    Priceid int 4
    Price smallmoney 4
    Seed smalldatetime 4
    DocId int 4
    Volatility decimal 5
    UpdateCount int 4
    AvgMinsUpdateInterval int 4

    The Proc Looks like this:

    Select PriceViewHotelPrice.price as Totalprice, PointerH as
    Pointer,PriceViewHotelPrice.Docid from price_view PriceViewHotelPrice Inner
    Join
            (
            select C1.Priceid as Pid,C1.VendorPackageId, C1.ComponentPointer as
    PointerH from
                    (
                    select distinct(HA.PriceId), HA.VendorPackageId, HA.ComponentPointer from
    CriteriaSelect 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 point out why I'm still hitting all the partitions? 

    -- 
will
  • Next message: we7313: "Read this for a more simple example"