In Classic ASP, are there major performance consequences of having several connections open and close?

Question

I have a code snippet similar to the one below that I would like to refactor into two different functions each with their own connection (for better maintenance):

Dim Conn, Sql, RS

Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open ConnString

Sql = SELECT * FROM CLIENTS

Set RS = Conn.Execute(sql)
//'Do something with Clients

Set RS = Nothing

Sql = SELECT * FROM DEALERS

Set RS = Conn.Execute(sql)
//'Do something with Dealers

Set RS = Nothing

Conn.Close
Set Conn = Nothing

Will having two functions (e.g. GetClients and GetDealers) each opening and closing their own connections have a major performance hit, opposite only opening and closing a single connection as illustrated above? If so, how would you refactor the code?

Answer 1

If I am not mistaken the connections are pooled so it should cost next to nothing to have each function open and close the connections.

Answer 2

You shouldn't have any performance problems in this case however it is good practice to use a single connection if the data source is the same.

A better way of doing it would be to pass a single opened connection object into each of the GetClients and GetDealers functions.

Answer 3

Connections are pooled automatically, so the cost is very small.

It isn't zero however. Every time you pull a connection from the pool and reopen it, you send a sp_reset_connection command to SQL Server. This is incredibly cheap, so I wouldn't worry about it.

Side note: A single ADODB.Connection may represent multiple database connections. If you try to use the same one to open a second recordset while still reading from the first one, there is a chance it will create a new one in the background. Again, this isn't a real concern just something you should know.

Answer 4

Connection pooling is available with classic ASP. I would refactor that code to use a method that accepts a connection string, and open and close the connection as quickly as possible within that method.

At least as concerning as the connection usage is the fact that you are not using disconnected recordsets (which ADO.NET implements by default). Disconnected recordsets let you close the connection and give it back to the pool as soon as you have done your query, rather than having to wait until you have iterated through the recordset.

Here is how you would do that in JScript; it should be simple to port to VBScript:

var sql = "select * from MyTable";
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
var nothing = rs.ActiveConnection;
cn.Open(connectString);
rs.CursorLocation = 3; //adUseClient
rs.Open(sql, cn, 3, 1);
rs.ActiveConnection = nothing;
cn.Close();
//now do something with disconnected rs

Answer 5

1. Don't use select * but specify columns you need.
2. Use getrows.
3. Specify what you want to have from the database (Use joins and where clause).

When you do all this your code will be optimal.

Answer 6

connections should be pooled