Hi All,
I am getting around 2000 ids in one local and then passing that ids to one of column in queryfilter using IN operator. But when i test the rule I get this error as:
An error occurred while retrieving the data.
So we split the ids in batches by taking 1000 ids in a set and then pass these id to queryfilter through looping. But still it failed with above error.
We have already optimized the view also and same query when we run in DB side it took just 0.170sec to bring the data. But same is not working in Appian queryentity.
Can anyone please suggest what could be the reason behind this and what all steps we can try to get this working.
Thanks in Advance..
Discussion posts and replies are publicly visible
This error is generated because either you are passing too long of an input to the DB or the DB is returning too much data to Appian (1 MB of data is the default limitation for Appian queries).
Is this an Oracle DB? There are some known limitations which could be related such as ORA-01795 maximum number of expressions in a list is 1000.
I would ask to see if there is any further information in the logs as well.
Have you tried looping with something lower such as 500 values at a time?
Generally this method you are using is not scalable when the list of IDs can grow this large and I would look to other designs to accomplish the outcome.
Yes we have Oracle DB and we also found this ORA-01795 maximum number of expressions in a list is 1000. And that is why we used that looping thing.
And we are querying data from DB with batchsize as 50 only to limit the data size and row.
Yes as per your last comment this id will surely increase in PROD so do you have any suggestion to change this design.
Just at a guess, your list of 2000 items has to come from somewhere, probably the database? If that's the case, would it make sense to create a database view, for this comparison, and then pull into Appian from that view? I would think this kind of heavy data processing should be moved to the database side, if possible.
I suppose it might be hitting some kind of time-out limit in Appian, you might want to check the logs to see if there was an error message that would give a hint as to what actually happened.
Nilesh Raman said:Yes as per your last comment this id will surely increase in PROD so do you have any suggestion to change this design.
For this we will need to know a little about your current data setup and process. Can you describe generally what is being accomplished here and how the system works?
We are passing the loggedinuser name to queryfilter to get these 2000 ids from DB. We cannot do this operation in DB side because of this dynamic value of filter we have to pass from Appian side itself. Once we have these ids we are passing it to another view which is getting failed.
Also one thing we observed that when we are doing this whole operation like getting the ids and passing to another queryentity then that rule just take 500ms but same rule when we are calling on parent Interface in gridfield then we are getting the error.
Querying two views using a parameter from Appian ..... that might be a case for a stored procedure.
Or, did you consider switching to syncronized Records?