SQL Server性能ResultSet 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?
方法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
方法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
方法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
推荐答案
返回标量值比结果集更有效,原因是结果集包含更多的辅助方法,这使它繁重,从而增加了延迟将对象从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性能ResultSet vs输出参数vs返回值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!