slow to delete records in transaction 1

[aftertaf]

Hi,

We have interbase on a lot of industrial PCs that have databases that can grow to 1,5gb

We have created a software that purges old data before running a gfix/gbak to reindex the database. It opens a transaction, deletes from 5-6 tables then closes the transaction.

On a 2.8ghz P4 it takes 3-4 seconds per transaction, and 10-15 seconds on a Celeron 850mhz.

I have increased the data page size from 4096 to 8192, but are there any other ways I can boost this performance?

any suggestions will be appreciated

Aftertaf

Sometimes it just needs a good kicking.

 

[unclejimbob]

When you say "It opens a transaction, deletes from 5-6 tables then closes the transaction." do you mean that the purge is accomplished within a single transaction or that each transaction removes a set of (possibly related) rows
from 5-6 tables and you have multiple occurences of these transactions ?

 

[aftertaf]

What i meant by that, and the way the pruge works is this:
the database stores data for each product made on the PC (production lines)

Each product has 1 data line in one table, and one or more in 4-5 other tables, related by foreign key to the primary key on the first table.

The transaction opens, deletes each data in other tables having the foreign key 'N', then deletes the row having the PK 'N' in the 'main' table'.
The transaction then closes, and another one is opened, repeating the process for rows concerning PK 'N+1' in the same tables... and so on.

We have some databases at around 300meg for which the purge works at 10000 products per hour.
And on this database that is 900mb in size, it purges around 120 products per hour (10-20 seconds per "transaction")

That's what i'm trying to fix: as the purge removes one week's worth of production and for some PCs this means 25000 products (so 25000 rows in the 'primary' table, plus 25000 x number of other rows in other tables)



Aftertaf

Sometimes it just needs a good kicking.

 

[unclejimbob]

These are just a couple of suggestions as they will need testing at your end but you could try:

1) using on delete cascade for the FK constraints Advantage/disadvantage = can be faster than your current method/sometimes this can actually be _slower_ than your current method but it is worth a try

2) putting the purge into a stored proc and call this proc from the client so that the server does all the work in one hit
Advantage/disadvantage = one transaction and will process as fast as the server can handle/will potentially take up to 100% CPU usage and may _possibly_ block other processes (depending upon which version of InterBase you are using) although this may not be a problem you if you intend running the process out of hours

As I say, these just need a whirl at your end.

hth
ujb

 

[aftertaf]

ok. thanks for that

The thing is, this method works well on most PCs & databases, so I was wondering if the re is any tweaking i could do on the database to make it perform better....

Though I'll note those ideas for if I can grab the DB and try locally

Aftertaf

Sometimes it just needs a good kicking.

 

[unclejimbob]

Yes, its a bit tricky giving a generic answer to this question, I guess the first thing (which by your comments you have already done) is be to make sure that both the database and the environment it is running under is identical to the databases and environments where there is no problem. Page buffer settings, sweep interval (if set), forced writes, type of storage and redundancy mechanisms, memory available to the process, any other apps that may interfere etc, etc

As an example of a 'gotcha' like this I was caught once with a slow performing application at one site only, turned out that the freeware anti-spyware/virus protection system they were using was 3 years out of date and they had it set (default setting I think) so that each individual packet was being assessed as it passed from the desktop to the network server, this resulted in a 5 minute response time for a list of items to be populated instead of 1 second. Upgraded the virus software to the latest version (which changed the default setting) and went back to sub-second response times. Happy days.

Good luck.

ujb