Dear Team,
Could someone help me to understand the below issue please.
I am trying to fetch the total count of the records from DB view. When I give fetchTotalCount=true it's throwing an error 'Expression evaluation error at function a!queryEntity [line 2]: An error occurred while retrieving the data. Details: Unexpected error executing query'. If I change the fetchTotalCount=false, it's working fine. Sample code is below:
a!queryEntity( entity: cons!DB_VIEWS_DSE, query: a!query( selection: a!querySelection( columns: a!queryColumn( field: "id" ) ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 1, sort: { a!sortInfo(field: "id", ascending: false()) } ) ), fetchtotalcount: true )
Discussion posts and replies are publicly visible
It seems like your query hits a database view. How long does the query take? To fetch the totalCount, Appian has to query the view twice. Once for the totalCount, second for the actual data. If a single query is just below the timeout, a second query will raise the bar.
Hi Stefan, Thanks for the response.
I agree with you. Two DB calls will happen if I used totalcount is true. But I am fetching only 1 record from DB and the total count of the table. Usually It should not take more than 3 to 4 sec. But my code taking around 8 to 10 sec to display the error message. I just really want to know how the fetchtotalcount will work internally.
FYI, this is happening only for few DB views not all. Could you please help me to know what is the potential cause for fetching only one record and totalcount
OK. Then your DB view is too slow. Appian has a timeout of 10 seconds. BTW, my timeout, as a user when waiting for a screen, is way lower.
What do you mean with "I just really want to know how the fetchtotalcount will work internally. "?
You will have to optimize that view.
Keep in mind that the fetch total count is likely much slower than the operation to just get a single row too. I also question - why do you need to use a database view? Many of the capabilities of database views can now be supported through synced record types with record relationships, custom fields, and other records capabilities.
Thanks for the response Peter. For my requirement we are joining morethan 7 tables and applying more conditions to filter(Around 4k lines of code). I can't see flexibility by using Records. If I used fetchtotalcount as false and batch sizd is -1 then I am able to get all 70k records in one go but if I change the totalcount as true even 1 record also not fetching and throwing an error. Can you please explain how the fetchtotalcount will work in appain ? I mean what type of logic they written inside and how the calculation will happen internally