SQL Server Performance ResultSet vs Output Parameter vs Return Value

Question

I'm weighing the potential performance impact of using one of three different methods of returning a single, scalar value from a stored procedure to my C# routine. Can anyone tell me which of these is "faster" and, most importantly, why?

Method 1:

CREATE PROCEDURE GetClientId 
    @DealerCode varchar(10)
AS
BEGIN
    SET NOCOUNT ON
    SELECT ClientId
        FROM Client
        WHERE ClientCode = @DealerCode
END
-- this returns null if nothing is found, 
-- otherwise it returns ClientId in a ResultSet

Method 2:

CREATE PROCEDURE GetClientId 
    @DealerCode varchar(10),
    @ClientValue int out
AS
BEGIN
    SET NOCOUNT ON
    set @ClientValue = -1
    set @ClientValue = (SELECT ClientId
        FROM Client
        WHERE ClientCode = @DealerCode)
END
-- this returns -1 for ClientValue if nothing is found,
-- otherwise it returns ClientId
-- the value for ClientValue is a scalar value and not a ResultSet

Method 3:

CREATE PROCEDURE GetClientId 
    @DealerCode varchar(10)
AS
BEGIN
    SET NOCOUNT ON
    declare @ClientValue int
    set @ClientValue = 
        (SELECT ClientId FROM Client WHERE ClientCode = @DealerCode)
    if @ClientValue is null or @ClientValue = 0
        return -1
    else
        return @ClientValue
END
-- this uses the return value of the stored procedure;
-- -1 indicates nothing found
-- any positive, non-zero value is the actual ClientId that was located

BenMorel · Accepted Answer · 2013-11-06 12:09:03Z

9

Returning a scalar value is more efficient than a result set, the reason is result set carries lot more helper methods along with it, which makes it heavy thus increasing latency in transmission of the object from sql to C# code/routine.

In your method 3: You have used a variable to return the value this is more better than sending an out parameter since here you are cutting down on traverse of an object atleast in one route ( i.e., when invoking the stored procedure).

A result set is more flexible than an output parameter because it can return multiple rows (obviously), so if you need a result set then it's the only choice anyway.

To order the queries based on performance that goes as Method 3, Method 2 Method 1.

Hope this is helpful in understanding the concept.

edited Nov 6, 2013 at 12:09

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Sep 5, 2013 at 14:01

Vinay Kumar.o

3362 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Roy Over a year ago

Another precision. With Method 3 (RETURN), you are limited to INTEGER type. If you want to return another data type, you should use Method 2 or Method 1.

Randy Braze Over a year ago

I appreciate all the help. Personally, I was leaning toward #2. However, as I'm returning an INTEGER in both #2 and #3, I could see where #3 would have the best performance. I would use #2 if I had to return some type of string. Of course, as previously stipulated, I would use #1 only if I need a result set.

Dale K Over a year ago

Its worth pointing out that the intention of the return value is to return the status of the procedure - not to return data. But so long as you know that use it as you will.

DCW · Accepted Answer · 2021-05-19 13:08:33Z

1

In terms of performance penalty, method 3 (RETURN) is penalty-free. The reason being that SQL Server will always return an integer result code from a Stored Procedure. If you do not explicitly specify one, then it it will implicitly return 0 (SUCCESS).

answered May 19, 2021 at 13:08

DCW

111 bronze badge

Comments

DeepBlue · Accepted Answer · 2021-10-14 09:16:20Z

0

CREATE PROCEDURE GetClientId 
   @DealerCode varchar(10)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @ClientValue INT=0;
    SELECT @ClientValue = ClientId FROM Client WHERE ClientCode = 
@DealerCode
    IF @ClientValue IS NULL OR @ClientValue = 0
        RETURN -1;
    ELSE
        RETURN @ClientValue;
END
-- this uses the return value of the stored procedure;
-- -1 indicates nothing found
-- any positive, non-zero value is the actual ClientId that was located

answered Oct 14, 2021 at 9:16

DeepBlue

1

Collectives™ on Stack Overflow

SQL Server Performance ResultSet vs Output Parameter vs Return Value

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related