Post reply

Efficiency of Scalar Function in SQL Server 2005 Express

  • March 17, 2010 at 5:52 pm

    #221922

    Hi,

    I am using SQL Server Express 2005 and have written a Scalar function that calculates the billing amount for a client. This function is included as part of a select statement (for output to a spreadsheet). The function includes the following lines that retreive any previous amounts billed to incorporate them into the final billing amount calculation. IsNull has been used as there are many times when there are no previous payment line items. All variables are type 'money' except for @ProcessID which is 'int'.

    When these lines are included in the function it slows down the retrieval of information into the Select statement. Is there a more efficient way to retrieve this information.

    SET @Part1SuspensionAmt = ISNULL((SELECT dbo.tblFeesBilling.BilledAmount FROM dbo.tblFeesBilling WHERE dbo.tblFeesBilling.FeeID = 157 AND dbo.tblFeesBilling.ProcessID = @ProcessID), 0)

    SET @Part2SuspensionAmt = ISNULL((SELECT dbo.tblFeesBilling.BilledAmount FROM dbo.tblFeesBilling WHERE dbo.tblFeesBilling.FeeID = 39 AND dbo.tblFeesBilling.ProcessID = @ProcessID), 0)

    SET @Part3SuspensionAmt = ISNULL((SELECT dbo.tblFeesBilling.BilledAmount FROM dbo.tblFeesBilling WHERE dbo.tblFeesBilling.FeeID = 40 AND dbo.tblFeesBilling.ProcessID = @ProcessID), 0)

    SET @Part4SuspensionAmt = ISNULL((SELECT dbo.tblFeesBilling.BilledAmount FROM dbo.tblFeesBilling WHERE dbo.tblFeesBilling.FeeID = 41 AND dbo.tblFeesBilling.ProcessID = @ProcessID), 0)

    SET @Part5PartPaymentAmt = ISNULL((SELECT dbo.tblFeesBilling.BilledAmount FROM dbo.tblFeesBilling WHERE dbo.tblFeesBilling.FeeID = 123 AND dbo.tblFeesBilling.ProcessID = @ProcessID), 0)

    SET @Part6PartPaymentAmt = ISNULL((SELECT dbo.tblFeesBilling.BilledAmount FROM dbo.tblFeesBilling WHERE dbo.tblFeesBilling.FeeID = 124 AND dbo.tblFeesBilling.ProcessID = @ProcessID), 0)

    SET @Part7PartPaymentAmt = ISNULL((SELECT dbo.tblFeesBilling.BilledAmount FROM dbo.tblFeesBilling WHERE dbo.tblFeesBilling.FeeID = 125 AND dbo.tblFeesBilling.ProcessID = @ProcessID), 0)

    SET @Part1TotalReductionAmt = @Part1SuspensionAmt

    SET @TotalReductionAmt = @Part1SuspensionAmt + @Part2SuspensionAmt + @Part3SuspensionAmt + @Part4SuspensionAmt + @Part5PartPaymentAmt + @Part6PartPaymentAmt + @Part7PartPaymentAmt

    SET @BillingAmount = @InitialAmount - @TotalReductionAmt

    RETURN @BillingAmount

  • March 17, 2010 at 7:46 pm

    #1135676

    First, it would help to see the code for the scalar function. Second, it may be worthwhile to seeif the scalar function can be converted to an inline TVF and used in a CROSS APPLY in the FROM clause.

    To see why, check out my blog post: http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/05/07/comparing-hardcoded-functions-in-line-tvf-s-and-scalar-functions.aspx.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply