SQL Server Performance

Restriction on Functions

Discussion in 'General Developer Questions' started by SanetteWessels, Nov 10, 2005.

  1. SanetteWessels New Member

    Hi All,

    I hope there is someone out there that will be able to help me with the following problem I have.

    I have a function that gets called 42 times per Inventory to calculate SafetyStock based on different Service Levels. The problem I originally had was that the function selected data out of a very large table (more than 200Million rows). This particular table is partitioned. I have written code to look at the specific table but cannot execute the code within the function because one cannot execute dynamic sql within the function. BOL suggests I use an extended procedure or another function.

    My problem is how do I select from a table where the name of the table sits in a variable without using dynamic SQL in the view?

    E.g: SELECT InventoryID
    FROM @cTableName
    WHERE InventoryID = @iInventory

    Any suggestions would be great!!

    Thanks

    SQL Developer
    JustEnough Software Corporation
    Walking on water and coding of a spec is easy, as long as both are frozen - A friend
  2. ranjitjain New Member

    Well,
    In this or any case where table name is dynamic you have to use dynamic query to execute and get result.
  3. Madhivanan Moderator

    Dynamic SQL is not allowed inside a Function. You need to use Stored Procedure for that
    If you want to pass the table as variable then Dynamic SQL is the only way

    Refer this
    http://www.sommarskog.se/dynamic_sql.html

    Madhivanan

    Failing to plan is Planning to fail
  4. Adriaan New Member

    Your function is querying a table with 200 million rows, and you don't know the name of that table upfront?

    How many such tables do you have, and how many of them do you need to query with that function? Consider creating a separate function for each table.

    Does your function do anything that cannot be done in a subquery or a derived table?

    Functions can be particularly bad if you're calling them for rows where the parameter(s) that you feed to the function are repetitive - so find a way to 'normalize' those parameters. Like using an embedded derived table with a GROUP BY on the parameters, with an outer derived table calling the function with the distinct parameters.
  5. mmarovic Active Member

    Can't you query partitioned view by its name instead of table that implements specific partition? What is the column which value is used for partitioning?
  6. SanetteWessels New Member

    Hi everyone,

    The code that call the function looks like this:

    -- Update SafetyStock
    UPDATE #ServiceLevel
    SET SafetyStock = dbo.udf_ReturnSSBasedOnNeuralNet ( SS.ServiceLevel,
    IP.LeadTimeDays,
    IP.ReplenishmentCycleDays,
    IP.ReviewPeriodDays,
    IP.LeadTimeVariance,
    IP.ForecastVariance,
    @iDaysInMonth,
    IP.InventoryID,
    GetDate() ,
    @bSalesPartitioned,
    @bForecastPartitioned)
    FROM #ServiceLevel SS
    INNER JOIN tbl_InventoryPolicy IP
    ON SS.InventoryID = IP.InventoryID

    I populate a table with different ServiceLevels ranging from 53% upto 100%. This is stored per InventoryID (as each InventoryID could potentialy have different PolicySetting (see other paramaters passed to function). The function then calculates the SafetyStock based on all parameters passed. The view access partitioned tables. The partitioned tables have been set to run per range of InventoryID's i.e. tbl_InventoryHIstory_1_10000, tbl_InventoryHistory_10001_20000 etc... There is then a view called 'tbl_InventoryHistory' which the function looks at currently (This is the 'table' that has over 200Million records. I need to change the code now to only look at a specific table i.e tbl_InventoryHIstory_1_10000 and NOT THE view 'tbl_InevntoryHistory'. In order to do that I have a lookup table with all the table names in to get the exact table name I have to access. That is the reason why I "do not know upfront" what the name of the table is going to be. And that is also the reason why the name of the table sits in a variable. Hope this all makes sense.

    BTW, this is the code that runs inside the function to check which tableName to use. The names differ from client to client.

    -- Get specific InventoryHistory table.
    SELECT @cTableName = TableName
    FROM ctbl_ForecastNames
    WHERE @iInventoryID BETWEEN TableStart AND TableEnd



    SQL Developer
    JustEnough Software Corporation
    Walking on water and coding of a spec is easy, as long as both are frozen - A friend

Share This Page