SQL Server Performance

Multi-valued function with CTE

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sekharabcs, Aug 18, 2009.

  1. sekharabcs New Member

    Hi All,
    I am getting warning "No join predicate" in the below function:
    --------------------------------------------------------------------------------------------
    ALTER Function hello.Test
    ( @Customer VarChar(20) = Null
    ,@Item VarChar(20) = Null
    ,@UC VarChar(10) = Null
    ,@EF VarChar(10) = Null
    ,@OD DateTime = Null
    )
    Returns Table
    As
    Return

    ( With CTE_Data (Item, Unit, ListPrice, ItemDG, VATCode, UnitWeight, ItemWeight, BaseUnit, UoMRatio)
    As (Select
    Item = I.No_
    ,Unit = Coalesce(U.Code, I.[Sales Unit Of Measure])
    ,ListPrice = I.[Unit Price]
    ,ItemDG = I.[Item Disc_ Group]
    ,VATCode = I.[VAT Prod_ Posting Group]
    ,UnitWeight = Coalesce(U.Weight, 0)
    ,ItemWeight = Coalesce(I.[Gross Weight], 0)
    ,BaseUnit = I.[Sales Unit of Measure]
    ,UoMRatio = Coalesce
    ( U.[Qty_ per Unit of Measure]
    , (Select Convert(Int, Left(@UC, 1)) Where @UC Like '[0-9]%')
    , (Select Convert(Int, Right(@UC, 1)) Where @UC Like '%[0-9]')
    , 1
    )
    From Van.[Item] I
    Left Join Van.[IUOM] U On I.No_ = U.[Item No_] And U.Code = Coalesce(@UC, I.[SUOM])
    Where I.No_ = Coalesce(@Item, I.No_)
    )
    Select Customer
    ,Item
    ,Unit
    ,ListPrice
    ,ItemDG
    ,VATCode
    ,Weight
    ,BaseUnit
    ,UoMRatio
    ,Discount
    ,DiscountAmount = ListPrice * (Discount / 100)
    ,NetPrice = ListPrice - (ListPrice * (Discount / 100))
    ,SpecialPrice
    ,CurrSpecialPrice
    ,EncoreDiscount
    From
    ( Select Customer = C.No_
    ,Item
    ,Unit
    ,ListPrice = Coalesce
    ( It.GSL
    ( Item
    ,Coalesce(@OD, GetDate())
    ,@UC
    ,BaseUnit
    ,UoMRatio
    ,''
    ,1
    )
    ,ListPrice * UoMRatio
    )
    ,ItemDG
    ,VATCode
    ,Weight = Case UnitWeight
    When 0 Then ItemWeight * UoMRatio
    Else UnitWeight
    End
    ,BaseUnit
    ,UoMRatio
    ,Discount = It.GLD
    ( C.No_
    ,Item
    ,C.[Customer Disc_ Group]
    ,ItemDG
    ,Coalesce(@OD, GetDate())
    ,@UC
    ,BaseUnit
    ,1
    )
    ,SpecialPrice = It.GSP
    ( C.No_
    ,Item
    ,C.[Customer Price Group]
    ,Coalesce(@OD, GetDate())
    ,@UC
    ,BaseUnit
    ,UoMRatio
    ,''
    ,1
    )
    ,CurrSpecialPrice = It.GSP
    ( C.No_
    ,Item
    ,C.[Customer Price Group]
    ,Coalesce(@OD, GetDate())
    ,@UC
    ,BaseUnit
    ,UoMRatio
    ,C.[Currency Code]
    ,1
    )
    ,EncoreDiscount = It.GED
    ( Item
    ,ItemDG
    ,Coalesce(@OD, GetDate())
    ,@UC
    ,BaseUnit
    ,@EF
    ,1
    )

    From CTE_Data, Van.Customer C
    Where C.No_ = Coalesce(@Customer, C.No_)
    ) As CTE
    )
    --------------------------------------------------------------------------------------------
    It would be great if anyone suggests me a solution for this warning. Also, could you please let me know if any performance tuning can be done on the above code. This UDF is used in many Stored Procedures in JOINS and hence I need to check is any tuning can be done. I have tried with several options to remove CTE and use Temporary tables, however I could not succeed.
    Sorry for the big question, Many thanks!!
    Cheers
    ABC
  2. Adriaan New Member

    No idea really, but your last FROM clause does not join the CTE with the Van table.
    The WHERE clause only filters the rows from the Van table (alias C). This should return the carthesian product of the filtered rows from Van against your CTE.
    Try and replace all the complex expressions and aliases with a simple 'blabla' string, just to see if you can get a grip on the actual structure of the query.
  3. sekharabcs New Member

    Thanks Adriaan and Satya for your most valuable suggestion. I will check for getting grip on the query first.
    Regards,
    ABC
  4. satya Moderator

    Welcoeme to the forums.
    One of the article refers:
    Under most circumstances, it is more efficient to process an inner join before an outer join because the inner join is more likely to reduce the number of rows that require more processing. The optimizer starts off with an initial join ordering based on this criteria, and then considers various permutations until it finds a good execution plan. Under certain circumstances, this reordering might result in a cartesian product because of the lack of a join predicate.
    Coming to SQL 2005:
    When additional join predicates are involved, this problem may increase the estimated cost of the JOIN operator to the point where the query optimizer chooses a different join order. When the query optimizer chooses a different join order, SQL Server 2005 system performance may be slow.

Share This Page