Hello,
I do not mean the "long", but the "frequent" disturbes Oracle DBAs.
Think of a general application (web, erp), it could not be just one data
operation during the application runs. So I have the question: connect
every time, or last a reasonable time (not be idle for as long as 10 hours).
It is true as you said, point 1 and 2. And it is also true that one
database may take more resources for establish than hold, while some are
opposite.
Take pgsql and mysql for example. Using short connection for 1000
web request, pgsql is much more slow than mysql. While using long
connection, they are more or less the same.
Besides performance, stable is another important thing. If the
network is OK, my pgsql long connection never broke, while mysql
sometimes just disconnect for no reason (not server timeout), and mssql.
So I would "prefer" long connection on pgsql with pool for frequent
query, but short connection on mysql (I do not trust mysql, so I do not
use pool on it).
That is my thought. So, which is prefered for Firebird?

Thanks.

Adam wrote:

>
>
> I disagree with you that the cause of unhappy DBAs is the length of
> the connections for the different engines. What causes unhappy DBAs is
> their servers grinding to a halt because of the way they are being used.
>
> 1. It takes time and resources to establish a connection.
>
> When you establish a connection (to any DBMS), a number of things
> happen. Usually, you have some form of authentication that must take
> place, and some caches that get created and resources set aside. This
> all takes time.
>
> 2. It takes resources to hold a connection open.
>
> There are often caches associated with a connection, and while the
> connection is open these resources can not be released.
>
> As Helen pointed out to you, you have different types of applications.
> An application that collects data every 30 minutes would probably be
> better off establishing connections as needed and closing them when
> done. An application that is continuously running queries would be
> foolish to use such an approach, because the overhead in connecting
> would slow the server.
>
> I would suggest the 'better' approach is to use a connection pool.
> Your data abstraction layer manages the connections. When you need to
> run a query, you simply grab an existing connection out of the pool.
> Once you have finished, return the connection to the pool. If the pool
> is empty when you need a connection, a new one is automatically
> established and added to the pool. (In fact you can be clever here and
> start establishing connections when the pool drops below a few
> connections). This is a reasonable compromise. You don't have people
> struggling to use the database under the load of unused but still open
> connections. The downside of course is that you can not rely so much
> on user security implemented within the database based on a generic
> connection user.
>
> http://en.wikipedia.org/wiki/Connection_pool
> <http://en.wikipedia.org/wiki/Connection_pool>
>
> Adam
>