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">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">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 ON [PRIMARY]; CREATE INDEX [IX_AU_PROCESSQUEUE_AUNODENAME] ON AU_PROCESSQUEUE ON [PRIMARY]; CREATE INDEX [IX_AU_PROCESSQUEUE_AUQUEUETIME] ON AU_PROCESSQUEUE ON [PRIMARY];

   On log database

   CREATE INDEX [IX_AU_GROUPDATA_REFERENCEID] ON AU_GROUPDATA ON [PRIMARY]; CREATE INDEX [IX_AU_GROUPDATA_ACKSTATUS] ON AU_GROUPDATA ON [PRIMARY]; CREATE INDEX [IX_AU_GROUPDATA_DISPLAYSTATUS] ON AU_GROUPDATA 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.

   .