Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Usage of Functions in Stored Procedures

By Sreeju Kumar,

Usage of functions in the stored procedures

I am a big fan of functions that they can be used in our SQL stored procedures or direct queries, because they are very handy and reusable.  There are some functions that have been written in the application that I work, which will be like getting the session id or getting a date by applying the UTC Offset per the locale settings. Those functions I used to enjoy while I write new procedures. That saves some time by just calling them from my procedures and getting my work done, without knowing how actually they do so. I work in a company who develops Supply Chain Solutions for the global Supply Chain Leaders.

One fine day I got an issue from a customer saying the shipments page is not loading when he click the Shipment tab. It was taking incredibly more time and at last got timed out. On my investigation I found that the Shipment has more than 15,000 cases (These are the physical cases need to be loaded to a Truck/Trailer) associated with it. There was a function which will get the Store Id from some table with some conditions (joins). The culprit was this function. This function was called in the select statement. So for the 15,000 cases this function has been called repeatedly for the same data.

There are several ways to break down this scenario. One is to make the function inline rather than calling it. This can be done by having the function logic right there in our proc where it creates a bottleneck. This approach is good if each record set will be getting different values by the existing function call.

For my scenario the Store Id was always same for all the 15,000 records. So I used the second approach. The second approach is to get the value prior to performing the select. This will be applicable only if the value is remaining same throughout for the records. For my Shipments case it was true since all the cases belong to a particular Store.

There is a third approach. However it is unlikely to be used, since it is the best solution on a performance point of viewJ. This is applicable only if the data returned from the function is same for the entire rows.

Let’s see these concepts with an example to understand well. The Scenario is simple. We have two numbers whose sum needs to be found for each case associated with the shipment. We will see how we can do it using a function, making the call inline and using a variable outside select block.

We will create a table CaseDetail and populate the data.

CREATE TABLE CaseDetail (CaseId INT IDENTITY(1,1), QTY NUMERIC(14,4), weightedQty NUMERIC(14,4), ShipmentId INT)

--We assume that these cases belongs to Shipment with id 1

DECLARE @count INT

SET @count =15000

WHILE (@count >0)

BEGIN

INSERT INTO CaseDetail VALUES(200,300,1)

SET @count = @count -1

END

We have CaseDetail with 15,000 records inserted and they belong to the Shipment with id 1.

Now we will create the function for getting the sum calculation.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE FUNCTION [dbo].[getSum]

(

      @sid NVARCHAR(80),

      @shipmentId INT

) RETURNS NUMERIC(14,4)

AS BEGIN

DECLARE @total NUMERIC(14,4)

SELECT @total = Qty+weightedQTY FROM dbo.CaseDetail WHERE shipmentId = @shipmentId

RETURN @total

END

Now we will have the procedure which uses this function.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE [dbo].[GetItemDetails](@sid NVARCHAR(80))

AS BEGIN

DECLARE@shipmentId INT

SET@shipmentId = 1

SELECT

      QTY AS Qty,

      WeightedQTY AS WeightedQty,

      [dbo].[getSum](@sid,@shipmentId)as TOTAL

      FROM CaseDetail WHERE ShipmentId = @shipmentId

END

Let’s try executing it (EXEC [dbo].[GetItemDetails] '8C77AD39-FDD1-43B5-81CE-02600B68A376').  We can see that it is taking some considerable amount of time. For me it took 128590ms. I have used the SQL Profiler (Start - >Run ‘Profiler’ for SQL Server 2000 or ‘Profiler90’ for SQL Server 2005) to get the execution duration stated above. Now let’s see avoiding the function call and making the function logic inline in the procedure.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE [dbo].[GetItemDetails_Inline](@sid NVARCHAR(80))

AS BEGIN

DECLARE@shipmentId INT

SET@shipmentId = 1

SELECT     

      Qty AS Qty,

      weightedQTY AS WeightedQty,

      Qty+weightedQTY AS TOTAL

      FROM dbo.CaseDetail WHERE shipmentId = @shipmentId

END

While executing we can see that the performance have been increased considerably. For me it took just 506ms.

Now let’s see the best solution for the scenario, by getting the value in a variable. As I told earlier this will be applicable only if we are sure that the data is same for all the rows.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE [dbo].[GetItemDetails_Variable](@sid NVARCHAR(80))

AS BEGIN

DECLARE@shipmentId INT

DECLARE @total NUMERIC(14,4)

SET@shipmentId = 1

SELECT TOP 1 @total = qty + weightedQTY FROM CaseDetail WHERE shipmentId = @shipmentId

SELECT     

      Qty AS Qty,

      weightedQty AS WeightedQty,

      @total AS TOTAL

      FROM CaseDetail WHERE shipmentId = @shipmentId

END@total as TOTAL

End

Execution of this proc took just 384ms. 

So we have seen that calling a function from our select statement need to be chosen carefully, since it can make performance overheads when the function contains complex logic and/or there is a large number of records to return.  We can use them if we are sure that the records will be minimal always.

Hope this information was helpful.

Thank you,

Sreeju

Total article views: 11868 | Views in the last 30 days: 2
 
Related Articles
FORUM

Call Stored Procedure from a Function

Call Stored Procedure from a Function

FORUM

sys.dm_exec_cached_plans : Total plans in the procedure cache and total memory size mobilized

sys.dm_exec_cached_plans : Total plans in the procedure cache and total memory size mobilized

FORUM

Calling Scalar Function within stored Procedure

Calling Scalar Function within stored Procedure

FORUM

Implementing a function within a stored procedure

Implementing a function within a stored procedure

FORUM

Stored procedure in User defined function?

Can I call Stored procedure in User defined function?

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones