Performance question - Output parameters vs single row?

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
Thu Jul 08 02:06:13 CDT 2004

Hi Joel,

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

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Joel Reinford" <jrreinford@earthlink.net> wrote in message
news:gNidnYJZNu0dJnHdRVn-hA@comcast.com...
> 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
>
>
>