In a queryentity function, in an interface, I'm getting an error like "Unexpected error executing query (type: [CPVAccountsStatementTrackerDT8331], query:[queryentity expression], order by: [[Sort[FinancialInstitutionOther asc]]], filters:[(firmId = TypedValue[it=1,v=-1)])". How should I resolve this error. In one of filters, we are filtering based on -1 but this is working in some environment while failing at some.
Discussion posts and replies are publicly visible
Hi swapnilk0010 , can you share your code. So that will try to tell and resolve your issue.
a!queryEntity_22r2(
entity:cons!CP_DSE_V_STATEMENT_TRACKER,
query:a!query(
pagingInfo:a!pagingInfo(
startIndex:1,
batchSize:-1),
selection:a!querySelection(
columns:a!queryColumn(field: "financialInstitutionother",alias:"FinancialInstitutionOther",visible:true()),
logicalExpression:a!queryLogicalExpression(
ignoreFilterWithEmptyValues:true(),
filters:{a!queryFilter(
field: "firmId",
operator:"=",
value:-1)
}
)))
This error message typically points towards a timeout issue.
And ...
Yeah what happens if you don't use -1 for your batch size? In general I recommend against using -1 for batch size in almost any scenario, because it returns all of your data, which can easily run into timeouts, the circuit breaker, or just poor performance.
How can we resolve this because I am going to need all this values for a drop down?
Thanks Stefen for suggestion. Can you also suggest how we can resolve this issue? The values queries using this expression is going to be used for a drop down. So, I'm going to need it.
Sorry, to say that, but no, you don't. Putting such a large number of items in a drop down does not result in a great UX. I suggest to use a picker field instead.
And then, in that constant, I see the letter "V". Does this indicate that you try to query a database view? Views are notoriously known for bad performance. If you need to use this view, you will have to find a way to speed it up.
Agreed with Stefan - there has to be some limit, right? What if you had 1 million values - surely you wouldn't want to load all of those values into a dropdown? Usually a dropdown is good within the dozens to low hundreds - if you have more than that, either using cascading dropdowns or a picker is a better approach.
I agree with Stefan (as usual) - if you have enough rows in the View that it's causing Query Entity to time-out, a dropdown containing all of those values will be slow and nearly useless in terms of User Experience.
How many rows are in the view? Does the query work OK if you set the batchSize to (1, 10, 100, etc?) instead of -1 (at least to test whether that's the issue)? It would be at least helpful if you confirm for us when/whether the query works when different conditions are present.
Yes the query works when we put other than -1.
Then it's definitely timing out when you try to query everything.
Also, when a data set is big enough to cause this timeout, you are almost guaranteeing a miserable user experience when anyone tries to use that dropdown.
You will likely need to implement at least one of the suggestions mentioned above: either implement a 2-dropdown cascading system (where a first dropdown selects some sort of subset/category, and the second dropdown queries only those items to show), or implement a Custom Picker Field. I don't use Custom Picker Field all that much, but this is one of those times where I almost certainly would.