CODEWITHSUNDEEP
delphi
np-hard
np-hard

Reputation: 5815

TADoConnection and pooling

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

Upvotes: 3

Views: 5736

Answers (4)

Jury
Jury

Reputation:

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.

Upvotes: 1

Darian Miller
Darian Miller

Reputation: 8098

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

Upvotes: 4

Darian Miller
Darian Miller

Reputation: 8098

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.

Upvotes: 3

Stijn Sanders
Stijn Sanders

Reputation: 36850

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.

Upvotes: 1

Related Questions