Make ADO Work Faster Using Output Parameters in Your Stored Procedures
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. .gif)
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.