|Subject||Database performance slowdown|
We are investigating an issue with one of our systems where it gradually slows down during the course of a day.
We have our epos system that we write ourselves using Delphi XE2 and the IBX components.
We have a group of tables that are updated during the end of sale save process.
Typically this whole save process takes milliseconds, but on one particular site we see it taking several seconds and occasionally taking up to 35 seconds.
We have done some digging into the behaviour of Firebird, we are currently using v2.5, and it appears that Firebird needs to check the entire chain of back versions when reading a record from the database. Is this the cooperative Garbage Collection process running?
It seems like it would be logical for a lookup to only search back records until it finds the record that is supposed to be visible to it, but it appears to go all the way back.
This causes us a slowdown if there are a lot of back versions still active, and flags up data as corrupted if something untoward has happened to them, even if the current committed version of the record seems to be valid.
Can anyone confirm this behaviour and any way to work around it?