Hi,

I am using Firebird 1.5.3. I have a database that is less than 100
megabytes. I heard disable the sweeping may boost the performance.
Thus, I try to set the sweeping interval to 0 and start using the
database in the following manners:

When performance a lengthy stock costing calculation on the database,
the performance is acceptable on the first time. But perform the same
calculation again for the second time, the performance degrade
dramatically. So does 3rd and 4th times:

First time: 368 seconds
Second time: 989 seconds
Third time: 1013 seconds
Fourth time: 1060 seconds

The following is the final gstat statistics. The "Oldest Transaction"
was below 1000 on the first round and increased to 4705 on the last round.

Database header page information:
Flags 0
Checksum 12345
Generation 5228
Page size 4096
ODS version 10.1
Oldest transaction 4704
Oldest active 4705
Oldest snapshot 4705
Next transaction 5221
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Mar 3, 2006 21:50:00
Attributes force write

Variable header data:
Sweep interval: 0
*END*


The costing calculation perform "start transaction" and "Commit
Transaction" for each stock item calculated. There are around 300
stock items. Each transaction involve avarage of 1000 database update
operations (DELETE/INSERT).

My question is the Next transaction seems not too far from the OAT
(below 20000). Why does the performance degrade so much on the second
round? If I restore and backup the database, the performance will
back to normal.. Could anyone advice on how to proper use the manual
sweeping?

--
Best regards,
Chau Chee Yang

E Stream Software Sdn Bhd
URL: www.sql.com.my
SQL Financial Accounting