Welcome to the Exact Product Blog

On this blog we will cover stories from all around the Exact ecosystem.

Improving the performance of Exact Globe

Written by Michiel Dorjee on . Posted in Exact Globe

In an earlier post on my personal blog, I wrote about improving the performance of Exact Globe. A discussion was initiated in the Exact Globe user group on LinkedIn, resulting in a few webinars with customers, implementation partners and contacts at Exact resellers to exchange our thoughts on the topic. In this post I want to give a first update on what has been going on since then, what the outcome is and how organizations and the people that use Exact Globe, could benefit from all this.

The approach taken by Exact’s product organization to improve the performance and scalability of Exact Globe has been divided into 3 parts:

  1. Reduce the number of records in the main transaction tables. More specifically, do a clean-up of MRP records that are generated during logistic, project or production processes.
  2. Introduce tables that capture financial balances. So we’re removing the need to retrieve totals by doing a sum across all records in Exact Globe’s financial transaction table (a.k.a. GBKMUT)
  3. Build an archiving function that enable organizations to remove all transactions related to old financial years

So what can you expect in the next product update of Exact Globe?

We’re currently in the final stages of the development of product update 395. Topic 1) and 2) will be a part of that product update; topic 3) is still under analysis. A little more detail on the improvements we made:

1) Reduce the number of records in the main transaction tables

Logistic processes in Exact Globe generate MRP planning records. Examples of logistic processes are sales orders, blanket sales orders, return to merchant authorization (RMA) orders, purchase orders, blanket purchase orders, return to vendor (RTV) orders, interbranch transfers and quotations.

Over the years, as customers’ databases become larger and filled with logistic transactions, the database actions on these MRP records becomes slower. Purpose of the adjustments that have been made in product update 395, is to improve the performance of logistic and MRP related data, by removing obsolete MRP records from the Exact Globe database.

After the completion of the logistic processes over a certain period of time, the historical records (i.e. the completed sales or purchase orders) might be obsolete and no longer required. In order to delete these obsolete logistic and MRP planning records (and hence improve the performance of the administration), a tool dedicated for this function is available as per product update 395.

Functionally, this is done via a wizard that will explain the user about the consequences and what pre-conditions have been set. Reason for setting some preconditions and limitations, is that we didn’t want to build solutions for every possible scenario and thereby delay delivery for the majority of our customers. We expect to get quite a bit of customer feedback and we intend to take that into consideration in next iterations of the cleanup application. That’s why you’ll see that i.e. the clean-up will not work in combination with production orders. See the screenshot below for an impression of how it looks like:

Logistic MRP cleanup, 395

2)      Introduce tables that capture financial balances.

In order to speed up the retrieval of transactions totals, new tables have been introduced to capture

a)      Total actual financial transactions per ledger

b)      Cash/bank balance

c)       Actual  stock per item

d)      Total accounts receivable per debtor

e)      Total accounts payable per supplier

Applications that benefit from these improvements are AR/AP/GL cards, cash/bank entry (where cash/bank balance is retrieved in the header) and viewing your stock positions report.

These tables are populated by making use of SQL triggers. This means that consistency and correctness of the values in the table, is ensured on a database level  outside of the Exact Globe applications. In order to keep the size of these new balance tables small and fast, a background process (a SQL agent task) will be automatically enabled on your SQL server and scheduled to compress the records in these new tables every night at 2am.

We’ve also implemented a fallback scenario J. Just in case anything would go wrong, the administrator can simply disable the use of these balance tables and Exact Globe will retrieve financial totals in the same way as it does in 394 and before.

A nice side-effect of having these balance tables is that organizations who have their own customized reports that show any of the 5 totals listed above, can rebuild these reports on the new tables, making retrieval of the report faster.

Interested?

In a next blogpost I will go into some more detail about the balance tables concept and the performance results we’ve seen in our labs.  Meanwhile, in case you’re interested to be an early adaptor of these improvements and give feedback to Exact, please contact your local Exact office to participate in the Controlled release of 395. We expect this controlled release to start mid September.

Tags: , , , ,

Comments (10)

  • Andre Speek

    |

    Sounds impressive and interesting. Still some hesitations for point 2. In a One-X or Single Database scenario’s there is already the Balance table which is used by Synergy. So I’m going to check if this new Balance table for Globe is maybe this one or a newly introduced table.

    I hope that the existing Balance table will be used. If not, I hope the Synergy reports and Globe reports that are each other counterparts are very well verified to have the same results. Similar reporting on different data sources (like we had on the Outstanding Amounts years ago, where Synergy used GBKMUT and Globe used BankTransactiont) is always very tricky since the one building the reports is not the one that fills the table.

    And wow… SQL triggers…? I have to adjust my Query toolkit. I have one that can find all triggers that are not on the EBC tables since those are (correction… were) certainly not created by Exact and might very well be the source if unexplainable database errors occurs.

    Hey… let’s even be more careful out there… 😀

    Cheers,

    Andre

    Reply

  • Ronald Voets

    |

    Hi Andre,

    Thanks for the comment. The new tables that keep the transaction totals, are only implemented for Exact Globe. For Exact Synergy nothing changes. We thought about re-using the Balance table as used by Exact Synergy, but this table has too much ‘grouping’ (so too much details) which we don’t need for Globe. So if we would use the Balance table from Synergy also in Globe, then the table would become too large (too many records) and not fast enough.

    I share your concern that two different data sources can lead to differences. It’s THE reason why we could only do this project if we succesfully implemented it with SQL triggers. We’re not going to rely on each individual application updating two tables instead of one.

    Reply

  • A User

    |

    Hi Ronald

    Well this does sound very interesting!

    SQL Triggers on Exact DB is this a first? Are you able to say which tables will have these triggers?

    Best regards

    Reply

  • Ronald Voets

    |

    Hi ‘user’,
    it’s a very interesting concept indeed and we’re quite excited about the results in our labs so far.
    it’s the first time we deploy SQL triggers on Exact Globe. In the past sometimes customer-specific triggers may have neen deployed by consultants, b ut it’s the first time we ship this as a standard part of Exact Globe.
    The table on which the trigger runs is our main transaction table GBKMUT. In a post next week, i will go into a bit more detail on the triggers themselves and the preliminary results. Stay tuned 😉

    Reply

  • Hans van Essen

    |

    Why are you using triggers?
    SQL Server supports indexed views wich performs *MUCH* better, is less error prone and is also much simpler to implement. And yes, you can also use indexed views on SQL Server standard and desktop editions.

    Reply

  • Ronald Voets

    |

    Hi Hans,
    good comment. We considered indexed views, but decided to use SQL triggers since that resulted in better performance improvements. If you have a database with a million transaction records and you create an indexed view for i.e. actual stock on it, then SQL needs to recalculate that view with every stock update/insert/deletion action on the transaction table.
    We’ve splitted the SQL triggers into 3 parts; one for updates, one for inserts and one for deletions. That means for the example of a stock totals table, the trigger only adds/subtracts whatever stock is received/fulfilled and doesn’t need to recalculate the new total stock actuals via a an expensive/slow query on the GBKMUT table (like in a situation with indexed.views).

    Reply

  • Nilesh Jain

    |

    Hi Roland,
    This indeed would be a good alternative to retrieve balances and write customized reports which could be faster than before.

    But how about updating of new tables based on triggers. In a situation where more than 200+ users are working on sales/purchase process in a single Globe databse, won’t it block the table thus users would feel the performance bottleneck during transaction entry/process. I have faced similiar situation in an integrated scenario (Globe/Synergy) where balance table of Synergy was being updated dynamically (based on transaction activities in Globe) and was locked out due to heavy usage. I hope we don’t miss out on this as we must keep the large users sites in consideration.

    Reply

  • Heiko van der Vlugt

    |

    Hi Ronald,

    The comment of Hans is true, we did use Indexed views in our Easy Access time and with great success, even with millions of transactions (database >400 GB and performing great).

    I assume the triggers are the first steps to an improved database model?

    Reply

  • Ronald Voets

    |

    @Nilesh,

    To mitigate the risk of locking by the SQL triggers, we’re oinly doing inserts and no updates. There’s a background job on the SQL server installed, by default running at 2am, to merge the records in the new transaction total tables.

    Also good to mention, is that users can turn the usage of the new transaction total tables OFF as well. Once it’s turned OFF, Exact Globe will behave and perform as it does today. That’s another step we took to mitigate the risk of the implementation.

    @Heiko We’re constantly seeking for ways to improve the performance of Exact Globe. The triggers are 1 step to improve the performance of Exact Globe, removing historic MRP records is another one implemented in 395, improved index structure on some master data (based on customer database statistics) is a third one implemented in 395. So it’s not “just ” the database model we focus on :-)

    Reply

  • Matthew Bather

    |

    It is not often I am surprised by software. Being in Product Management sort of removes any of the unknown excitement of “playing” with new software. Well, upon updating my local system to the latest cut of Update 395 (currently in CR), I had one of those Wow moments! I know Globe intimately, having been responsible for many of the designs in logistics and manufacturing, so I had an idea as to where I might check to see if the performance had actually improved. Well, the term “blazing” comes nowhere close to what I experienced. Brilliant and crisp. One might liken the increase in performance to upgrading from a tube based TV set to a High Definition 1024p, 52″ flatscreen TV.

    Admittedly, my database is nothing significant, but the improvements will definately be relative, and remain significant.

    Anyone still thinking about whether to look at 395 should stop thinking and participate in the CR. Put the product in a “sand-box”, test it, shake it and make your own determination. We can debate the technology implications of this all day long, each with differing opinons based on personal experience. At the end of the day, the real issue at hand is the increase in performance. Would you prefer to fly from London to Singapore on a Boeing 747 or would you prefer the Concorde?

    Reply

Leave a comment