Utilizzare i parametri di output di una stored procedure in certe situazioni può essere molto performante rispetto all'uso del recordset nel recuperare dati da SQL Server tramite ADO. Questa tecnica può esserci di aiuto quando dobbiamo recuperare una singola riga da una tabella, come nel caso delle pagine web di dettaglio che riportano informazioni su uno specifico prodotto. Qui l'istruzione SQL Â da inviare al server è simile a questa:
SELECT * FROM Prodotti WHERE ProdottoID = @ProdottoID
La SELECT precedente recupera sempre una singola riga della tabella Prodotti visto che la condizione nella clausola WHERE fa riferimento alla chiave primaria della tabella stessa.
Un esempio pratico
Per quantificare l'efficienza di un metodo rispetto all'altro ho preparato due stored procedures e due scripts ASP in cui provare gli approcci. Gli esempi si basano sul database Northwind di SQL Server 2000. Entrambe le stored procedure ritornano un singolo record formato da quattro campi presenti su tre tabelle differenti: Products, Categories e Suppliers.
La prima procedura InfoProdotto_1 Â usa l'istruzione SELECT per recuperare il record e quindi usiamo l'oggetto Recordset di ADO per l'output dei risultati sulla pagina ASP.
--Prima procedura: recupero i dati tramite una SELECT
CREATE PROCEDURE InfoProdotto_1
@ProdottoID 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=@ProdottoID
GO
La seconda invece, InfoProdotto_2 , usa i parametri di OUTPUT della procedura per recuperare i campi del record e quindi usiamo l'oggetto Command di ADO per l'output dei risultati sulla pagina ASP.
--Seconda procedura: recupero i valori dai parametri di output della sp
CREATE PROCEDURE InfoProdotto_2
@ProdottoID int,
@NomeProdotto nvarchar(40) OUTPUT,
@NomeAzienda nvarchar(40) OUTPUT,
@NomeCategoria nvarchar(15) OUTPUT,
@PrezzoUnitario money OUTPUT
AS
SELECT
@NomeProdotto=p.ProductName,
@NomeCategoria=c.CategoryName,
@NomeAzienda=s.CompanyName,
@PrezzoUnitario=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=@ProdottoID
GO
Per testare le performance ho creato due scripts ASP che utilizzano ADO per fare chiamate ripetute alle stored procedures appena create.
Come si può vedere dal codice sottostante, ogni script chiama in modo continuato per 15 secondi la procedura opportuna incrementando ad ogni chiamata un contatore che alla fine costituirà il parametro di paragone tra i due metodi.
Il primo script ASP dal nome Prod1.asp chiama in modo continuo per 15 secondi la procedura InfoProdotto_1 istanziando ogni volta l'oggetto Recordset per recuperare i valori:
lCount=0
do while DateDiff("s",StartTime,time)<15
lCount=lCount+1
 Set rs = cmd.ExecuteÂ
loop
Il secondo script ASP dal nome Prod2.asp invece chiama in continuamente per 15 secondi la procedura InfoProdotto_2 eseguendo ogni volta il metodo Execute dell'oggetto Command per recuperare i valori contenuti nei parametri:
lCount=0
do while DateDiff("s",StartTime,time)<15
lCount=lCount+1
cmd.ExecuteÂ
loop
Per entrambi gli script l'output dei valori viene stampato una sola volta alla fine del ciclo di chiamate per non "sporcare" lo stress test. Nel primo script i valori sono contenuti nell' oggetto Recordset mentre nel secondo script all'interno della collection Parameters dell'oggetto Command.
Il codice di Prod1.asp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>TEST con RS</title>
</head>
<body>
<%
Dim ProdottoID
Dim NomeProdotto
Dim NomeAzienda
Dim NomeCategoria
Dim PrezzoUnitario
Dim cmd
Dim rs
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Password=;User ID=sa;Initial
Catalog=Northwind;Data Source=TUOSERVERDB"
cmd.CommandType = 4 'Stored Procedure
cmd.CommandText = "InfoProdotto_1"
cmd.Parameters.Append cmd.CreateParameter("@RETURN_VALUE", 3, 4, 0,
Null)
cmd.Parameters.Append cmd.CreateParameter("@ProdottoID", 3, 1, 0,
10)
dim StartTime
starttime=Time
dim lCount
lCount=0
do while DateDiff("s",StartTime,time)<15
lCount=lCount+1
Set rs = cmd.Execute
loop
response.write "Eseguita <b>" & lCount & "</b> volte in 15 secondi - <a href=""Prod2.asp"">Compara
con il secondo test</a>!"
NomeProdotto = rs("ProductName").Value
NomeAzienda = rs("CategoryName").Value
NomeCategoria = rs("CompanyName").Value
PrezzoUnitario = rs("UnitPrice").Value
Set rs = Nothing
Set cmd = Nothing
%>
<hr size="1" noshade>
<table border="0" cellspacing="2" cellpadding="2"
align="center">
<tr>
<th colspan="2" align="center">Informazioni sul
prodotto con ID n° 10</th>
</tr>
<tr>
<td>Nome prodotto:</td>
<td><%= NomeProdotto %></td>
</tr>
<tr>
<td>Nome azienda:</td>
<td><%= NomeAzienda %></td>
</tr>
<tr>
<td>Nome categoria:</td>
<td><%= NomeCategoria %></td>
</tr>
<tr>
<td>Prezzo unitario:</td>
<td><%= PrezzoUnitario %></td>
</tr>
</table>
</body>
</html>
Il codice di Prod2.asp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>TEST senza RS</title>
</head>
<body>
<%
Dim ProdottoID
Dim NomeProdotto
Dim NomeAzienda
Dim NomeCategoria
Dim PrezzoUnitario
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Password=;User ID=sa;Initial
Catalog=Northwind;Data Source=TUOSERVERDB"
cmd.CommandType = 4 Â Â Â Â Â Â 'Stored Procedure
cmd.CommandText = "InfoProdotto_2"
cmd.Parameters.Append cmd.CreateParameter("@RETURN_VALUE", 3, 4,
0, RETURN_VALUE)
cmd.Parameters.Append cmd.CreateParameter("@ProdottoID", 3, 1, 0,
10)
cmd.Parameters.Append cmd.CreateParameter("@NomeProdotto", 202,
3, 40, null)
cmd.Parameters.Append cmd.CreateParameter("@NomeAzienda", 202, 3,
40, null)
cmd.Parameters.Append cmd.CreateParameter("@NomeCategoria", 202,
3, 15, null)
cmd.Parameters.Append cmd.CreateParameter("@PrezzoUnitario", 6,
3, 0, null)
dim StartTime
starttime=Time
dim lCount
lCount=0
do while DateDiff("s",StartTime,time)<15
lCount=lCount+1
cmd.ExecuteÂ
loop
response.write "Eseguita <b>" & lCount & "</b>
volte in 15 secondi - <a href=""Prod1.asp"">Compara
con il secondo test</a>!"
NomeProdotto = cmd.Parameters("@NomeProdotto").Value
NomeAzienda = cmd.Parameters("@NomeAzienda").Value
NomeCategoria = cmd.Parameters("@NomeCategoria").Value
PrezzoUnitario = cmd.Parameters("@PrezzoUnitario").Value
Set cmd = Nothing
%>
<hr size="1" noshade>
<table border="0" cellspacing="2" cellpadding="2"
align="center">
<tr>
<th colspan="2" align="center">Informazioni
sul prodotto con ID n° 10</th>
</tr>
<tr>
<td>Nome prodotto:</td>
<td><%= NomeProdotto %></td>
</tr>
<tr>
<td>Nome azienda:</td>
<td><%= NomeAzienda %></td>
</tr>
<tr>
<td>Nome categoria:</td>
<td><%= NomeCategoria %></td>
</tr>
<tr>
<td>Prezzo unitario:</td>
<td><%= PrezzoUnitario %></td>
</tr>
</table>
</body>
</html>
Uno sguardo ai risultati del test
Le prove che ho effettuato sul mio PC portatile (Windows 2000 Advanced Server, SQL Server 2000 Enterprise Edition, 256 MB RAM, PIII 933) non possono essere di riferimento rispetto ad un ambiente di produzione ma sono sicuramente utili per discutere i risultati ottenuti. Lo script Prod1.asp ha recuperato le informazioni sul prodotto 2543 volte contro le 15345 volte dello script Prod2.asp nello stesso arco di tempo di 15 secondi. Strabiliante, il secondo script ha mostrato un incremento superiore al 600%!
Il motivo di questo gap di prestazioni non è attribuibile a SQL Server perché la velocità di esecuzione e le prestazioni delle due stored procedures sono praticamente identiche ma tuttalpiù ad ADO perchè il recupero delle informazioni attraverso il Recordset è molto più lento e meno performante rispetto alla stessa cosa fatta con l'interrogazione della collection Parameters dell'oggetto Command.
Quindi nel caso ci dovessimo trovare davanti ad una pagina web che ha molti accessi concorrenti potrebbe essere utile, se possibile, usare la collection di parametri di Command per recuperare i valori dal database anziché il classico Recordset e l'applicazione sarebbe certamente più veloce!
