Performance Comparison Retrieving Data From SQL Server

by Granville Bonyata on August 19, 2012

C# developers generally find retrieving data in a result set from a SQL Server 2008 database procedure is easier than retrieving data from output parameters of a SQL Server 2008 database procedure. Is there any performance cost in this choice?

Turns out the answer looks to be yes, although probably only worth pursuing when you’re trying to wring every bit of performance from your application.

The application opens the database connection for a retrieval method. It calls the associated procedure 1000 times in a loop, setting local variables after each call with the retrieved data. It closes the connection and repeats the process for the other retrieval method. The computed time excludes opening and closing the database connections.

The code:

create procedure prReturnDataSet
as
begin
select
'N' as HasErrorOccurred,
'N' as HasLockTimeoutOccurred,
'N' as WasRowUpdated;
end;
go

create procedure prContainOutputParameters
(
@HasErrorOccurred varchar(1) output,
@HasLockTimeoutOccurred varchar(1) output,
@WasRowUpdated varchar(1) output
)
as
begin
select
@HasErrorOccurred = 'N',
@HasLockTimeoutOccurred = 'N',
@WasRowUpdated = 'N';
end;
go

 

I ran the application 10 times using SQL Server 2008. The results are below.

72.0041ms for Result Set Retrieval.
58.0034ms for Output Parameter Retrieval.

72.0042ms for Result Set Retrieval.
55.0032ms for Output Parameter Retrieval.

69.0039ms for Result Set Retrieval.
54.0031ms for Output Parameter Retrieval.

81.0046ms for Result Set Retrieval.
100.0057ms for Output Parameter Retrieval.

70.004ms for Result Set Retrieval.
54.0031ms for Output Parameter Retrieval.

71.0041ms for Result Set Retrieval.
59.0034ms for Output Parameter Retrieval.

72.0041ms for Result Set Retrieval.
57.0033ms for Output Parameter Retrieval.

69.0039ms for Result Set Retrieval.
52.003ms for Output Parameter Retrieval.

72.0041ms for Result Set Retrieval.
55.0031ms for Output Parameter Retrieval.

72.0041ms for Result Set Retrieval.
61.0035ms for Output Parameter Retrieval.

For occasional outliers, output parameter retrieval takes longer than result set retrieval, but on average output parameter retrieval takes about 20% less time than result set retrieval. So – in those cases where you’re pushing the performance edge and need to buys some gains, output parameters look like an option you should benchmark.

Link to the C# code.

Previous post:

Next post: