Solution - Responder - Responder History Schema Growth

Version 3

    Behavior

     

    Every table in the active schema (EX: RX_CALLS) has a matching History table (EX: RX_CALLS_HISTORY) table that is populated with any change to the active table. The History tables are used to generate the archive records after an incident is completely managed through Responder, or after an incident is cancelled. These History tables can grow large over time and need to be managed accordingly. Internal load testing has shown the system can be slowed down by large amounts of records in the History tables from older incidents.

     

    Cause

     

    Displayed below are settings in the SubmitRulesConfig file that pertain to these tables:

     

    2015-05-11 11_43_24-SubmitRulesConfig.xml_ - Microsoft Visual Studio (Administrator).jpg

     

    The ArchiveCancelledIncidents value determines whether incidents that are cancelled are sent over for archiving. This is not a common setting for customers but can be selected if you want all incidents sent to archive, regardless of whether they were mistakes by the users or actually recreated correctly later.

     

    The DeleteHistory value will trigger a deletion of all history records for an incident once the system has determined it has been successfully archived. Incidents in the archive lose some level of granularity when compared to the History records, but they occpuy significantly less space. Also, having this option set to true and the ArchiveCancelledIncidents value set to false will cause all history records from cancelled incidents to remain in the history tables, since they never went to the Archive schema.  There is an additional customization available through our sample code that will delete cancelled incidents from the history schema without sending them over to archive.

     

    Solution

     

    At this time, Schneider Electric recommends that these records be deleted to maintain performance. The recommended process is to flip the values above to allow the software to remove the records automatically. Customers who choose to keep the records around after the incidents have been archived may want to set up some additional maintenance and processes to handle the table records at a predetermined time. Some clients have set up automated scripts that run every few months to purge the data or move them into another schema that Responder does not see. Other clients have rebuilt the Active database during upgrades and reset the sequences on the new tables. This method will depend on the amount of Responder users and crews, as they'll need to be copied over from the older system, in addition to any active incidents during the upgrade.

     

    There is also a "Build From History" tool in Archive Explorer that can be run on some interval to clean up the history tables if they are left behind as a normal process. This tool will delete the history if the option is checked, including incidents that have already been Archived. It will also search the history tables for any missing incidents and archive them as well.  Due to limitations of x86 processes, the tool may need to be run in smaller date ranges, depending on the amount of work attempted.

     

    2015-05-11 12_15_41-Responder Archive Explorer - Shawn Leingang [NAM_SESA220610].jpg