Modern CTRM systems produce vast amounts of data. Over time this becomes a serious performance issue for customers, and one that is not easily solved. Historically, some CTRM companies have only provided rough guidelines on database maintenance. This leaves customers to try and solve these problems on their own utilizing custom scripting or even data warehousing. All the while end users of the CTRM application become increasingly unhappy with painfully slow performance.

There are three key solutions that can be implemented to help keep CTRM database sizes under control and make system performance as optimal as it can be: archiving, purging, and database maintenance.

Archiving

Over time, certain types of data in a CTRM system is no longer needed in the live system, but due to audit controls or other internal controls the data is required to be retained. Moving this data out of the main database into an archive database can drastically improve performance.

The main areas to consider related to archiving are around audit history, pricing data, and valuation data. These three areas of modern CTRM systems house millions if not billions of records. Poor SQL queries, indexing, and other inefficiencies coupled with this amount of data can lead to serious performance issues.

By archiving the data, it can still be viewed by the customer in the CTRM system, and in some cases the data can even be moved back into the main database if required.

Purging

Some data in a CTRM database can be purged after a period of time. The difference between purging and archiving is that once data is purged, it is removed from the database completely and cannot be retrieved. Data eligible for purging could include error logging tables that become outdated or irrelevant after the error has been resolved. Typically, internal controls and audit dictate the duration of time data is required to be kept before purging. In some cases, the data is moved to the archive first, and then purged from there after a certain amount of time. Purging has the same result as archiving in that database size is reduced, which leads to more performant database access by the CTRM system.

Database Maintenance

Database maintenance is often overlooked for most CTRM databases. Most CTRM vendors rely on the customers DBAs to do this with limited best practices if any. The result of this again appears to the end users of the CTRM system as performance issues in their day to day tasks. Some typical database maintenance tasks that should be performed regularly are:

• Statistics Updates
• Index Rebuild/Reorganize
• Performance Monitoring

The addition of indexes is also something that can drastically improve performance. An analysis of slow-performing functions of the system, along with performance and execution plan information from the database system, can shine a light on new indexes to add. For one customer that was experiencing extremely slow query times of more than 10 minutes, an analysis and several index changes improved the query times to under 1 minute.

Performance issues in CTRM systems can often be hard to pinpoint. By controlling the amount of data in the system through archiving and purging, along with recurring database maintenance, you can make the database, a typical bottleneck, as high performing as possible.

As with many tasks in the energy technology space, the challenges are in the details and the unexpected. These systems are not intuitive so before you leap, reach out!