QueryEntity Failing when passing 1000 ids on column using IN operator

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?

Getting so many rows based on an ids is normally sometthing I would advise against doing though it does seem like you've defined some parameters to prevent it getting out of control.

Have you considered using the new JDBC Connected System?

docs.appian.com/.../custom-jdbc-connected-system.html

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?

Our current setup is as follow:

1. We have two separate groups which upload data in our system to create issues.

2. On the basis of loggedInUser group, we are getting the ids associated and then passing it to queryentity to get only those set of data from view to display in Read-Only grid. In View we have just 25 columns.

3. As soon as user select a filter in grid, it is failing with above mentioned error.

So do you suggest anything here which can help me to overcome this issue.

Oh yes! Use record level security!

docs.appian.com/.../record-level-security.html

we can't have record type as those are VIEW.

Our Use case involves 3-4 filters applied as per logged in member entitlements and some additional business filter which can be passed via Appian.

Here Query entity expression is not taking much time and giving result very quickly but same Query entity expression is causing mentioned error while using it via Interface.