SQL Server 性能结果集 vs 输出参数 vs 返回值 [英] SQL Server Performance ResultSet vs Output Parameter vs Return Value

问题描述

我正在权衡使用三种不同方法之一将单个标量值从存储过程返回到我的 C# 例程的潜在性能影响.谁能告诉我其中哪个更快"，最重要的是，为什么?

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?

方法一:

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

方法二:

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

方法三:

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

推荐答案

返回一个标量值比返回一个结果集更高效，原因是结果集带有更多的辅助方法，这使得它很重从而增加了延迟在对象从 sql 到 C# 代码/例程的传输中.

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.

在您的方法 3 中:您使用了一个变量来返回该值，这比发送一个 out 参数更好，因为在这里您至少在一个路由中减少了一个对象的遍历(即，在调用存储过程时).

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.

按照方法 3、方法 2、方法 1 的性能对查询进行排序.

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

希望这有助于理解这个概念.

Hope this is helpful in understanding the concept.

这篇关于SQL Server 性能结果集 vs 输出参数 vs 返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！