Why all the connections?

I have a fairly simple application. It opens TADODataSet (SourceDataset), returning a large result set. It reads through the result rows, opening another TADODataSet (SourceLookup) for each row in the result. Both DataSets point to the same TADOConnection (SourceConnection). Then for each row, a record is inserted in another database connected to another TADOConnection.

The problem is that each time I open SourceLookup, I get a new connection to the database. (This is Microsoft SQL Server 2000, and select @@CONNECTIONS shows the number of connections going up, SourceLookup gets a SPID in the database that comes and goes...).

Since the SourceConnection is Active all the time, returning the large result set, I can't see why SourceLookup with the same TADOConnection should be making a new connection. And anyway, what about connection pooling in ADO?

The large number of connections is creating performance issues.

Details:

Delphi 7 Enterprise
MS SQL Server Developer Edition SP4
Windows XP SP2
All TADOConnections have KeepConnection=True
Note: the SourceDataSet and SourceConnection are on a form; SourceLookup is on a separate DataModule.

Thanks for anything you can suggest,

Kevin
.

Relevant Pages

  • Re: Why all the connections?
    ... It reads through the result rows, opening another TADODataSet (SourceLookup) for each row in the result. ... a record is inserted in another database connected to another TADOConnection. ... Since the SourceConnection is Active all the time, returning the large result set, I can't see why SourceLookup with the same TADOConnection should be making a new connection. ... MS SQL Server Developer Edition SP4 ...
    (borland.public.delphi.database.ado)
  • Re: Why all the connections?
    ... Both DataSets point to the same TADOConnection ... > The problem is that each time I open SourceLookup, ... > select @@CONNECTIONS shows the number of connections going up, ... > MS SQL Server Developer Edition SP4 ...
    (borland.public.delphi.database.ado)