SQL Profiling 2.0.3: Notes on the Data Limit
There are limits of data records for SQL Profiling. These limits are however no fixed limits and there is no guarantee that the data limit can be reached on any system. The amount of supported data is highly dependent of the general power of the database management system (DBMS) and the used hardware.
Information on recommended values for the data limit and on the test environment
The limits given here are to be considered as recommendation.
The maximum recommended data limit also applies if the logging of steps has been deactivated.
The given limits are no guarantee that the stated amount of rows can be reached on any system in any environment with any use case. We reached this amount with a good query response time in our tests, but in general the performance depends on many different factors that vary from system to system.
For this reason, we recommend that you execute your own specific performance tests with respect to the amount and form of data that you want to log. Best is to test on a system that comes very close to the productive system that you will be using.
DBMS | Data limit |
|---|---|
MSSQL | 150 million data records |
PostgreSQL | 60 million data records |
The data limit can be applied to each table.
Example for MSSQL:
Database table | Data limit |
|---|---|
| 150 million data records* |
| 150 million data records* |
| 150 million data records |
| 150 million data records |
* With this amount of data, filtering for a time frame will be slow and maybe not possible anymore.
Recommendations on the Limit of Data Records
We do not recommend to put more than a few million process executions (1 process
execution corresponds to one 1 row in each table) into the ProcessBeforeEvent and ProcessAfterEvent tables to ensure a fast response time of the Process Monitor App.
Especially with time span filters, the query time rises quickly with the amount of rows in the ProcessBeforeEvent and ProcessAfterEvent tables. A few million could be around 10 to 25 million, depending on the performance of the DBMS servers.
It is possible to use data limits that exceed these values. However, the use of higher data limits is your responsibility.
Why Do the Limits for MSSQL and PostgreSQL Differ?
The difference in limits is to be attributed to the test environment. For reasons related to the nature of the test environment, PostgreSQL was tested with a smaller data set. Therefore, we can only recommend a limit for PostgreSQL as stated above.