Welcome to the Exact Product Blog

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

Posts Tagged ‘SQL trigger’

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.


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.