Improve speed and stability of Controller when have large numbers company locks (XACCLOCK table)

If a customer has a complicated company structure (e.g. with many different actualities) then after they create company locks for all their periods/actualities, XACCLOCK becomes absolutely enormous

• For example, one of our customers has 22 million records (in XACCLOCK) just for the single period 2412 !

  • 825,230 are in period 2412AC

  • 32,000 are in 2412BU

  • 900,000 in 2412F1

  • and so on...

The massive XACCLOCK SQL table causes very slow read/write queries to the table. This causes problems e.g.:

(a) User's sessions hang when they try to use the menu 'change status'. The hanging can last 2-120 minutes depending on how many changes they make in there

(b) During this 'hanging' period, no new users can logon to Controller (they all get “Communication link failure” … “Timeout error [258]” errors)

=> Downtime

+++++++++++++++++

Please can you improve the product to achieve BOTH of the following:

(1) Method to delete old company locks (trim XACCLOCK) from inside the GUI (so the end user can do it - the method does not have to use SQL scripts)

(2) Method to prevent hanging/system-downtime when changing status (if there is a large XACCLOCK)

• For example, create a better SQL index or (probably a better idea) partitioning of the SQL table XACCLOCK (e.g. a separate partition for each period or period/actuality-combination)

• This would allow for faster queries (thereby not causing the hanging/timeout errors when changing status!)

+++++++++++++++++

Many thanks!