title: Classic ASP - When to close recordset
body: <p>I would like to know, which of the following examples is the best for closing a recordset object in my situation?</p>
  
  <p><strong>1)</strong></p>
  
  <p>This one closes the object inside the loop but opens a new object when it moves next. If there were 1000 records, this opens an object 1000 times and closes it 1000 times. This is what I would normally do:</p>
  
  <pre><code>SQL = " ... "
  Set rs1 = conn.Execute(SQL)
  While NOT rs1.EOF
  
      SQL = " ... "
      Set rs2 = conn.Execute(SQL)
      If NOT rs2.EOF Then
          Response.Write ( ... )
      End If
      rs2.Close : set rs2 = Nothing
  
  rs1.MoveNext
  Wend
  rs1.Close : Set rs1 = Nothing
  </code></pre>
  
  <p><strong>2)</strong></p>
  
  <p>This example is what I want to know about. Does saving the object closure (rs2.close) until after the loop has finished, gains or reduces performance? If there were 1000 records, this would open 1000 objects but only closes it once:</p>
  
  <pre><code>SQL = " ... "
  Set rs1 = conn.Execute(SQL)
  While NOT rs1.EOF
  
      SQL = " ... "
      Set rs2 = conn.Execute(SQL)
      If NOT rs2.EOF Then
          Response.Write ( ... )
      End If
  
  rs1.MoveNext
  Wend
  rs1.Close : Set rs1 = Nothing
  rs2.Close : set rs2 = Nothing
  </code></pre>
  
  <p>I hope I've explained myself well enough and it's not too stupid.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>To those who think my query can be modified to avoid the N+1 issues (2nd query), here it is:</p>
  
  <p>This is for an online photo library. I have two tables; "photoSearch" and "photos". The first, "photoSearch", has just a few columns and contains all searchable data for the photos, such as "photoID", "headline", "caption", "people", "dateCaptured" and "keywords". It has a multi-column full-text index on (headline, caption, people, keywords). The second table, "photos", contains all of the photos data; heights, widths, copyrights, caption, ID's, dates and much more. Both have 500K+ rows and the headline and caption fields sometimes return 2000+ characters.</p>
  
  <p>This is approximately how the query looks now:
  (things to note: I cannot use joins with fulltext searching, hence keywords being stored in one column - in a 'de-normalized' table. Also, this kind of pseudo code as my app code is elsewhere - but it's close )</p>
  
  <pre><code>SQL = "SELECT photoID FROM photoSearch
  WHERE MATCH (headline, caption, people, keywords)
  AGAINST ('"&amp;booleanSearchStr&amp;"' IN BOOLEAN MODE)
  AND dateCaptured BETWEEN '"&amp;fromDate&amp;"' AND '"&amp;toDate&amp;"' 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 FROM photos LEFT JOIN events AS e USING (eventID) LEFT JOIN location AS l USING (locationID) WHERE photoID = "&amp;rs1.Fields("photoID")&amp;";"
      Set rs2 = conn.Execute(SQL)
      If NOT rs2.EOF Then
          Response.Write ( .. photo data .. )
      End If
      rs2.Close
  
  rs1.MoveNext
  Wend
  rs1.Close
  </code></pre>
  
  <p>When tested, having the full-text index on its own table, "photoSearch", instead of the large table, "photos", seemed to improve speed somewhat. I didn't add the "photoSearch" table, it was already there - this is not my app. If I try joining the two tables to lose the second query, I lose my indexing all together, resulting in very long times - so I can't use joins with full-text. This just seemed to be the quickest method. If it wasn't for the full-text and joining problems, I would have combined both of these queries already.</p>