Nested For Loops

[dw]

Hello all. We're doing a site with teams and their members. We've got a page
where we need to display people according to who belongs to a which team.
I've heard that nested loops are bad, but what's the alternative? Would a
group-by clause in the SELECT do the trick? Right now we're doing:

(pseudo-code)---------------------------------
For each team
print team name
For each peson
print person name
Next
Next

(actual code)-----------------------------------
For i = 1 to rsTeams.Recordcount
rsMain.Filter = "team = '" & rsTeams("team") & "'"
Response.Write "<b>The following people are on the team "&
rsTeams("team") & ":</b><p>"
For j = 1 To rsMain.Recordcount
Response.Write rsMain("person_name")
Response.Write "<br>"
rsMain.movenext
Next
If rsTeams.EOF Then Exit For
rsTeams.movenext
Next

[David Morgan]

I don't know that nested loops are bad, but nested loops over recordsets
with filtering could probably be done a more efficiemt way.

Have a look at the MSDataShape Provider. It could help you out here and
remove the need for filtering.

Something like this would return you one recordset, with a child recordset
in the last field position of the first recordset.

SHAPE {select * from customers}
APPEND ({select * from orders} AS rsOrders
RELATE customerid TO customerid)

This would give some ASP something like this....

Dim objConn, objRs, rsChild

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open "PROVIDER=MSDataShape;DATA " & stExistingConnectionString '
Assumes your connection string starts with the word PROVIDER

stSql = "SHAPE {select TeamID, TeamName from Teams} APPEND ({select TeamID,
TeamMemberID, TeamMemberName from TeamMembers} AS Whatever RELATE TeamID TO
TeamID) "

Set objRs = objConn.Execute(stSql, , adCmdText)

If Not objRs.EOF Then
Do While Not objRs.EOF

Response.Write "<p>" & Server.HTMLEncode(objRs.Fields(1).Value)

Set rsChild = objRs.Fields(2).Value
If Not rsChild.EOF Then
Response.Write "<blockquote>"

Do While Not rsChild.EOF

Response.Write Server.HTMLEncode(rsChild.Fields(2).Value) &
"<br>"

rsChild.MoveNext
Loop
Response.Write "</blockquote>"
End If

Set rsChild = Nothing

Response.Write "</p>"

objRs.MoveNext
Loop
End If

objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
"dw" <co***************@uncw.edu> wrote in message
news:uj**************@TK2MSFTNGP11.phx.gbl...

Hello all. We're doing a site with teams and their members. We've got a page where we need to display people according to who belongs to a which team.
I've heard that nested loops are bad, but what's the alternative? Would a
group-by clause in the SELECT do the trick? Right now we're doing:

(pseudo-code)---------------------------------
For each team
print team name
For each peson
print person name
Next
Next

(actual code)-----------------------------------
For i = 1 to rsTeams.Recordcount
rsMain.Filter = "team = '" & rsTeams("team") & "'"
Response.Write "<b>The following people are on the team "&
rsTeams("team") & ":</b><p>"
For j = 1 To rsMain.Recordcount
Response.Write rsMain("person_name")
Response.Write "<br>"
rsMain.movenext
Next
If rsTeams.EOF Then Exit For
rsTeams.movenext
Next

[Jeremy]

> Hello all. We're doing a site with teams and their members. We've got a
page

where we need to display people according to who belongs to a which team.
I've heard that nested loops are bad, but what's the alternative? Would a
group-by clause in the SELECT do the trick? Right now we're doing:

(pseudo-code)---------------------------------
For each team
print team name
For each peson
print person name
Next
Next

Yes, a group by clause would do the trick, but using a group-by clause and
then caching the output would do the trick, better:

If IsCacheExpired(CacheDataKey, CacheExpireKey, ExpireInterval) Then
Dim LastTeam
For Each TeamMember

If LastTeam <> TeamMember.Team Then
Print TeamMember.TeamName
LastTeam = TeamMember.Team
End If

Print TeamMeber.PersonName
Next
End If

Assuming here that IsCacheExpired( ) checks an Application (or session
depending on the scope) variable to see if the cache is expired.
IsCacheExpired uses the CacheDataKey to hold the actual value in
Application, and uses CacheExpireKey to check if the cache needs to be
refreshed, and uses ExpireInterval to calculate the next expire date. You
may also want to keep a list of Keys around incase you want to release the
entire cache when data is written to the database. We also use several
Constants to indicate the refresh interval. For example:

Const CRI_FAST = 1 '// 1 hour
Const CRI_SLOW = 6 '// 6 hours
Const CRI_DAY = 23 '// 1 day

This allows us to change the meaning of a FAST refresh without looking
through every line of code to change a 1 to a 2. Our algorithm uses hours as
the unit of measure, but that is just our implementation.

An even better way to do this would be to save the keys in some sort of
resource, such as an XML file. This way, the refresh interval, CacheDataKey,
and CacheExpire key will be bound together by one single ID, and can be
referenced as such. Then you would just need a list of constants for the
ID's.

If you are doing performance tweaking, I highly recomend this page:
http://msdn.microsoft.com/library/de...ml/asptips.asp

And this one for String optimizations:
http://msdn.microsoft.com/library/de...aspstrcatn.asp

And this one for ADO & SQL:
http://msdn.microsoft.com/library/de...l/BestPrac.asp
HTH,
Jeremy

[dw]

Thank you, David and Jeremy. Very useful answers. Thanks :-)

"Jeremy" <th***********@hotmail.com> wrote in message
news:dB*********************@twister.tampabay.rr.c om...

Hello all. We're doing a site with teams and their members. We've got a page

where we need to display people according to who belongs to a which team. I've heard that nested loops are bad, but what's the alternative? Would a group-by clause in the SELECT do the trick? Right now we're doing:

(pseudo-code)---------------------------------
For each team
print team name
For each peson
print person name
Next
Next

Yes, a group by clause would do the trick, but using a group-by clause and
then caching the output would do the trick, better:

If IsCacheExpired(CacheDataKey, CacheExpireKey, ExpireInterval) Then
Dim LastTeam
For Each TeamMember

If LastTeam <> TeamMember.Team Then
Print TeamMember.TeamName
LastTeam = TeamMember.Team
End If

Print TeamMeber.PersonName
Next
End If

Assuming here that IsCacheExpired( ) checks an Application (or session
depending on the scope) variable to see if the cache is expired.
IsCacheExpired uses the CacheDataKey to hold the actual value in
Application, and uses CacheExpireKey to check if the cache needs to be
refreshed, and uses ExpireInterval to calculate the next expire date. You
may also want to keep a list of Keys around incase you want to release the
entire cache when data is written to the database. We also use several
Constants to indicate the refresh interval. For example:

Const CRI_FAST = 1 '// 1 hour
Const CRI_SLOW = 6 '// 6 hours
Const CRI_DAY = 23 '// 1 day

This allows us to change the meaning of a FAST refresh without looking
through every line of code to change a 1 to a 2. Our algorithm uses hours

as the unit of measure, but that is just our implementation.

An even better way to do this would be to save the keys in some sort of
resource, such as an XML file. This way, the refresh interval, CacheDataKey, and CacheExpire key will be bound together by one single ID, and can be
referenced as such. Then you would just need a list of constants for the
ID's.

If you are doing performance tweaking, I highly recomend this page:
http://msdn.microsoft.com/library/de...ml/asptips.asp
And this one for String optimizations:
http://msdn.microsoft.com/library/de...aspstrcatn.asp
And this one for ADO & SQL:
http://msdn.microsoft.com/library/de...l/BestPrac.asp

HTH,
Jeremy

[Mark Schupp]

Maybe I'm missing something but it seems to me that it would be simpler to
join the tables and order by team name

make a single pass through the data and output the "team heading" when the
team name changes.

strTeam = ""

while not rsdata.eof

if strTeam <> rsdata("team") then
'out put team name and message here
strTeam = rsData("team")
end if

'output team member info here.

loop

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"dw" <co***************@uncw.edu> wrote in message
news:uj**************@TK2MSFTNGP11.phx.gbl...

Hello all. We're doing a site with teams and their members. We've got a page where we need to display people according to who belongs to a which team.
I've heard that nested loops are bad, but what's the alternative? Would a
group-by clause in the SELECT do the trick? Right now we're doing:

(pseudo-code)---------------------------------
For each team
print team name
For each peson
print person name
Next
Next

(actual code)-----------------------------------
For i = 1 to rsTeams.Recordcount
rsMain.Filter = "team = '" & rsTeams("team") & "'"
Response.Write "<b>The following people are on the team "&
rsTeams("team") & ":</b><p>"
For j = 1 To rsMain.Recordcount
Response.Write rsMain("person_name")
Response.Write "<br>"
rsMain.movenext
Next
If rsTeams.EOF Then Exit For
rsTeams.movenext
Next