Make ADO Work Faster Using Output Parameters in Your Stored Procedures

SQL Server 2000

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Craig Utley

Many of us have read or heard that using SQL Server output parameters is more efficient than simply returning a recordset. But, how much more efficient? And was the gain in ADO, SQL Server, or both? Although Craig Utley is based in Kentucky, you might think he's from Missouri, the "show me" state.

In the course of writing my book, SQL Server 2000 Web Applications Developer's Guide (ISBN 0072126191), I decided to find out whether using output parameters from a stored procedure was really more efficient than outputting to a recordset and, if so, by how much. To that end, I created two very simple stored procedures in the Northwind database and then put together some simple ASP files to test them.

Understanding the examples

It's important to point out that what I do in these stored procedures doesn't work in every case, but it's more common than you might expect. The idea is that you'll only be returning one record. Imagine going to a Web site and searching for books with the words "SQL Server 2000" in the title. Several dozen titles are returned, and, at that point, you see one that interests you, so you click on it… Guess what? You've just returned one record. That record might join multiple tables, but, at the end of the day, it really just returns one record.

There are two stored procedures. The first stored procedure, ProductInfo1, returns a single record using a standard SELECT statement, and thus returns a recordset. The second stored procedure, ProductInfo2, returns the fields of the record using output parameters and never has to create a recordset.

Both stored procedures return four fields from three different tables. The ProductName and UnitPrice fields come from the Products table, the CompanyName comes from the Suppliers table, and the CategoryName comes from the Categories table.

CREATE PROCEDURE ProductInfo1
@ProductID int 
AS
SELECT p.ProductName, 
       c.CategoryName, s.CompanyName, 
       p.UnitPrice 
FROM Products p INNER JOIN Suppliers s ON 
        p.SupplierID = s.SupplierID
     INNER JOIN Categories c ON 
        p.CategoryID = c.CategoryID
WHERE p.ProductID=@ProductID 

CREATE PROCEDURE ProductInfo2
@ProductID int,
@ProductName nvarchar(40) OUTPUT,
@CompanyName nvarchar(40) OUTPUT,
@CategoryName nvarchar(15) OUTPUT,
@UnitPrice money OUTPUT
AS
SELECT @ProductName=p.ProductName, 
       @CategoryName=c.CategoryName, 
       @CompanyName=s.CompanyName, 
       @UnitPrice=p.UnitPrice 
FROM Products p INNER JOIN Suppliers s ON 
        p.SupplierID = s.SupplierID
     INNER JOIN Categories c ON 
        p.CategoryID = c.CategoryID
WHERE p.ProductID=@ProductID

The client scripts

To test the speed, I created two Active Server Pages (ASPs). These ASPs used ADO to call the stored procedures. They made repetitive calls to the stored procedures by checking the time, and then looping for 15 seconds, calling the stored procedure as many times as possible, and keeping count. At the end, the number of iterations through the loop is printed out, as well as the four fields. The record is only printed once, after the loop has been exited, so as not to affect the timing of the loop. Note: If you decide to try these, search for the file "adovbs.inc" and make sure it's in the same virtual directory as your ASP files. Also, make sure you change the data source in the ADO connection string to your server name.

Not surprisingly, the first ASP is named ProductInfo1.asp. It calls the stored procedure ProductInfo1 that returns the record via a recordset. The code is as follows:

<%@ Language=VBScript %>
<% option explicit %>
<HTML>
<HEAD>
</HEAD>
<BODY>
<!-- #include file="adovbs.inc" -->
<%
dim cn
dim cmd
dim rs
dim pProductID

set cn=server.CreateObject("ADODB.connection")
set cmd=server.CreateObject("ADODB.command")
set pProductID= _
   cmd.CreateParameter("ProductID",adInteger,adParamInput)
cn.Open "Provider=SQLOLEDB.1;Password=;User ID=sa;" & _
   "Initial Catalog=Northwind;Data Source=laptop"
set cmd.ActiveConnection=cn
cmd.CommandText="ProductInfo1"
cmd.Parameters.Append pProductID
pProductID.Value=1
dim StartTime
starttime=Time
dim lCount
lCount=0
do while DateDiff("s",StartTime,time)<15
   lCount=lCount+1
   set rs=cmd.Execute (,,adCmdStoredProc)
loop
response.write lCount
%>

<TABLE Border="1" Cellspacing="2" Cellpadding="2">
   <TR>
      <TH>Product Name</TH>
      <TH>Category Name</TH>
      <TH>Company Name</TH>
      <TH>Total</TH>
   </TR>
      <%
      do until rs.EOF
         Response.Write("<TR>")
         Response.Write("<TD>" & rs("ProductName") & "</TD>")
         Response.Write("<TD>" & rs("CategoryName") & "</TD>")
         Response.Write("<TD>" & rs("CompanyName") & "</TD>")
         Response.Write("<TD>" & rs("UnitPrice") & "</TD>")
         Response.Write("</TR>")
         rs.MoveNext
      loop
      %>
</TABLE>
</BODY>
</HTML>

The second ASP, ProductInfo2.asp, calls ProductInfo2, which uses output parameters to return the data. As you can see from the code, there's more effort required to deal with the output parameters. However, as you'll see in a moment, the speed increase is significant.

<%@ Language=VBScript %>
<% option explicit %>
<HTML>
<HEAD>
</HEAD>
<BODY>
<!-- #include file="adovbs.inc" -->
<%
dim cn
dim cmd
dim rs
dim pProductID
dim pProductName, pCompanyName, pCategoryName
dim pUnitPrice

set cn=server.CreateObject("ADODB.connection")
set cmd=server.CreateObject("ADODB.command")
set pProductID= _
   cmd.CreateParameter("ProductID",adInteger, _
   adParamInput)
set pProductName= _
   cmd.CreateParameter("ProductName",adVarChar, _
   adParamOutput,40)
set pCompanyName= _
   cmd.CreateParameter("CompanyName",adVarChar, _
   adParamOutput,40)
set pCategoryName= _
   cmd.CreateParameter("CategoryName",adVarChar, _
   adParamOutput,15)
set pUnitPrice= _
   cmd.CreateParameter("UnitPrice",adCurrency, _
   adParamOutput)
cn.Open "Provider=SQLOLEDB.1;Password=;User ID=sa;" & _
   "Initial Catalog=Northwind;Data Source=laptop"
set cmd.ActiveConnection=cn
cmd.CommandText="ProductInfo2"
cmd.Parameters.Append pProductID
cmd.Parameters.Append pProductName
cmd.Parameters.Append pCompanyName
cmd.Parameters.Append pCategoryName
cmd.Parameters.Append pUnitPrice
pProductID.Value=1
dim StartTime
starttime=Time
dim lCount
lCount=0
do while DateDiff("s",StartTime,time)<15
      lCount=lCount+1
      cmd.Execute ,,adCmdStoredProc + adExecuteNoRecords
loop
response.write lCount
%>

<TABLE Border="1" Cellspacing="2" Cellpadding="2">
   <TR>
      <TH>Product Name</TH>
      <TH>Category Name</TH>
      <TH>Company Name</TH>
      <TH>Total</TH>
   </TR>
   <%
   Response.Write("<TR>")
   Response.Write("<TD>" & _
     cmd.Parameters("ProductName").Value  & "</TD>")
   Response.Write("<TD>" & _
     cmd.Parameters("CategoryName").Value & "</TD>")
   Response.Write("<TD>" & _
     cmd.Parameters("CompanyName").Value & "</TD>")
   Response.Write("<TD>" & _
     cmd.Parameters("UnitPrice").Value & "</TD>")
   Response.Write("</TR>")
   %>
</TABLE>
</BODY>
</HTML>

Testing the speed

This isn't the most scientific way to measure speed, but if you run several iterations, you'll notice that the results are generally within a range of 5-10%, so I feel the tests are a good indicator of relative performance. In my test on my laptop (running Windows 2000 Advanced Server, SQL Server 2000 Enterprise, with a PIII 600 MHz and 256 MB RAM), ProductInfo1.asp averaged 465 iterations through the loop, while ProductInfo2.asp averaged a whopping 17,113 iterations, for an increase of more than 3,500 percent! Obviously, your mileage will vary based upon many factors, but, again, the point is that the relative performance is vastly improved.

Nirvana?

So, should you rush out and recode every stored procedure you have to use output parameters? Well, no. Remember, these sample stored procedures are only returning one record each. More importantly, though, it's crucial to understand just where the increase is being seen.

Based on further experimentation, the impact of these two stored procedures on SQL Server itself seems almost identical. I've struggled to get meaningful statistics on their impact, but if I "trick" ADO into thinking that ProductInfo1 doesn't return a recordset, the performance of ProductInfo1.asp is within 1-2% of ProductInfo2.asp (in fact, it's 1-2% faster). Therefore, it appears that the increase you're seeing here is from ADO—not SQL Server.

Having said that, this is still a great technique for speeding up access to your data. If you figure that many Web applications have pages that just pull one record at a time, and you can speed up that access significantly, this might be a valid technique to use. And since many of you don't just do straight SQL Server, but integrate it with Web or Windows applications, this is a useful trick to have up your sleeve.

Download UTLEY.TXT

To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the February 2001 issue of Microsoft SQL Server Professional. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.