best practices for subforms/subquerys

My ASP pages uses several SQL stored procedures to show data. The first
procedure finds all items "A" for a specific period, all other
procedures query all other kind of stuff, using values "A" form the
first procedure as a parameter. It looks something like this:

<%
sql1 = "exec proc1"
sql2 = "exec proc2 '" & rs1.Fields.Item("A").Value & "'"
sql3 = "exec proc3 '" & rs1.Fields.Item("A").Value & "'"

set conn = CreateObject("ADODB.Connection")
conn.open
set rs1 = conn.execute(sql1)
%>



<%
do while not rs1.EOF

response.write rs1.Fields.Item("A").Value

set rs2.conn.execute(sql2)

do while not rs2.EOF

response.write rs2.Fields.Item("B1").Value
response.write rs2.Fields.Item("B2").Value

rs2.MoveNext()
Loop


set rs3.conn.execute(sql3)

do while not rs3.EOF

response.write rs3.Fields.Item("C1").Value
response.write rs3.Fields.Item("C2").Value

rs3.MoveNext()
Loop

rs1.MoveNext()
Loop
conn.Close
%>

I would like to know the best practice (IIS performance / SQL
performance) to use this kind of setup. I'm especially interested in
where to put the conn.open and conn.close statements. Any ideas /
enhancements /improvements are welcome.

TIA,
Wouter

Re: best practices for subforms/subquerys

wouter@iwork.nl wrote:
> My ASP pages uses several SQL stored procedures to show data. The
> first procedure finds all items "A" for a specific period, all other
> procedures query all other kind of stuff, using values "A" form the
> first procedure as a parameter. It looks something like this:
>
> <%
> sql1 = "exec proc1"
> sql2 = "exec proc2 '" & rs1.Fields.Item("A").Value & "'"
> sql3 = "exec proc3 '" & rs1.Fields.Item("A").Value & "'"

See this post for a better way to execute your procedures:
http://tinyurl.com/jyy0


>
> set conn = CreateObject("ADODB.Connection")
> conn.open
> set rs1 = conn.execute(sql1)
> %>

>
> I would like to know the best practice (IIS performance / SQL
> performance) to use this kind of setup. I'm especially interested in
> where to put the conn.open and conn.close statements. Any ideas /
> enhancements /improvements are welcome.
>

My preference is to use a single call to the database whenever possible. So
my first task would be to examine these three procedures and see if they
could not be combined into a single procedure, even if it means returning
multiple recordsets. From what I can see from the above, it would be a
simple matter to create a single procedure that accepts the parameter value
you are passing to the other two and calls the three procedures itself,
meaning your application only has to call and process the results from a
single procedure.

Best practice is to delay opening the connection until just before the first
time you use it in the page, and to close it immediately after the last time
you use it on your page. This means making use of GetRows and GetString as
much as possible (http://www.aspfaq.com/show.asp?id=2467), or using
disconnected recordsets if you absolutely must loop through a recordset so
you can close the connection immediately after the disconnection. I.E.,
minimize the time your page is connected to the database to maximize the
optimization provided by session pooling
(http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling 2.asp)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: best practices for subforms/subquerys

Bob,

Thanks for helping me out. Your post brings me to these next questions:

[first] I've read the article describing GetRows and GetString and I'll
try coverting my code. Using these methode's I think I understand I can
open my connection, run my first procedure, store it's values in an
array (GetRows), close my connection, do some HTML TABLE stuff, open my
connection again, run the other procs looping through the array to get
the parameter values, do some more HTML TABLE stuff and finally close
my connection. Am I right?

[second] The parameter values I pass to the stored procedures are
start_date, end_date and host_name. The procedures query and calculate
system monitoring values form a series of tables, to which 20.000 rows
are added on a daily basis. Could I increase performance by selecting
all host_name items between start_date and end_date into a temporary
table and start calculating an grouping from this #table?

Thanks,
Wouter

Re: best practices for subforms/subquerys

wouter@iwork.nl wrote:
> Bob,
>
> Thanks for helping me out. Your post brings me to these next
> questions:
>
> [first] I've read the article describing GetRows and GetString and
> I'll try coverting my code. Using these methode's I think I
> understand I can open my connection, run my first procedure, store
> it's values in an array (GetRows), close my connection, do some HTML
> TABLE stuff, open my connection again, run the other procs looping
> through the array to get the parameter values, do some more HTML
> TABLE stuff and finally close my connection. Am I right?

You could, but I don't think you should. Think: "single database call",
instead of "multiple database calls". I have rarely run into cases where I
needed to make more than one call to the database in a page. I strive to do
my processing in the stored procedure rather than pulling the data to the
page and making multiple database calls. Remember, you can return multiple
resultsets from a single procedure:
create procedure test as
set nocount on
select 'test1' as test
select 'test2' as test2

You would process the resultsets in asp by using NextRescordset:

set rs=createobject("adodb.recordset")
conn.test rs
if not rs.eof then ardata1=rs.getrows
set rs=rs.NextRecordset
if not rs.eof then ardata2=rs.getrows


If you absolutely cannot think of a way to create a single stored procedure
that returns your multiple resultsets, then you should probably keep your
connection open for the duration of the page instead of closing and
reopening it. There is a little overhead involved with opening a connection.
Also, if using temp tables, there is a good chnce that closing the
connection will result in that temp table being discarded, or worse, being
used by another user.

>
> [second] The parameter values I pass to the stored procedures are
> start_date, end_date and host_name. The procedures query and calculate
> system monitoring values form a series of tables, to which 20.000 rows
> are added on a daily basis. Could I increase performance by selecting
> all host_name items between start_date and end_date into a temporary
> table and start calculating an grouping from this #table?
>

I could not tell without doing some testing.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"