Closing unused connections in the connection pool

[Nevena Komarova]

Hi,

I have ASP.NET web service connecting to an Interbase 7.1 database via
BDPConnection with connection pooling enabled. How can I setup the time
that unused connections remain in the connection pool, so if there are
no requests to the service for a long period of time the connections in
the pool to be closed.

Thanks in advance,

Nevena

[Robin]

Hi Nevena,

BDP has a setting called 'Connection Lifetime'.

More info can be found at the following link:

http://dn.codegear.com/article/33443

--
Robin.

Australian Bridal Accessories := http://www.bridalbuzz.com.au
Turbo for Noobs (a work in progress) := http://turbofornoobs.blogspot.com/

[Nevena Komarova]

Thanks Robin,

I did tried to set the connection lifetime, but seems that it checks for
how long the connection has stayed open and it is checked on
connection.close when the connections is returned to the pool. What I
want is to close the connections that are in the pool, but not used for
a long period of time, lets say if there no requests to the service for
15 min I want to close the connections in the pool.

[Robin]

Are you explicitly closing your datareaders etc when you are finished
with them in code?

This is pretty important, and easy to skip.

[Nevena Komarova]

Yes, I am closing the datareader, I double checked that. I also did a
test with a very basic service and it has the same issues, can't see
what is wrong:

BdpCon := BdpConnection.Create('assembly=Borland.Data.Interbase,
Version=' +
'2.5.0.0, Culture=neutral,
PublicKeyToken=91d62ebb5b0d1b1b;vendorclient=gd' +
's32.dll;pooling=True;provider=Interbase;grow on
demand=True;database=c:\test\test.gdb;username=sysdba;max pool
size=20;passwo' +
'rd=masterkey;connection lifetime=0;min pool size=0');

c_sel := 'select customername from customer '
+ 'where (customerid='''+customerid+''') ';
bdpcon.Open;
bdpcmd := BDPCommand.Create(c_sel, bdpcon);
try
bdpdr := bdpcmd.ExecuteReader;
if bdpdr.read then result := bdpdr.GetString(0);
finally
bdpdr.close;
bdpcmd.Close;
bdpcon.Close;
end;

[Mike Chamberlain]

Hello Navena,

I also use BDP 2.5 with ASP.NET webservices and had the same problem as
you. My research indicates that there is no way in BDP 2.5 to do what
you are doing. Once a connection is opened, it stays open until your
app quits (or in your case when IIS is restarted). The connection
lifetime parameter refers to something else and is not relevant for this.

However, for me it's never really been that much of a problem. My web
service opens as many connections as it needs to process the maximum
number of concurrent DB requests that occur. This usually stabalizes
for us at around 4. It's cheap enough to leave a connection open, but
more expensive to drop it and recreate it, so it all works well for us.

One thing to watch out for is something I discovered from my testing:
the BDP 2.5 connection pool ignores the maximum connections settings.
It either opens as many as it needs, or maxes out at the default, I
can't remember which.

Mike

[Wayne Niddery [TeamB]]

Mike Chamberlain wrote:
>
> One thing to watch out for is something I discovered from my testing:
> the BDP 2.5 connection pool ignores the maximum connections settings.
> It either opens as many as it needs, or maxes out at the default, I
> can't remember which.

Search the groups on "Grow On Demand". I can't remember which way it is
offhand, but you either need to remove or add spaces to whatever is
generated in the connection string.

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"Light is faster than sound, which is why some folks appear bright
before they speak."

[Nevena Komarova]

Thanks for your reply, Mike.

The only reason I want to close the connections in the pool is that
Interbase keeps an open transaction for each connection and leaving
transactions open for a long time will affect server performance.

[Wayne Niddery [TeamB]]

Nevena Komarova" <"nkomarova at hotmail dot com wrote:
>
> The only reason I want to close the connections in the pool is that
> Interbase keeps an open transaction for each connection and leaving
> transactions open for a long time will affect server performance.

Are you sure about that? That would definitely be a bug. In my experience
though, any transaction left open would be a result of application code. A
connection itself does not require a transaction.

Do you have a QC# for this?

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)

"The purpose of morality is to teach you, not to suffer and die, but to
enjoy yourself and live." - Ayn Rand

[Mikey C]

Nevena Komarova wrote:
> Thanks for your reply, Mike.
>
> The only reason I want to close the connections in the pool is that
> Interbase keeps an open transaction for each connection and leaving
> transactions open for a long time will affect server performance.

This is not true. You need to make sure that you commit or rollback all
transactions and close your command objects (very important). As long
as you do both of these, transactions will not be left open.

I have developed a data access layer in C# for use with the BDP 2.5 that
encapsulates best practices, eliminating the need for the programmer to
manage connections or ensure objects are committed / closed. It
operates in a similar fashion to Data Access Block from the Microsoft
Patterns and Practices Enterprise Library. It's been in production in
our business for 6 months now so is stable. I will be posting it soon
to The Code Project, so I'll let you know when it is up.

Mike

[Nevena Komarova]

After closing the connection I've noticed in the Interbase Performance
Monitor that the oldest active transaction is not equal to the next
transaction, that's way I suspect that there is an open transaction left
for the connection. I did a few very basic tests, one of them is:

c_sel := 'select customername from customer '
+ 'where (customerid='''+customerid+''') ';
bdpcon.Open;
bdpcmd := BDPCommand.Create(c_sel, bdpcon);
try
bdpdr := bdpcmd.ExecuteReader;
if bdpdr.read then result := bdpdr.GetString(0);
finally
bdpdr.close;
bdpcmd.Close;
bdpcon.Close;
end;

I haven't logged a bug report yet.