In this chapter, we will cover the following topics:
- Cleaning up the general ledger data
- Cleaning up the procurement and sourcing data
- Cleaning up the sales and marketing data
- Cleaning up the inventory and warehouse management data
- Cleaning up the old calendar dates
- Cleaning up notifications
- Reindexing tables or the whole database
- Compressing the database tables
- Synchronizing the database
- Truncating a table
- Running a consistency check
- Optimizing the model store
The recipes in this chapter can be used to guide you through the various performance-related tasks that are normally not executed until a performance issue arises; for example, when the posting sales order documents take progressively longer to execute.
If we have a set of routines that we can perform (or better still, automate), it will reduce our administration overheads and help maintain a good user experience when it comes to the system's performance.
Transactions such as general ledger and stock transactions are usually created from a data set. There are two main patterns for this, which are as follows:
- Journals: This is the pattern where the user will create a line for each item being counted. This is then posted creating the necessary stock transactions. For example, a stock counting journal).
- Document posting forms: When a sales order invoice is posted, the user will fill in some information, for example, adjusting the invoiced quantity that is then used to post the various invoice transactions.
The data used during document posting is often of little value; journals may hold important information for a period of time.
These tables will eventually grow to a point where performance can be affected and should be considered for cleaning up, that is, deletion.
Each module has one or more clean-up routines, and each may require a different retention policy. This decision is best left with the key user in that area. This tends to end up with a debate as there is often a fear attached to remove data from a system, which is understandable while cleaning up journals, but this fear is less when we are deleting data that no one knew was there. This is the case with most of the clean-up routines.
Each clean-up routine has the ability to add data to a batch queue, and for the most part this is recommended. Where you can alter the query to include the transactions being processed, you can use the relative date function, for example, ((lessThanDate(-30)), if you wish to delete data older than a period of a few days or months.