How to avoid inefficient execution of ASP's SQL (reprint)

Method One, try to use complex SQL instead of a simple stack of SQL.
For the same transaction, a complex SQL completion is more efficient than a bunch of simple SQL finishes. When you have multiple queries, be good at using joins.
Ors=oconn.execute ("SELECT * FROM Books")
While not ors.eof
strSQL = "SELECT * from Authors WHERE authorid=" &ors ("Authorid") Ors2=oconn.execute (strSQL)
Response.Write ORs ("Title") & ">>" &ors2 ("Name") & "
&q Uot;
Ors.movenext ()
Wend
Slower than the following code:
Strsql= "SELECT books.title,authors.name from Books JOIN Authors on Authors.authorid=books.authorid"
Ors=oconn.execute (strSQL)
While not ors.eof
Response.Write ORs ("Title") & ">>" &ors ("Name") & "
&qu ot;
Ors.movenext ()
Wend
Method Two, try to avoid using updatable Recordset
Ors=oconn.execute ("select * from Authors WHERE authorid=17", 3, 3)
ORs ("Name") = "Darkman"
Ors.update ()
Slower than the following code:
strSQL = "UPDATE Authors SET name= ' Darkman ' WHERE authorid=17"
Oconn.execute strSQL
Method III, update the database, as much as possible with batch update
Make all the SQL into a large batch SQL and run it one at a time, which is much more efficient than updating data one by one. This also satisfies your need for transaction processing:
Strsql= ""
strsql=strsql& "SET xact_abort on";
strsql=strsql& "BEGIN TRANSACTION";
strsql=strsql& "INSERT into Orders (ordid,custid,orddat) VALUES (' 9999 ', ' 1234 ', GETDATE ())";
strsql=strsql& "INSERT into Orderrows (ordid,ordrow,item,qty) VALUES (' 9999 ', '", ' G4385 ', 5) ";
strsql=strsql& "INSERT into Orderrows (ordid,ordrow,item,qty) VALUES (' 9999 ', '", ' G4726 ', 1) ";
strsql=strsql& "COMMIT TRANSACTION";
strsql=strsql& "SET xact_abort off";
Oconn.execute (strSQL);
Where the SET xact_abort off statement tells SQL Server to cancel a transaction that has been completed if an error is encountered during the following transaction processing.