Geek asks and answers

Classic ASP - When to close a recordset

I would like to know which of the following examples is best for closing a record set object in my situation?

1)

This object closes the object inside the loop, but the next time it opens, a new object opens. If there were 1000 entries, this opens the object 1000 times and closes it 1000 times. This is what I usually did:

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

2)

This example is what I want to know about. Does object closure (rs2.close) persist until the loop completes, improves or decreases performance? If there were 1000 records, this would open 1000 objects, but only close them once:

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

I hope I explained myself well enough, and this is not too stupid.

UPDATE

For those who believe that my request can be modified to avoid problems with N + 1 (second request), here it is:

-. ; "photoSearch" "" . -, "photoSearch" , "photoID", "headline", "caption", "people", "dateCaptured" "keywords". (, , , ). , "" , ; , , , , , . 500K +, 2000+ .

, : ( : , - "-" . , , )

SQL = "SELECT photoID FROM photoSearch
WHERE MATCH (headline, caption, people, keywords)
AGAINST ('"&booleanSearchStr&"' IN BOOLEAN MODE)
AND dateCaptured BETWEEN '"&fromDate&"' AND '"&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 FROM photos LEFT JOIN events AS e USING (eventID) LEFT JOIN location AS l USING (locationID) WHERE photoID = "&rs1.Fields("photoID")&";"
    Set rs2 = conn.Execute(SQL)
    If NOT rs2.EOF Then
        Response.Write ( .. photo data .. )
    End If
    rs2.Close

rs1.MoveNext
Wend
rs1.Close

, , "photoSearch" "" , , . "photoSearch", - . , , , , . , . , .

+5
3

. -, mysql, , , , ...

, . :

1 : PhotoId, "PhotoIds"

SELECT photoID FROM photoSearch
WHERE MATCH (headline, caption, people, keywords)
AGAINST ('"&booleanSearchStr&"' IN BOOLEAN MODE)
AND dateCaptured BETWEEN '"&fromDate&"' AND '"&toDate&"' LIMIT 0,50) AS PhotoIds

2 , . WHERE , . , "" .

SQL = "SELECT p.photoID, p.setID, p.eventID, p.locationID, p.headline, p.caption, + more FROM
    photos AS p,
    events AS e USING (p.eventID),
    location AS l USING (p.locationID),
    (SELECT photoID FROM photoSearch WHERE MATCH (headline, caption, people, keywords)
        AGAINST ('"&booleanSearchStr&"' IN BOOLEAN MODE) AND dateCaptured BETWEEN
        '"&fromDate&"' AND '"&toDate&"' LIMIT 0,50) AS PhotoIds
    WHERE p.photoID=PhotoIds.photoID;"

: . smt. , , .

, . Execute , ( ), : "INSERT", "DELETE", "UPDATE". , -, ? Rs = Nothing, , ( mysql). "SELECT" (, ), (ADODB.Recordset), , , .

- " mysql" .. , ( ) include , . : (), ()

+4

SQL-, , , SQL, , , , . " ", :

 Set Conn = Server.CreateObject("Adodb.Connection")
 Conn.Open "ConnectionString"

 Set oRS = Server.CreateObject("Adodb.Recordset")
 oRS.Open "SQL STATEMENT", Conn

 Set oRS2 = Server.CreateObject("Adodb.Recordset")
 oRS2.ActiveConnection = Conn

 Do Until oRS.EOF

    oRS2.Open "SQL STATEMENT"
    If oRS2.EOF Then ...
    oRS2.Close

 oRS.Movenext
 Loop
 oRS.Close
 Set oRS = Nothing
 Set oRS2 = Nothing
 Set Conn = Nothing
+3

, , ..:)

, . "... ( ID photoSearch...)". , , , , . , , . .

, /​​ ? , , , , ...:)

One more thought - you can consider some caching logic to reduce the number of redundant requests that you can make, either at the page level or at the level of this method. Search parameters can be combined together to form a key for storing data in a cache of some type. Of course, you will need to handle the appropriate invalidation / expiration caching logic. I saw systems speeding 100x with very simple caching logic added to such bottlenecks.

0
source

More articles:


All Articles