We are working on a project wherein we pull data from an external application an

How complex is the compare? Can you do it in a complex rule?

Assuming rule, create a rule that compares and returns you the result. And execute it in a process when the system is least used. Delete or archive the process sooner based on your retention requirement. Default 7 days should be good for future debugging if needed.

Shyam, Thankyou for the reply. Comparison logic is little complex as it need to go through lot of data that need to be compared. Ideally each comparison would take 30-40 sec .I had a test run on around 300 cases and it took around 2 hours to complete. I tried to run the nodes in parallel but I couldn't figure out way to store the discrepancy data of every case into a single CDT which is used to export to excel. I wanted to reduce this time of processing.

@gopinathreddyr Prior to presenting a solution, could you please elaborate on this - what would take 30 - 40 seconds long? Is it just the expression rule or is it the entire process? And further may I also please know if you mean a process instance by the term 'case' in your query?

@sikhivahans Thank you for your response. The comparison logic is a process model. Each case created has several parameters that need to be compared (To make sure that data in Appian and External Application are in sync ).So for all these comparisons we have a process model(which internally calls sub-process for every parameter that needs to be checked for). To execute this entire process it takes around 30-40 sec. Case is a individual record in our application.So depending on the case count,I need to run this comparison process model and collect all the differences .This later would be exported to excel.

Do we have control over the external database? can we create a flag on the table to identify changes in the record.

Using with(), looping functions, query entity, you can achieve complex data processing. I suggest creating the rule(s) with the same logic as process model. You can define local variables and get append your results to it. Give it a shot.

Which Appian version are you on?

@shyamb we are on Appian 7.10 . Yes looping functions did help me to reduce the process running time. Now to run all the nodes in parallel I came up with new approach. I have created a table for this discrepancies and storing them once the comparison is done and any discrepancies found. Once we come out of the sub-process (all the cases are checked for discrepancies) I'm querying the DB and converting this to Excel. This way i could complete the whole process in 5 min when compared to Executing it one by one which took 2 hrs. Is this a good approach to follow? Any other suggestions are welcome.

@vahuwalia unfortunately we don't have control over external DB.

@gopinathreddyr To the best of my knowledge, I would like to suggest few things from the performance perspective based on which you could formulate either a new approach or you could continue with the approach devised by you recently and possibly some alterations if needed:

1. Please do bear in mind that delegating the functionality of the process completely to the complex expression rules is not a right practice. Please bear in mind that there is a limit for the execution time of the expression rule. If you write a complex expression rule that does all the work for you by replacing the process, I would like to suggest to check if the expression rule's processing time falls within the execution time limits by testing with the maximum amount of data. If it doesn't, this isn't a right idea as per my knowledge.

The documentation at https://forum.appian.com/suite/help/7.10/Appian_Health_Check.html#USRX-0032 complements the above explanation. If you still think that the expression is that complex and handles lot of calculations, I would further like to split the functionality by creating new expression rules.

Additionally I would also like to suggest to refer https://forum.appian.com/suite/help/7.10/Appian_Health_Check.html#USRX-0028 if you are employing queries in the expression or process as most of the times database read/write operations would be the culprits. In our case, writing just 1000 records into a table (flat, no relationships defined) that has 6 columns has been identified as a risk.

2. If possible try to delegate the logic to database(for example making comparisons by making use of the views etc) as much as possible. But also make sure that the database objects also performs faster executions which would need you to focus on the joins, indexes etc.

3. Never completely depend on process or completely on a single expression rule that does the entire work. Both should go hand in hand and make best use out of it by combining them. Make use of batching technique, let's say there are 1000 operations, make sure that your expression rule handles just 200(an arbitrary value, should be based on the complexity of your rule) at a time(maintain this in a constant so that you can increase the batch size if you think that operations are taking less time and also you can bring down the batch size if the batches are consuming more than the expected execution time.) and your process should be able to loop back until all the batches are processed.

4. Not sure if you are using MNI extensively in the current use case, but if so, make sure that the subprocesses are triggered by using messaging. https://forum.appian.com/suite/help/7.10/Appian_Health_Check.html#DSGN-0032 should give you an good idea.

I guess that implementing the above scenarios might delay the process completion time, but ideally their prime focus is on performance. I would like to suggest to focus on the performance rather than keeping the Appian engines busy just for faster execution of few things. Also the above design considerations will ideally let you handle any batch size and any complex operations if you control the batch size effectively. These are the things which I have learnt so far from my previous experiences as well as the learnings from the Health Check results and I guess even Appian recommends the same. If you haven't gone through the Health Check already, I would like to suggest to go through https://forum.appian.com/suite/help/7.10/Appian_Health_Check.html# and this would let you know the right way to a better extent.