Introduction

Due to the database structure of Exact Globe, the retrieval of transaction totals requires the system to totalize all transactions. Over time, as your database becomes bigger and filled with transactions, the retrieval of balance totals becomes slower. Faster retrievals are therefore necessary to help you work more efficiently with the system. 

Enhancements have been made to improve the performance of retrieving balance totals whereby you are required to enable a new Use balance tables to improve performance setting. 

Menu path

What has been changed

The following changes have been implemented to improve the performance in retrieving data through the use of new database tables. The changes are applicable only to actual transaction data and the budget or planning data will not be supported.

Enabling the new balance tables

• To improve the performance of retrieving data, you are required to enable a new Use balance tables to improve performance check box, which is accessible via  a new Transaction totals button added to the Table list screen. 
  
  
  
  
• The Transaction totals screen will be displayed when you click the Transaction totals button. By default, the Use balance tables to improve performance check box is selected.
  
  
  
  
• When this check box is selected, the system will use the balance tables to improve the performance for retrieval of the following:
  
  
  1. Opening balance from cash/bank journals.
  2. Opening balance totals from G/L cards.
     Note: When the Reversal entry check box under the Entry section in General ledger settings is selected, the retrieval of balance totals will not be based on the balance table.
  3. Totals from debtor/creditor cards.
  4. Stock totals (free and available stock).   
     
     The balance calculation is only applicable to certain scenarios as listed below.
     
     

     Application	Applicable	Not applicable
     Cash/Bank journal entry	Creating new entries in all situations. Editing entries for all cases except when the "BankReconciliationLogic" setting is enabled.	Editing entries when the "BankReconciliationLogic" setting is enabled. This is because the opening balance will be calculated based on the entry number which is not available in the balance table.
     General ledger card	Card option. Linked/unlinked filter. Unprocessed filter. Date range filter (date, reporting date, period). Show: Closing entries filter. Show: Allocated cost transaction filter. Security level (remind count). Hidden count. Hidden amount. Multi currencies.	Fulfillment date range filter. Transaction filter. Type filter. Any criteria inside Filter button.
     Debtor card/Creditor card	Card option. Unprocessed filter. Date range filter (date, reporting date, period). Security level (reminder count). Hidden count. Hidden amount. Multi currencies.	Fulfillment date range filter. Transaction filter. Any criteria inside Filter button.
     Stock position reports	Warehouse filter. Reference date filter. Note: All existing filtering is supported.

• Besides the four main areas above, the following will also experience performance improvements:
  
  
  1. When you click Parts list in the Stock check screen from sales order entry. The item used in sales order entry must be a BOM item with its “part” item still available in stock.
  2. When you click Change in the overview of sales order entry to go to the Fulfillment date screen.
  3. When you click Unassign in the Maintain warehouses – Assign screen. 
     

• When the Use balance tables to improve performance check box is selected, an SQL agent task is automatically added to your SQL agent to rebuild the new balance tables. The SQL agent task will be added only if the user who update Exact Globe to product update 395 is also having the administrator rights to the MS SQL server.
  
  The rebuild process is scheduled to run at 2.00 am local time. The rebuild process takes approximately 15 minutes and the database is not accessible during the rebuild process. However, the actual time taken for the rebuild process is dependant on the size of the database. 
  
  Note: If you have multiple databases in the same Microsoft SQL server, you are required to reschedule the task for each database to prevent overloading the server as the rebuild process for each database is scheduled at the same time.  
  
  
• When you open Exact Globe, checking will be performed and  the following warning messages will be displayed if:
  
  
  1. You have the Administrator role, the rights to enable SQL agent,  and the SQL agent is disabled.
     
     
     
     
  2. You have the Administrator role, the rights to enable the SQL agent task, and the SQL agent task is disabled.
     
     

• You can rebuild the balance tables manually using the Rebuild button in the Transaction totals screen.

• You can view the information of the rebuild process with the Log button in the Transaction totals screen.   

Note: If you are restoring Exact Globe database in product update 395, it is advisable to manually rebuild the balance tables.

Generating stock positions report

• A new Qty. to be rcvd./ Quantity to be delivered check box is added at Include under the Display section at Inventory/Reports/Stock positions. By default, the Qty. to be rcvd./ Quantity to be delivered check box will be selected.
  
  
  
  
• For optimal performance in generating stock positions report, clear this check box. When this check box is cleared, the Qty. to be rcvd., Quantity to be delivered, and Available columns in the Stock positions screen will be removed.
  
• When you view the stock positions for all warehouses using the Totals only button, the Quantity on order and Qty. to be rcvd. columns will be removed.
  
  

Related document

• Overview of Stock Positions
• Stock Positions Report
• Table List
• How to: Start SQL Server Agent (SQL Server Configuration Manager)