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
we already done the optimization as much as possible. Will it helps the indexing concept here ?
To fetch the total count, Appian does a SQL query like "SELECT count(*) FROM ... WHERE ...". As this has to include all filters and hits a slow view, it takes almost the same time as the final query to get the actual data.
There is nothing you can do in Appian to make this faster. If you do not want to use synced records, then one way is to speed up your view using basic principles of database performance tuning.
The other option is, to create some logic in the DB that prepares the data you need and stores it in a separate table to avoid the need for a view.
There are other ways to get the total count with less effort mentioned in this great article and also the explanation why a COUNT() takes so long:https://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/
This has to be executed in a separate SQL. Looks like you are only interested in the total count without the need paging to it because you just read one record. Saying this - the total count with the described methods can be an estimate depending how up-to-date you want to have this number.