Hi team,
I am capturing data from process metrics report and storing to oracle db as it's business requirement.
Now I just want to check is there any inbuilt functionality of Appian which only writes the rows in table which has been not inserted in the previous run ?
if not is there any suggested way you recommend that can be followed to achieve the same ?
Discussion posts and replies are publicly visible
Hi, We have to query the data with a!queryEntity() / a!queryRecordType() from the database and check if the data is present or not. By checking that you can write only data which is not present to the database.
Actually I am using the out of box utility provided by Appian, the way it's doing is fetching all the record from the table and then comparing it with the data from report and finding unique. I think it's the same way that you mentioned ryt ? but the problem here is while fetching all the records from the table it is failing because of large volumne.
Why not use a stored procedure to insert the data. It can check for an existing row and only insert if nothing is found.
Try to create a process model which can be used as a subprocess and with the help of looping in it with the start index and batch size as 1000 (for example) fetch the data and then continue the looping process till you fetch all the data which can solve the issue which is caused due to the high volume.
If I want to do it without procedure.. one way I am thinking is to get data in batches the concern that I have with this approach is as the data will keep on growing and our database hits will be increased so that can cause an issue in future. so as an alternative what I am thinking is to create a record on that table and hit the record and get data in batches and as per my understanding it would solve multiple db hits problem.. is my understanding correct ? or do you think any caveat in this alternate solution..
Would need to know more about your requirements to give a proper solution but one way to do it could be to calculate a unique composite key or a hash from key portions of the data. This way you can rerun your inserts without inserting duplicates.