TADoConnection and pooling

Question

I am working on all application that uses multiple threads that access a sql server 2005 db, i am currently using implicit connections by setting up the connection string property of the TADOQuery object, but that seems to open a lot of connections to the database. We need to reduce the number of connections that a single instance keeps open, so

1. is there any kind of connection pooling available in tadoconnection library, and how can i enable it and set it limits.
2. if not then whats the recommended way of reducing and reusing connections to the database, so that the application scales well.

i would appreciate any opinions or thoughts..

many thanks

Answer 1

Another reason for increased number of connections is multiple open queries active on the same connection.

Say you have code like

db := TAdoConnection.Create()
qry1 := TAdoQuery.Create();
qry2 := TAdoQuery.Create();

qry1.connection := db;
qry2.connection := db;

qry1.SQL.Text := 'select * from whatever';

qry1.open;
while not qry1.eof do
begin
  x := qry1.fieldbyname('fld').AsString;
  qry2.SQL.Text := 'select * from elsewhere where SomeField='+ QuotedStr(x);
  qry2.Open;
  ..do something..
  qry2.Close;

  qry1.Next;
end;

The qry2.Open() will cause another connection to be created which is not pooled

If you have code like this, store the results of qry1 to a local place and then do the work of qry2. In this case, you will maintain only one connection.

In rough terms this looks more like:

db := TAdoConnection.Create()
qry1 := TAdoQuery.Create();
qry2 := TAdoQuery.Create();
str := TStringList.Create();

qry1.connection := db;
qry2.connection := db;

qry1.SQL.Text := 'select * from whatever';

qry1.open;
while not qry1.eof do
begin
  x := qry1.fieldbyname('fld').AsString;
  str.Add(x);
  qry1.Next;
end;
qry1.Close;

for i := 0 to str.Count-1 do
begin
  qry2.SQL.Text := 'select * from elsewhere where SomeField='+ QuotedStr(str[i]);
  qry2.Open;
  ..do something..
  qry2.Close;
end