Handling Loop Within Loop 1

[wordomouth]

Hi all. I have an application where I have a proc in an outer loop that returns a large recordset and then within that loop I have another proc that gets hit a ton of time because for each record in the outer loop I have to pull detail records in for the header record part number. So I turned on SQL Profiler and I see the proc getting called over and over again and it hangs the whole system. How else can I handle something like this in my ASP page to show information from the header and then fetch based on the each header record the detail from another table using the Part Number? Its not easy combining the 2 queries into 1 set because they are complex queries. I was thinking could I use a disconnected recordset or something. Need help big time here thanks all.

 

[steven290]

2 ways

first loop using getrows method which disconnects from database right away because it you justs makes 1 call to the db and brings it in as array

second is to use a join sql

 

[tortoisehalfbreed]

Could you not use hierarchical recordset? I've used this once before - its tricky to get right but got good results.. have a look at this...

http://www.4guysfromrolla.com/webtech/060301-1.shtml

and also, try increasing the cachesize of your recordset - to say 100, so instead of making 100 return trips for 100 records, the program only makes 1.

Hope this helps

 

[steven290]

you could, but performance-wise vs the getrows method it doesn't compare. But i guess it comes down to preference

 

[vongrunt]

first loop using getrows method which disconnects from database right away

Can you explain this a little bit more? I'm not sure how getRows() itself can reduce number of procedure calls.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[steven290]

you can do a

sql="select * from tblname"
set rs = conn.execute(sql)
if not rs.eof then
rows=rs.getrows
rs.close
conn.close
set rs=nothing
set conn=nothing

'at this point you have closed the connection
'we can now loop through the records as much as we want
'without making a call to the db

else
norecords=true
end if


for i=lbound(rows,2) to ubound(rows,2)
response.write rows(0,i) &"<br>"
next


for i=lbound(rows,2) to ubound(rows,2)
response.write rows(1,i) &"<br>"
next

for i=lbound(rows,2) to ubound(rows,2)
response.write rows(2,i) &"<br>"
next

'i've just looped through the entire table 3 separate times,
'displaying 3 separate columns - all while my coonnection
'to the db is close, try that using the
'do while not rs.eof method you would have to close
'after the last loop

 

[vongrunt]

This applies to which query? The one that should retrieve all detail records at once?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[steven290]

it can apply to all queries but i think its most effective on ones that deal with alot of records at once

 

[steven290]

but depending on what he is trying to do, he might be able achieve what he wants using a join in sql