object - Classic ASP - When to close recordset -

i know, of following examples best closing recordset object in situation?

1)

this 1 closes object inside loop opens new object when moves next. if there 1000 records, opens object 1000 times , closes 1000 times. do:

sql = " ... " set rs1 = conn.execute(sql) while not rs1.eof sql = " ... " set rs2 = conn.execute(sql) if not rs2.eof response.write ( ... ) end if rs2.close : set rs2 = nothing rs1.movenext wend rs1.close : set rs1 = nothing 

2)

this example want know about. saving object closure (rs2.close) until after loop has finished, gains or reduces performance? if there 1000 records, open 1000 objects closes once:

sql = " ... " set rs1 = conn.execute(sql) while not rs1.eof sql = " ... " set rs2 = conn.execute(sql) if not rs2.eof response.write ( ... ) end if rs1.movenext wend rs1.close : set rs1 = nothing rs2.close : set rs2 = nothing 

i hope i've explained myself enough , it's not stupid.

update

to think query can modified avoid n+1 issues (2nd query), here is:

this online photo library. have 2 tables; "photosearch" , "photos". first, "photosearch", has few columns , contains searchable data photos, such "photoid", "headline", "caption", "people", "datecaptured" , "keywords". has multi-column full-text index on (headline, caption, people, keywords). second table, "photos", contains of photos data; heights, widths, copyrights, caption, id's, dates , more. both have 500k+ rows , headline , caption fields return 2000+ characters.

this approximately how query looks now: (things note: cannot use joins fulltext searching, hence keywords being stored in 1 column - in 'de-normalized' table. also, kind of pseudo code app code elsewhere - it's close )

sql = "select photoid photosearch match (headline, caption, people, keywords) against ('"&booleansearchstr&"' in boolean mode) , datecaptured between '"&fromdate&"' , '"&todate&"' limit 0,50;" set rs1 = conn.execute(sql) while not rs1.eof sql = "select photoid, setid, eventid, locationid, headline, caption, instructions, datecaptured, dateuploaded, status, uploaderid, thumbh, thumbw, previewh, previeww, + more photos left join events e using (eventid) left join location l using (locationid) photoid = "&rs1.fields("photoid")&";" set rs2 = conn.execute(sql) if not rs2.eof response.write ( .. photo data .. ) end if rs2.close rs1.movenext wend rs1.close 

when tested, having full-text index on own table, "photosearch", instead of large table, "photos", seemed improve speed somewhat. didn't add "photosearch" table, there - not app. if try joining 2 tables lose second query, lose indexing together, resulting in long times - can't use joins full-text. seemed quickest method. if wasn't full-text , joining problems, have combined both of these queries already.

here thing. first, photo ids , make mysql thinks actual table hold photo ids only, , make actual statement, no need recordset connections...

and not forget start end this. here sample code explanations:

step 1 create photo ids lookup table , name it: our photoid lookup table name "photoids"

select photoid photosearch match (headline, caption, people, keywords) against ('"&booleansearchstr&"' in boolean mode) , datecaptured between '"&fromdate&"' , '"&todate&"' limit 0,50) photoids 

step 2 have photo ids, informations it. insert above statement before clause same way real tables. note our "fake" table must between parantheses.

sql = "select p.photoid, p.setid, p.eventid, p.locationid, p.headline, p.caption, + more photos p, events e using (p.eventid), location l using (p.locationid), (select photoid photosearch match (headline, caption, people, keywords) against ('"&booleansearchstr&"' in boolean mode) , datecaptured between '"&fromdate&"' , '"&todate&"' limit 0,50) photoids p.photoid=photoids.photoid;" 

note: write these codes here , never tested. there may spelling errors or smt. please let me know if have troubles.

now getting primary question

no need close executed queries, if using execute method. execute method closes after execution unless not returning recordset data (thats purpose of execute command @ first place) like: "insert", "delete", "update". if didnt open recordset object, why try close never opened? instead can use set rs=nothing unreference object , send garbage collection free system resources (and thats nothing mysql itself). if using "select" queries, (the queries return data) must open recordset object (adodb.recordset) , if opened it, need close finishes job.

the important thing close "main connection mysql server" after each page load. may consider put connection close algorithm (not recordset close) include file , insert @ end of everypage make connection database. long talk short: must use close() if used open()