Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Performance ResultSet vs Output Parameter vs Return Value

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
like image 823
Randy Braze Avatar asked Jul 24 '13 17:07

Randy Braze


People also ask

Why We Use output parameter in stored procedure?

The Output Parameters in Stored Procedures are used to return some value or values. A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.

What is return value SQL Server?

Return Value in SQL Server Stored Procedure In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.

Can stored procedure return resultset?

In addition to returning output parameters, a stored procedure can return a result set (that is, a result table associated with a cursor opened in the stored procedure) to the application that issues the CALL statement.

How do I execute a stored procedure without parameters in SQL Server?

The simplest kind of SQL Server stored procedure that you can call is one that contains no parameters and returns a single result set. The Microsoft JDBC Driver for SQL Server provides the SQLServerStatement class, which you can use to call this kind of stored procedure and process the data that it returns.


2 Answers

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.

like image 97
Vinay Kumar.o Avatar answered Oct 24 '22 23:10

Vinay Kumar.o


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).

like image 44
DCW Avatar answered Oct 24 '22 22:10

DCW