Optimizing Performance

It is important that you optimise the performance of your data loads while processing a large volume of data. To do this, you need to configure the following: 

1. Change isolation level for backend and log database
   1. Go to .../<ApdeptiaInstallFolder>/AdeptiaServer/ServerKernel/etc folder.
   2. Open server-configure.properties in the Text Editor.

   3. Set the isolation level to "2" for both backend and log database.
      For backend database

      <Property comment="Transaction Isolation Level for the connections in the pool. Specify 1 for READ UNCOMMITTED and 2 
      for READ COMMITTED." dynamic="no" name="abpm.jdo.isolation.level">2</Property>

      For log database

      <Property comment="Transaction Isolation Level for the connections in the pool. Specify 1 for READ UNCOMMITTED and 2 
      for READ COMMITTED." dynamic="no" name="abpm.logdb.isolation.level">2</Property>

   4. Save the file.

2. Apply Indexes
   Depending on the database you are using, run the following queries to apply indexes on the database. 

   SQL Server database

   On backend database

   CREATE  INDEX [IX_AU_PROCESSQUEUE_AUSTATUS] ON [AU_PROCESSQUEUE]([AU_STATUS]) ON [PRIMARY];
   CREATE  INDEX [IX_AU_PROCESSQUEUE_AUNODENAME] ON [AU_PROCESSQUEUE]([AU_NODE_NAME]) ON [PRIMARY];
   CREATE  INDEX [IX_AU_PROCESSQUEUE_AUQUEUETIME] ON [AU_PROCESSQUEUE]([AU_QUEUE_TIME]) ON [PRIMARY];

   On log database

   CREATE  INDEX [IX_AU_GROUPDATA_REFERENCEID] ON [AU_GROUPDATA]([REFERENCEID]) ON [PRIMARY];
   CREATE  INDEX [IX_AU_GROUPDATA_ACKSTATUS] ON [AU_GROUPDATA]([ACKSTATUS]) ON [PRIMARY];
   CREATE  INDEX [IX_AU_GROUPDATA_DISPLAYSTATUS] ON [AU_GROUPDATA]([DISPLAYSTATUS]) ON [PRIMARY];

   MySQL database
   
   On backend database

   CREATE  INDEX IX_AU_GROUPDATA_REFERENCEID ON AU_GROUPDATA(REFERENCEID);
   CREATE  INDEX IX_AU_GROUPDATA_ACKSTATUS ON AU_GROUPDATA(ACKSTATUS);
   CREATE  INDEX IX_AU_GROUPDATA_DISPLAYSTATUS ON AU_GROUPDATA(DISPLAYSTATUS);

   On log database

   CREATE INDEX IX_AU_PROCESSQUEUE_STATUS_JOB  ON AU_PROCESSQUEUE (AU_STATUS,  AU_JOB_ID);
   CREATE INDEX IX_AU_PROCESSQUEUE_STATUS_NODE  ON AU_PROCESSQUEUE (AU_STATUS,  AU_NODE_NAME);

3. Enable READ_COMMITTED_SNAPSHOT on the SQL Server log database
   Run the following queries on the SQL Server log database to enable READ_COMMITTED_SNAPSHOT. Before running the queries, ensure that no process flow or any query is running.

   ALTER DATABASE LogDBName SET allow_snapshot_isolation ON
   ALTER DATABASE LogDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   ALTER DATABASE LogDBName SET read_committed_snapshot ON
   ALTER DATABASE LogDBName SET MULTI_USER
    
   where, 
         LogDBName is the name of Log database.

   You can execute the below query to verify that READ_COMMITTED_SNAPSHOT is enabled.

   SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc, snapshot_isolation_state 
   FROM sys.databases WHERE name='LogDBName'

   Contact your System Administrator in case you are unable to run the queries.

   .