In one of our report definition we have below parameter values under data access
Maximum elapsed time in seconds: 60
Maximum elapsed time in seconds for export: 30
We happened to see below exception while exporting the retrieved data to excel through report.
SQLState 72000, Error code 1013: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
In few threads it says, configure the value in Maximum elapsed time in seconds for export parameter to be greater than or equal to the value in Maximum elapsed time inseconds parameter. Is it mandatory/advised to have Maximum elapsed time in seconds for export >= Maximum elapsed time inseconds.
Will there be any DB sessions or performance issue by increasing Maximum elapsed time in seconds for export from 30 seconds to 60 seconds.
Optional. Specify the maximum elapsed time in seconds that the report execution is allowed to run before being interrupted. If you leave this field blank, the system enforces a default limit of 30 seconds.
If the report exceeds the maximum elapsed time, a dialog opens explaining that the report took too long to run and that the filter conditions must be more specific. A report typically runs faster by adding additional filter conditions, or by making the existing ones more constraining.
Maximum elapsed time in seconds for export:
Optional. Override the default of 30 seconds if your system generally requires more time to export a report.
So, it's not mentioned as per my research but will try to find more and share if found in this post.
Logically, maximum elapsed time in seconds for export should be greater than maximum elapsed time. Because later one is the timeout for retrieving the data from the database and mapping it into clipboard. So, in either case, whether you're trying to just retrieve the data to display in UI or export, data has to be populated on the clipboard so this elapsed time or query execution time will pass.
Now, when export to excel operation is done on report definition the data will be again manipulated by OOTB activities to create a CSV or excel file in the format so this is additional time required.
By this, we can conclude that Maximum elapsed time in seconds for export >= Maximum elapsed time inseconds.
Now let's come to your second question,
Yes, there would database performance issue when your report definition is going to have millions of record as SQL query will take time and it would be irrespective of this configuration. These settings just allow the report definition rules to run till this threshold timing.
Hope it answers your questions, kindly notify if it does by marking this post as answered for tracking purpose.
Posted: 4 years ago
Posted: 21 Feb 2018 12:42 EST
Satish Annam (SatishAnnam)
Standard Chartered Bank
Sr Technical Manager
Standard Chartered Bank
The settings Maximum elapsed time in seconds and Maximum elapsed time in seconds for export are used to control the timeout of the transaction during the execution of the report definition and export to excel of the report definition respectively.
Note: Here if you set Maximum elapsed time in seconds=30 -> if the database is not giving the result in 30 seconds then it stop the transaction(by throwing the error). This is done from the Pega side and not the Database side, so nothing to do with the DB sessions. With these settings we are controlling the timeout of the query result by throwing exception after the specified time to stop the transaction.