At 01:14 PM 2/08/2004 +0000, you wrote:

>The app was written in Delphi, a pascal language. It is an inventory
>and sales system. Throughought 8-12 hours time periods they run live
>shows, entering new inventory, customers, work orders, and sales
>orders. These sales orders are updated throughout the show as
>customers add to their orders and the inventory system is updated as
>items are added onto orders. As orders are taken and added to CC
>payment info is taken and preauths are recorded on the cards. After
>the show is complete every order meeting a predetermined sales
>criteria are turned into invoices, an entirely different table,
>payments are charged, and the status of the orders and invoices are
>updated throughout this procedure. They run about 1,000 orders per
>week. Every time an order is added to, or something is changed (i.e.
>shipping, contact info, discounts), the order is recalculated and
>posted to the DB. Slowness seems to occur when the DB size gets
>above 100MB!

You'll get this with long-running transactions - the engine can't collect
garbage created by updates and deletes. The database fills up with back
versions that get stuck, and memory gets exhausted as the transaction
accounting structures accumulate.

>The transaction deadlocks happen very sporradically, on
>different tables throughout the DB.

Transaction deadlocks aren't necessarily a bad thing, provided the
application handles them properly. "Deadlock" is an ugly word but it's
merely one strategy for dealing with conflicting work being performed on
the same record.

>We use the Borland Database Enigine (BDE).

This is the obvious source of the problems you describe - AutoCommit being
the main cause of long-running transactions. The BDE itself can cause
problems because its architecture wasn't designed for multi-user,
transactional environments. It was designed to drag all database systems
down to the lowest common denominator.

>I obviously don't know as muh as I should about this!!

To build a usable client/server app with the BDE, you really need to
understand the effects of detrimental Delphi things like TTables,
AutoCommit, holding live datasets in grids for hours on end, using the
wrong transaction parameters. To build a *useful* app with Firebird, you
need to ditch the BDE and use IBO or FIBPlus.

/heLen