2

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?

PropellerHead
  • 929
  • 1
  • 12
  • 27

6 Answers6

5

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.

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
1
  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.

Mischa Kroon
  • 1,772
  • 1
  • 13
  • 19
0

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.

Craig Bovis
  • 2,720
  • 3
  • 26
  • 33
  • Why do you think it is a good idea to keep the connection open? – Jonathan Allen Jun 03 '09 at 20:54
  • It's one school of thought: Use 1 connection for the lifetime of 1 request. Since # of requests are limited to # of threads available to the IIS app, and those are typically even more limited than db connections, you won't generally run into problems as long as the connection is released at the end of the request. – Joel Coehoorn Jun 03 '09 at 21:02
  • Joel, you are assuming the web app is the only thing hitting the database. I would recommend keeping connections open for as short a time as possible, via disconnected recordsets. – D'Arcy Rittich Jun 03 '09 at 21:31
  • Here is a link that explains disconnected recordsets that I found usefull http://www.4guysfromrolla.com/webtech/080101-1.shtml. – PropellerHead Jun 04 '09 at 05:06
  • @Craig: I don't think using a single connection for both requests in necessarily good practice. In good practice the retrieval of variety of recordsets during a request would be refactored into separate functions. Sharing a connection across such otherwise de-coupled functions would be a bad idea, it would be better for each function to retrieve a connection themselves that way the retrieved connection will be in known state. – AnthonyWJones Jun 04 '09 at 07:17
0

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.

Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447
0

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
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • This approach really should be re-assessed in light of modern hardware. Just how many connections is too many on a modern SQL Server? Also worth considering is that a client cursor comes with a significant memory cost on the web server yet most ASP based SQL usage can be performed with a standard forward-only "firehose" rowset which has a very small memory footprint by comparison. – AnthonyWJones Jun 04 '09 at 07:31
  • For me the key issue is that it is far easier to scale out web servers than database servers, so it is important to minimize the impact on the database server. – D'Arcy Rittich Jun 04 '09 at 13:28
0

connections should be pooled