stored procedure benchmarking?

[hithere]

anyone know a way to see how a stored procedure is more efficient than the sql string that was running? "Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!"
Marvin the Martian

 

[jemminger]

sure:

create a table with ten columns VARCHAR(255)
populate 250,000 rows of data

run these:

inlineSQL.asp:[tt]
<%
set cn = Server.CreateObject(&quot;adodb.connection&quot
cn.open &quot;connection string to my db&quot;

for x = 1 to 100
set rs = cn.execute(&quot;select * from myHugeTable&quot
response.write(&quot;inline loop &quot; & x & &quot; - &quot; & now() & &quot;<br/>&quot
next

cn.close()
set cn = nothing
%>[/tt]

storedProc.asp:[tt]
<%
set cn = Server.CreateObject(&quot;adodb.connection&quot
cn.open &quot;connection string to my db&quot;

for x = 1 to 100
set rs = cn.execute(&quot;exec getAllFromHugeTable_sp&quot
response.write(&quot;SP loop &quot; & x & &quot; - &quot; & now() & &quot;<br/>&quot
next

cn.close()
set cn = nothing
%>[/tt] =========================================================
while (!succeed) try();
-jeff