Re: Why all the connections?

helmut woess wrote: 
Am Fri, 27 Jan 2006 14:47:52 -0500 schrieb Kevin Davidson:

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

@@connections is a counter for all connections or connection-tries since
the start of the server. So this value grows up till you stop and restart
the server, then the counter will start from 0 again. To Check what's actually running try a "sp_who" without parameters.

bye,
Helmut
This is running on a local workstation server, with one SQL application running -- so all the connections must be coming from this one application.
.

Relevant Pages

  • Why all the connections?
    ... It opens TADODataSet, ... 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. ...
    (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)
  • Re: Reconnect does not work TADOconnection
    ... We've dealt with similar issues using ADO and SQL Server. ... close the connection it also closes any open ADOdataset's that are bound to ... TADOconnection. ... Using an exception handler something like this: ...
    (borland.public.delphi.database.ado)
  • Re: Two connections with Delphi 7 Professional?
    ... The timeout value is also a property of TADOConnection (CommandTimeout). ... > more than one connection at a time,without any restrictions? ... Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. ...
    (borland.public.delphi.database.ado)
  • TAdoConnection Disconnect
    ... (Delhi 7, Sql Server 2000) ... I have a TAdoConnection and I open this connection with starting program ... After a button click I want to close connection (after this time user can ...
    (borland.public.delphi.database.ado)