Hi All,
I have performance issue with batch processing of data,
Required to do: Need to update around 3L of records in DB.
First Try: Implemented a process where 200 records will be fetched at once and passed to subprocess which is configured for MNI. But the issue here is when we configure MNI it has a limit of 1000 instances per node. So after that it moves to pause state.
Second Try: Updated the same process without configuring MNI, but passing 200 records to the subprocess and looping it internally till 200 records are done and then back to parent process will again fetch 200 records and will pass them to subprocess. This continues till all the records in DB completes. i.e 3L records.
So here finally in the Second Try i was able to loop it successfully with out any pauses or breaks or fails after 1000 records. But the performance is too low and taking 20 hrs for 35k records to update. In this case if we have to process 3L records it will take a week or more.
Can anyone suggest on this.
Thanks
Vasu J
Discussion posts and replies are publicly visible
Stored procedure is definitely the most efficient if it works for your use case. A few other methods we use:
1) Load a multi-valued CDT and run through a single Write to Datastore node. Loading the CDT takes longer than the DS write in our case, about 20 minutes start to finish for loading, updating via script task and writing to the DB for ~45k records.
2) Outside Appian, if you have access to the database - export / collect the data to update in CSV format, import to a new DB table and update via SQL.