Conn.Open() --> What is a better programming practice? 2

[dbrom]

Hey everyone!

Just wanted to ask your opinion on what is a better way to execute sql statements in a loop. First version opens and closes connection inside a loop, and the other does it once outside of it.

I have read that it is better to open and close connections immediately, but still version 1 looks like a huge overkill...

What do you think is more efficient?

VERSION 1:

var connTviDB = Server.CreateObject("ADODB.Connection"

for (i=0; i<arrMsgIDs.length; i++)
{
sqlQuery = &quot;DELETE FROM tviMemberMessages &quot;
sqlQuery += &quot;WHERE messageID = &quot; + arrMsgIDs[ i ]
connTviDB.Open(...)
connTviDB.Execute(sqlQuery)
connTviDB.Close()
Response.Write(sqlQuery + &quot;<BR>&quot
}

connTviDB = null


OR

VERSION 2


var connTviDB = Server.CreateObject(&quot;ADODB.Connection&quot;
connTviDB.Open(...)

for (i=0; i<arrMsgIDs.length; i++)
{
sqlQuery = &quot;DELETE FROM tviMemberMessages &quot;
sqlQuery += &quot;WHERE messageID = &quot; + arrMsgIDs[ i ]
connTviDB.Execute(sqlQuery)
Response.Write(sqlQuery + &quot;<BR>&quot
}

connTviDB.Close()
connTviDB = null
<Dmitriy>
dbrom@crosswinds.net

 

[DougRomeWebDevel]

Use the second method. No sense having the server do more work than necessary.

Better yet do a batch delete!

&quot;DELETE tviMemberMessages where messageID in ([coma delimited list of all ids])&quot;

 

[Ibtisam]

In my openion the second method is more professinal and i am always used it.

Finish your loop and then close the connection at the end, no need to do more work.

 

[RushiShroff]

Practice II nd method.
Because
<b>set Con=server.createObject(&quot;ADODB.Connection&quot</b>
defines the application level interface.
And
<b>Con.open ...</b>
defines the system level interface with the Database.

No meaning of redifining the interface again and again.It is a bad practice.Also It is a bad practice to execute SQL Statements again and again.You should try to concatenate string and generate SQL dynamically.Then you should execute it only once.
If you dont know how to concatenate SQL tell me.
(It doesnt work with ms-access97/2000)

Rushi@emqube.com

 

[dbrom]

Thanks!

It was very helpful.
And the idea about batch delete - that's a great idea, especially considering that the string that I am breaking up into an array is already comma-delimited...

Thanks again!
;-) <Dmitriy>
dbrom@crosswinds.net