Dec 26, 2014

Stored procedure vs. Function performance SQL server

Some developers have a little confusion about when to create SQL function and when to create stored procedures? Some of the developers always create the stored procedures. Here we are going to discuss about performance impact between stored procedure and SQL function. 




To understand this better way I would like to first explain the difference between them. In simple word we can say stored procedure is a pre-complied database object which keeps batching of SQL statements and can return more than one result set. So inside a stored procedure we can write all types of DML, DDL, SELECT statements, Dynamic queries, implement error handling, invoking other stored procedure and SQL function and more thing. In the other hand SQL functions (SQL Server supports two types of the SQL function. They are SQL a scalar value function and tabled value function) are also pre-compiled objects which always return a single value or object. In case of scalar valued function, inside SQL function we cannot write DML, DDL, SELECT statements (allowed in a very limited way), dynamic queries, cannot implement error handling, cannot invoke other stored procedures etc. And in case of tabled value function very limited DDL and DML are allowed.

You may be thinking SQL functions have a lot of restrictions on SQL statement then why I am going to write SQL function!! Only benefit over a SQL function over a stored procedure is SQL function can be part of the SELECT statement and DML statements and which can improve overall query performance drastically.  Let’s discuss in detail when stored procedure will perform better and when a SQL function by examples.

a. Create scalar SQL function when there is need to get a single value after some calculation and that calculation is need to be done multiple times with different parameters. For examples: There is need to count the total comments sent by a user. 

Using stored procedure:

CREATE PROC uspUserCommentCount(
  @ntUserID AS BIGINT
)
AS
BEGIN

SELECT COUNT(*) FROM tblComment WHERE ntUserID = @ntUserID

  END

Using SQL function:

CREATE FUNCTION udfUserCommentCount(
     @ntUserID AS BIGINT
)
RETURNS INT
AS
BEGIN

RETURN (SELECT COUNT(*) FROM tblComment WHERE ntUserID = @ntUserID)

END

If we execute only times then we will not get a significant performance difference:

EXECUTE dbo.uspUserCommentCount 10

Total logical read: 4

SELECT dbo.udfUserCommentCount(10)
Total logical read: 4

If there is business need to get total comments of all users according to users by using either SQL Function or Stored procedure. 

Using SQL function:

SELECT
ntUserID, dbo.udfUserCommentCount(ntUserID)
FROM tblUser
Execution Time: 34 Seconds

Using Procedure:

It is not possible to call stored procedure in the SELECT statement. For example:

SELECT ntUserID, dbo.uspUserCommentCount(ntUserID) FROM tblUser

It will throw error message: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.uspUserCommentCount", or the name is ambiguous.

To accomplish this task we must have to use either WHILE LOOP or CURSOR. Which will lead a row based approach. I think there is no need to explain that a row based approach can be how much costlier. 

DECLARE @ntUserID AS BIGINT
DECLARE @tblUserCommentCount TABLE(ntUserID BIGINT,ntCommentCount BIGINT)

DECLARE curUser CURSOR LOCAL FAST_FORWARD FOR SELECT ntUserID FROM tblUser
OPEN curUser
FETCH NEXT FROM curUser INTO @ntUserID

WHILE @@FETCH_STATUS = 0 BEGIN

     CREATE TABLE #tblCommentCount(ntCommentCount BIGINT)
    
     INSERT INTO #tblCommentCount
     EXECUTE dbo.uspUserCommentCount @ntUserID
    
     INSERT INTO @tblUserCommentCount
     SELECT @ntUserID,ntCommentCount FROM #tblCommentCount

     DROP TABLE #tblCommentCount
    
     FETCH NEXT FROM curUser INTO @ntUserID
    
END

CLOSE curUser
DEALLOCATE curUser

SELECT * FROM @tblUserCommentCount

Execution Time: 6 minutes 56 seconds

You might be also thinking that business need, can be full fill by very simple script. For example:

SELECT ntUserID,COUNT(*)
FROM tblComment
GROUP BY ntUserID

Then what is need to use SQL function or stored procedure. Yes there is no need to use those objects. In this example there was very simple business requirement. In case of complex business requirement some developers create stored procedure instead of SQL function and call the stored procedure in a LOOP either from application code or by using Transact-SQL. It may be due to either they not aware of query cost or they think it is very difficult to convert existing complex procedure to equivalent SQL function etc.



 b. Create table valued SQL function when there is need to get a single result set  then output of result set is need to be used multiple times to perform some kind of join with other tables. For example: There is business need to get last three recent comments data of a user.  

Using stored procedure:

CREATE PROCEDURE uspGetLastThreeComment(
     @ntUserID BIGINT
)
AS
BEGIN
SELECT TOP(3) * FROM tblComment WHERE ntUserID = @ntUserID
ORDER BY dtCommentDate DESC
END

Using Table valued function:

CREATE FUNCTION udfGetLastThreeComment(
     @ntUserID BIGINT
)
RETURNS TABLE
AS
RETURN

     SELECT TOP(3) *
     FROM tblComment
     WHERE ntUserID = @ntUserID
  ORDER BY dtCommentDate DESC
If we execute only times then we will not get a significant performance difference:

EXECUTE dbo.uspGetLastThreeComment 10

Total logical read: 4

SELECT * FROM dbo.udfGetLastThreeComment(10)

Total logical read: 4

If there is business need last three comments of all users with user detail information by using existing procedure of tabled value function.

Using tabled value function:
SELECT *
FROM tblUser U CROSS APPLY
dbo.udfGetLastThreeComment(U.ntUserID)

Execution time: 39 Seconds

Using Procedure:

It is not possible to call stored procedure in JOIN statement. For example:

SELECT *
FROM tblUser U CROSS APPLY
dbo.uspGetLastThreeComment(U.ntUserID)

It will throw error message: Invalid object name 'dbo.uspGetLastThreeComment'.
To accomplish this task we must have to use either WHILE LOOP or CURSOR. Which will lead a row based approach and which a very costly operation.


No comments:

Post a Comment