SQL Server Performance

Deploy view or use function for lookup

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Hmnt, Oct 31, 2009.

  1. Hmnt New Member

    I've two master tables **Org** & **Item** and then there's an **OrgItem** table.I have to fetch *ItemCodes* for a particular Org.
    TABLE STRUCTURE:
    - Org ( Id, OrgCode, Name) - Org Master table
    - Item ( Id, ItemCode, Name) - Item Master table
    - OrgItem ( ItemId, OrgId, ItemCode) - Some Org specific ItemCodes
    ----------
    Now only some Item's have Org specific ItemCode so by default I fetch the ItemCode from Item but if for a particular Item+Org combination **if its record exists** in the OrgItem table then I have to fetch the ItemCode from the OrgItem table.
    Creating a **View_OrgItem_Item** might be a solution but it wud mean a view which consists of n-fold the size of the original Item table (where n = number of Orgs).
    Another option is to create a **GetItemCode(ItemId, OrgId)** which does the lookup and return either Item.ItemCode or ORgITem.ItemCode
    So, my question is - shud I go for a 'View_OrgItem_Item' or simply create a function
    GetItemCode(ItemId, OrgId)?
    Which one is best in terms of performance? Any other factors to consider.
    **Note: It is NOT necessary that ALL the Orgs have recrds in OrgItem*
  2. Adriaan New Member

    I would not create a functions to do the work of plain old CASE, unless you need the same functionality in more than one query.
    Something like:
    SELECT
    CASE WHEN Item = 'x' AND Org = 'y' THEN Item.ItemCode
    ELSE (CASE WHEN OrgItem.OrgItem IS NOT NULL THEN OrgItem.OrgItem ELSE Item.ItemCode END) END
    FROM OrgItem LEFT JOIN Item ON OrgItem.key = Item.key
    If you do create a UDF, do not let it do the lookup in the other table. You keep the join in the main query, and you feed the column values to the UDF for evaluation (which will use pretty much the same CASE expression as above).
    In case you move the lookup action to the UDF, you won't profit from any indexes that do support the join operation in the main query.

Share This Page