Performance Issue

<i><b>Originally posted by : Peter Young (petersgyoung@hotmail.com)</b></i><br />This is the first time I join this forum. I would like to post a performance issue to start off the thread. The following are three different methods for updating a database. Which one is more efficen? I personally prefer think Method 2 is the best if the database is large and there are many users updating the database at the same time.<br /><br /><br />METHOD 1<br /><%<br />Dim cnn<br />Dim rs1<br />Dim rs2<br />Dim strA, strB, strC, strD<br /><br />strA = Request.Form("A")<br />strB = Request.Form("B")<br />strC = Request.Form("C")<br />strD = Request.Form("D")<br /><br />Set cnn = Server.CreateObject("ADODB.Conncetion")<br />cnn.Open "DB1"<br /><br />rs1.Open "Select * from TableA", cnn, 2, 2<br />rs2.Open "Select * from TableB", cnn, 2, 2<br /><br />rs1.Addnew<br />rs2.Addnew<br /><br />rs1("fieldA1") = strA <br />rs1("fieldA2") = strB<br /><br />rs2("fieldB1") = strC<br />rs2("fieldB2") = strD<br /><br />rs1.Update<br />rs2.Update<br /><br />rs1.Close<br />rs2.Close<br /><br />Set rs1 = nothing<br />Set rs2 = nothing<br /><br />Set cnn = nothing<br /><br />%><br /><br /><br />METHOD 2<br /><br /><%<br />Set cnn = Server.CreateObject("ADODB.Conncetion")<br />cnn.Open "DB1"<br /><br />rs1.Open "Select * from TableA", cnn, 2, 2<br />With rs1<br /> .Addnew<br /> .fields("fieldA1") = Request.Form("A")<br /> .fields("fieldA2") = Request.Form("B")<br />'Add other rs1 fields here if necessary<br />End With<br /><br />rs2.Open "Select * from TableB", cnn, 2, 2<br /><br />With rs2<br /> .Addnew<br /> .fields("fieldB1") = Request.Form("C")<br /> .fields("fieldB2") = Request.Form("D")<br />'Add other rs2 fields here if necessary<br />End With<br /><br />rs1.Update<br />rs2.Update<br /><br />Set rs1 = nothing<br />Set rs2 = nothing<br /><br />Set cnn = nothing<br /><br />%><br /><br /><br />METHOD 3<br /><br /><%<br />'cnn created and open at Session_OnStart()<br /><br />rs1.Open "Select * from TableA", cnn, 2, 2<br />rs2.Open "Select * from TableB", cnn, 2, 2<br /><br />rs1.Addnew<br />rs2.Addnew<br /><br />rs1("fieldA1") = Request.Form("A")<br />rs1("fieldA2") = Request.Form("B")<br /><br />rs2("fieldB1") = Request.Form("C")<br />rs2("fieldB2") = Request.Form("D")<br /><br />rs1.Update<br />rs2.Update<br /><br />Set rs1 = nothing<br />Set rs2 = nothing<br /><br />'cnn to be closed at Session_OnEnd()<br /><br />%><br /><br />I don't have a chance to test these methods on a large database with many users. Anybody wants to do this experiment?<br /><br />Peter