GUI and database connection strategy

As a general answer, you should use a connection pool if possible and ensure that's shut down

>>Also, my db server (firebird 1.5) uses additional memory each time an update statement is executed.

If you're adding to a batch, a buffer would have to be increased in size. If you don't want that to happen, you'd have to apply each update immediately probably, but then performance would take a hit

Firebird is the open-source db based on Interbase:  http://www.ibphoenix.com/
It's client/server with daemon.  It's completely independent of my app and will typically run on a separate machine.

If possible, you should use a PreparedStatement for the update as they are intended to optimize loop-oriented queries

> As a general answer, you should use a connection pool if possible and ensure that's shut down
I only need one single connection.  Using a single connection with timeout would be equivalent to using a connection pool... am I wrong?

>>I only need one single connection.

Oh in that case, yes, you may as well keep the one connection open. Allow for abrupt termination and make sure it's closed (shutdown hook) or you may leak db resources

Thanks for the tip about the memory usage issue...
I have not tested using up all the memory myself, so I'm not quite sure why that happened.  I still need to test that.
There might be a configuration setting that tells the db to empty the buffer when it exceeds a certain size...  I'm not a db expert as you can see.

>>I only need one single connection.
It's a 2-tier application (GUI + DB).  So, I only need one single connection per GUI.  But I will have several GUI running simultaneously on different machines.
I read the JDBC driver for firebird can support about 150 simultaneous connections without problem (the theoretical limit is 1024).  150 simultaneous GUI is more than enough in my case.
Do you still think that keeping the connection open is better than using a timeout?

>> If possible, you should use a PreparedStatement for the update as they are intended to optimize loop-oriented queries
Currently, I don't use PreparedStatement at all.  The PreparedStatement vs regular statement seems to be controversial.
According to articles, the difference in performance switches after a certain number of uses of the PreparedStatement.
Also, since i have several different GUI running, each of them will ask the db server to compile their own version of the same PreparedStatement... I don't know exactly how that works.
Anyway, for a small amount of statements, this should not make a big difference.  To keep it simple, I currently only use regular statements.
I'll probably need to move to 3-tier in a next phase, adding a java server tier that will do all the db stuff.  Then I guess using connection pooling and PreparedStatements will make a big difference.

Oh, I forgot to specify that my GUI is single-threaded.  It always waits for each sql statement/query to return before executing the next one.

> Does Firebird support stored procedures (not that i want to complicate things ;-))
I almost added that to my previous comment...  Yes it does support stored procedures.
I'm using them only to increment the record primary id's.
But here again, I might need to support other db's later on, and not all of them support stored procedures.
I wrote my abstraction layer to be as plain as possible.  Performance is not a main issue for the time beeing.

Actually, it's more than a database abstraction layer, it's a complete data access abstraction layer.
It does not absolutely require a database...  The data could come from anywhere.
The GUI uses methods of an interface to fetch and save all data.
Then I implement the interface methods to interact with the data repository (database in this case).
If I want to use a different data repository, I just need to write a new implementation of the interface.
Same thing for optimization, I can have different implementations for different optimizations.
I could write 2 different implementations, one that closes the connection after a timeout and one that leaves it open at all times and dynamically load one or the other based on the user's choice (name of the implementation class in a properties file for instance).

Currently my GUI uses one single connection and only closes it when it exits.
Then I heard there was this memory problem...  And found out I could solve it by closing the connection.
So I read a little bit about connection pooling, so I thought I might do something similar...
Using the timeout, would have solved my memory issue in some cases by closing the connection.  But it surely does not cover all the possible cases.

I agree, using a timeout does not make much sense in my case because I know I will always need one and only one connection.  Connections have timeouts in pools because you never know exactly howmany simultaneous connections will be need.  When there is no available connection, a new one is created... this implies that some connections have to be closed when too many are in the pool (hence the timeout).

I will need to solve my problem the proper way...  Which means reading the firebird docs :-(.  Firebird is not the most popular database (even if it's a very nice for mid-size database), so it's sometimes hard to find good support.
EE has an Interbase TA (http://www.experts-exchange.com/Databases/Interbase/).  I'll look for help over there if i need.

I'll wait for your last comment and close the question if nothing new comes up.
Thanks for your help.

Certainly it's worth checking the query buffer size issue, as if, memory is a higher issue than performance, you should probably keep that buffer size as small as possible.

:-)