Subject Re: Firebird performance degradation
Author adragan2
Take great care of how you handle transactions !!!

It very much matters how you wrote the client application.
It also matters if you are using BDE or IBX or IBO for the client.
Each "falvour" generates a different approch to the client application.
My experienece with BDE is that you can't make a decent interface with
it, especially to low IQ customers.
I used IBX for obvious reasons but have no experience with IBO.

Rules ( that apply to IBX ):
After every insert/update make a CommitRetaining.
When you close/open a form make a Commit for all opened transactions
Commit all opened transactions before you disconnet from the database.
Try using 2 transactions at the same time, one for "display" of data
and one for "processing"
ex : You have an invoice on the screen
Recalculate the invoice with a stored procedure on a
different transaction. Commit that transaction.
Refresh the invoice interogation with CommitReatining;

If you follow these simple rules you don't have any performance
degradation or deadlockes or many other nasy surprises.

I had a server that run for 6 months without any intervention.
The server is still running with 512 Mb RAM after 2 years with 6 users
2 invoiceing + 2 collection + 2 accounting !!

Friends of mine had a Web server with Interbase that crushed every day
They were clumbsy and never closed explicitly the transactions from
the php scripts and blamed on Interbase.

We have a proverb: "You can drive a car to where you want to go or you
can put it into a wall. It's the same car !"