🚀 go-pugleaf

Nevena Komarova wrote:
> 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

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/

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.


> Hi Nevena,
> 
> BDP has a setting called 'Connection Lifetime'.
> 
> More info can be found at the following link:
> 
> http://dn.codegear.com/article/33443
> 
> 

Nevena Komarova wrote:
> 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.
> 
> 
>> Hi Nevena,
>>
>> BDP has a setting called 'Connection Lifetime'.
>>
>> More info can be found at the following link:
>>
>> http://dn.codegear.com/article/33443
>>
>>

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

This is pretty important, and easy to skip.


-- 
Robin.

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

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;





Robin wrote:
> Are you explicitly closing your datareaders etc when you are finished 
> with them in code?
> 
> This is pretty important, and easy to skip.
> 
> 

Navena Komarova wrote:
> 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

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

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." 

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.

Mike Chamberlain wrote:
> 
> 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

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 

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

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.


Wayne Niddery [TeamB] wrote:
> 
> 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?
>