SQL Profiling 2.0.3: General Notes on the Versions 1.x.x and 2.0.3

What’s New with Version 2.0.3

The following changes were made with version 2.0.3:

• The logging of steps can be configured for the following states:

  • No logging of steps

  • Only steps that have ended with an error or a negative state are logged.

  • All steps are logged. This corresponds to the behavior of version 1.x.x.

The logging of steps is a global setting and cannot be defined individually for specific processes.

• The table structure has been changed to achieve better performance.

  • Indices have been analyzed and defined.

The indices have been optimized for use in the Process Monitor App.

If queries are made independently against the database, the performance may vary. In this case, you may have to analyze the indices and set them yourself.

Limiting all queries via OFFSET...LIMIT... Is recommended.

• It is recommended to use Liquibase for the datatable administration. In exceptional cases, Liquibase can be deactivated. If Liquibase is deactivated, you must create and manage the required tables in the database yourself.

It is not recommended to deactivate Liquibase in a production environment.

• The way how process logs are deleted can be configured. You can choose between keeping logs of crashed processes and only deleting logs of successfully executed processes or to delete all the process logs without any respect to their state.

Deleting process logs without distinction between “crashed” and “not crashed” is faster than with a distinction between them. It is recommended to use the faster way in environments with high load.

General Notes on the Versions 1.x.x and 2.0.3

Below there are some general considerations that might help you when using SQL Profiling 2.0.3:

The following database versions are supported:

• MSSQL from version 2019

• PostgreSQL from version 14

Oracle databases are not supported with the version 2.0.3 of SQL Profiling.

• The SQL Profiling 2.0.0 is not compatible with the table structure of the SQL Profiling 1.x.x. See Table Structure in the Database.

• For the update to SQL Profiling 2.0.3, we recommend to delete all old profiling data from SQL Profiling 1.x.x and start with a new empty database.
  The DatabaseChangelog und DatabaseChangeloglock tables should no longer exist and all entries related to SQL Profiling should be removed from them.

• There is no migration step between 2.0.3 and 1.x.x. If data from the SQL Profiling 1.x.x must be kept for any reasons, a manual migration must be developed and executed. Please contact our customer support for this.

• If there are performance issues with the SQL Profiling, first try to disable the full step logging. If this is not possible, another solution is necessary. Please contact our customer support for this.

• If custom filters are used via the Process Monitoring Adapter, a customer specific index optimization should be done. Otherwise, the queries could be slow, since the performance heavily relies on suitable indexes.

• If custom filters are used via the Process Monitoring Adapter, the user is encouraged to use paging.

• Filtering for time windows will get slower, as the amount of data will grow. Usually, the ProcessBefore/ProcessAfter tables are used to do time span filtering. However, the amount of data in those tables is affecting the performance. In most of the use cases, those tables would not have that much data as the process step tables, so this should not be a major issue in most of the production scenarios.

• If the automatic detection of crashed processes at startup is disabled, you as customer should define how crashed processes are recognized. Otherwise crashed processes will be still shown as “running”. Please contact our customer support in case of questions.

• Simultaneous deletion of database entries (cyclical automatic deletion through SQL Profiling) during a high volume of profiling entries is not recommended.

• The performance results of this documentation cannot be applied to a Oracle database. At time of writing this documentation, performance tests on oracle were not finished. Based on the conclusions we draw from the executed performance test of Oracle, there may be a upcoming patch version of this plugin.