You are viewing limited content. For full access, please sign in.

Discussion

Discussion

Forms and Workflow Performance Tuning

Forms Workflow
Updated March 25, 2024
posted on March 13, 2024

Hi, we implemented a business process where there is quite a few invoke workflow with Forms and Workflow passing variables back and forth, the workflow also makes quite a few database connection to the SQL server to run queries. In the UAT environment where everything is installed together except for the SQL server, this particular section of the business process takes around 19 seconds to run, but in the production environment with a more powerful VM and various LF modules are installed separately (internal forms routing service and workflow is installed on the same machine), the same section of business process took 34 seconds to run. The SQL server in production is also running SQL Always On so the workflow and Forms are connected through the AAG listener.

 

Does anyone have any advise on how we can tune the performance for Forms and Workflow, or improve the AAG connection speed to SQL server? Since from the client's perspective it doesn't really make sense for the production environment to be slower than UAT. Thanks.
replied on March 25, 2024

Please do not post here if you already opened a case with Tech Support. As Yuxuan mentioned above, there is not enough information to troubleshoot it here.
replied on March 17, 2024

Hi Yuxuan,

 

We did a side by side comparison of the time taken for each activities for one of the workflow and found that in general all the activities ran slower in production compare to UAT, so we are suspecting it is caused by the database connection or the query execution is slower in production due to the AAG listener. We did monitor the query execution time for the workflow database using SQL profiler but did not see any queries that has taken especially long to run, so we are trying to set up AAG in the DEV environment to see if AAG is really causing the issue.

 

The left is production and the right is UAT, in general production runs 10-20% slower than UAT, the amount of the data is the same.

 

Thanks
replied on March 18, 2024

It looks like the majority of the timing difference is in the final track tokens activity call. The other activities are quick, but not as quick as your other environment.

The root cause of this is definitely the workflow database.

  1. Is this a new server/database?
  2. If not, is there a significant amount of instance history in the database?
  3. It is generally not best practice to track tokens on every workflow invocation, instead if you intend to fail gracefully you can wrap your workflow in a try catch and only track tokens when errors are met
    1. overly tracking tokens will explode the size of your database and slow down any other activity like you are seeing here
replied on March 24, 2024

We are suspecting it could be the workflow database as well, this is a brand new server/database so not a lot of instance history at the moment.

 

The system is at an initial launch phase at the moment so we put the track token in the workflow in case we needed to trace any issue, not only when errors are met but on a logical level as well, as there may be some exceptional cases where workflow encountered no error but the routing was incorrect. The workflow database also has SQL TDE enabled so we are trying replicate this in the UAT environment to see if this is the main cause on the performance difference.

 

Thanks

 
replied on March 14, 2024

Hi Wai Kit Lam,

 

It is kind of hard to tell why it is slow with the overall time of that section of BP. Do you have any data for the execution time on each step? That might be helpful to see which step is slowing it down.

Also, there might be other possible factors that may affect the performance, as you mentioned AAG listener is configured for the SQL server. 

Furthermore, the production might have much larger amount of data than the UAT environment, therefore the query might be slower. You can track it in SQL server to see if that's the case.

 

Thanks
You are not allowed to follow up in this post.
Details
Related Posts
Loading ...