Performance question - Output parameters vs single row?

[Joel Reinford]

I'm aiming this question at Bill Vaughn but anyone is welcome to jump in. I
have seen several articles claiming that it is faster to return a set of
output parameter values rather than a single row. You make a reference to
this for ADO on Page 269 of ADO.NET and ADO Best Practices. I don't see any
numbers to back this up and attempted to run a simple test of my own.

My test just ran a loop to populate the fields on a form based on a single
record from the Northwind Orders table. I ran the loop 100,000 times using
two different stored procedures. The first one used output parameters, the
second returned a single row into a datareader and I looped through it to
get the column values. I ran the test a half dozen times for each, just to
see if I was experiencing any variation between tests.

The single row test completed in approx 22 seconds and the output parameters
test in approx 27 seconds each time. This was not what I was expecting so
I'm looking for any comments on these two approaches.

Joel Reinford
Data Management Solutions LLC


CREATE PROCEDURE dbo.dp_OrderSingle
(
@OrderID int = NULL
, @CustomerID nchar(5) = NULL OUTPUT
, @EmployeeID int = NULL OUTPUT
, @OrderDate datetime = NULL OUTPUT
, @RequiredDate datetime = NULL OUTPUT
, @ShippedDate datetime = NULL OUTPUT
, @ShipVia int = NULL OUTPUT
, @Freight money = NULL OUTPUT
, @ShipName nvarchar(40) = NULL OUTPUT
, @ShipAddress nvarchar(60) = NULL OUTPUT
, @ShipCity nvarchar(15) = NULL OUTPUT
, @ShipRegion nvarchar(15) = NULL OUTPUT
, @ShipPostalCode nvarchar(10) = NULL OUTPUT
, @ShipCountry nvarchar(15) = NULL OUTPUT
)
AS

SET NOCOUNT ON


SELECT
@OrderID = o.OrderID
, @CustomerID = o.CustomerID
, @EmployeeID = o.EmployeeID
, @OrderDate = o.OrderDate
, @RequiredDate = o.RequiredDate
, @ShippedDate = o.ShippedDate
, @ShipVia = o.ShipVia
, @Freight = o.Freight
, @ShipName = o.ShipName
, @ShipAddress = o.ShipAddress
, @ShipCity = o.ShipCity
, @ShipRegion = o.ShipRegion
, @ShipPostalCode = o.ShipPostalCode
, @ShipCountry = o.ShipCountry

FROM
Orders o

WHERE
o.OrderID = @OrderID



CREATE PROCEDURE dbo.dp_OrderSingleRow
(
@OrderID int
)
AS

SET NOCOUNT ON

SELECT
o.OrderID
, o.CustomerID
, o.EmployeeID
, o.OrderDate
, o.RequiredDate
, o.ShippedDate
, o.ShipVia
, o.Freight
, o.ShipName
, o.ShipAddress
, o.ShipCity
, o.ShipRegion
, o.ShipPostalCode
, o.ShipCountry

FROM
Orders o

WHERE
o.OrderID = @OrderID

 

[Miha Markic [MVP C#]]

Hi Joel,

I think that other people have seen this behaviour, too.
So, it is quite possible that single row is faster.