TADoConnection and pooling

Question

Hello,

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

Setting the TADOConnection component on the DataModule and using that from all other ADO components on the datamodule should already reduce a lot of the connections. If the application is using a great number of datamodules, it's possible to make these datamodules share the same connection at runtime.

Answer 2

By default, ADO database connections to SQL Server are pooled with no work needed by you. The pooling behavior can be altered by your connection string. What's your connection string look like?

Just keep your connection string exactly the same and create/free a ADO Connection within your thread and let the ADO runtime handle the pooling. The number of connections per process will usually equal to the max number of "recent" simultaneous connections plus a few for a buffer.

Answer 3

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

Answer 4

Microsoft says: "...to disable OLE DB Resource Pooling, which ADO uses by default ... you can do by adding "OLE DB Services = -2" to the ADO Connection string".

Here is the link: http://support.microsoft.com/kb/229564

It's very useful when a client application uses an application role.