Hello !
I am connecting to a view on an external RDBMS via a synonym present on the target system. I've configured a datasource, created a cdt and published the data store successfully.
From my local expression rule I simply am trying to query to fetch data with the last modified date > (ri!dueDate, say 05-NOV-2020). This will be a batch process and I want to fetch around 50-100 records at a time.
[While passing the ri!dueDate in a filter parameter of the a!query() with the operator as '>=' and sort by last modified date in desc, the 5 oldest records were returned (records from 2018) no matter what I tried inside the rule. So I decidedto break this down and start analyzing from scratch. ]
Here's what I get with a descending sort on the last modified date, to fetch the most recent records:
Code:
Result:
A List of variant with 5 items, returning in the correct sort order (latest records from 2020). The time taken for this fetch was 11,160 ms.
If I increase the batchSize to anything greater than 5, I get "Expression evaluation error at function a!queryEntity: An error occurred while retrieving the data."
Now, with the same code as above but without the sort and batchsize = 200, I get all the records from this view which takes about 17,598 ms.
I went through different while trying to understand this -
I've hit a blind spot with this issue. Any ideas or suggestions on what is going on here would be highly appreciated.
Thanks in advance everyone !
Discussion posts and replies are publicly visible
Is it possible that you have defined a primary key on a field that is not unique? All data store entities must have a field that is a primary key, and Appian always assumes that field is unique. If the field ends up not being unique, you can often see unexpected behavior when querying data.
just spitballing a bit here, but i'd also suspect perhaps the view itself is doing something inefficient, etc. I'd be curious to see loading times for default and sorted-by-column views on it...
Hi Peter. The primary key is defined on a field that is unique.
Hi Mike. Loading times for say 10 records for default is around 18.663 seconds and with a sorted by column is around 20.143 seconds.
When I tried running the QE rule today with the same batch size as yesterday (5), I got the above error while a batch size of 2 worked. It is very random.
Not sure if this helps but this view contains a DB Link along with multiple select queries and joins.
Typically, when we get "Expression evaluation error at function a!queryEntity: An error occurred while retrieving the data.", the problem is either bad SQL for the DB View or no Primary Key.
It's probably just timing out, which could by why you're seeing some inconsistencies. Are the times you mentioned above occurring if you try to run the query directly against the RDBMS? If so, I'd suggest looking for ways to improve the performance of your DB View before trying to troubleshoot further in Appian.
Thanks Peter. I was trying to understand why there's such a difference with sort and without sort. I seem to be having absolutely no issues (Never get an error) without sort but with sort, so much inconsistency. Unfortunately my team does not own the view so I will have to get the 'other' team to take a look at that.
Also, the times I mentioned in the comment to Mike is when I directly run the query against the RDBMS. The ones in my original post are from a!queryEntity().
Hey Bryant, I completely agree. We can rule out the primary key issue because there is one defined. Let me go the SQL route.
If you're seeing 18 - 20 second loading times when looking at the View directly in the database, then the problem is likely with your view. You should run it through some heavy QA checks to make sure it isn't doing something massively inefficient.
Make sure your SQL handles "joins" properly for cases where the child table doesn't have a row or has multiple rows for a row in the parent table.