Hi All,
Is there any most efficient way to get a total count of a large data set of an Entity without pulling all the data itself from the data set . Use case is to retrieve only total no records in an enitity.Thanks in advance.
Discussion posts and replies are publicly visible
Using a!queryEntity(fetchTotalCount: true) will get the total count of a large set. You should then set your pagingInfo to a!pagingInfo(1,1) which will only return one entry from the entity
To make it even more efficient you can set the columns parameter so your query only returns the ID of the first result.
You might want to benchmark before and after doing that, in case the filtering actually takes more time that grabbing the whole first record.
Thanks for the inputs. we tried these two ways. we got little improvement by selecting only one column. As per the documentation fetchTotalCount having performance impact. Is there any other alternative way to achieve this?
You can create a custom database view to count all of the rows of a table, then query this view from Appian
I'd think it more efficient to use a!pagingInfo(1, 0) which will return only the metadata including totalCount, but no DB rows. Should be at least a bit more slender than returning 1 row. This should also negate the need to request a specially trim set of columns to return, since this returns no columns.
Another thing you might consider this app https://community.appian.com/b/appmarket/posts/execute-stored-procedure from the app market. You can use that to execute a SQL stored procedure, or more precisely a FUNCTION that returns the count of that table.
In essence taking advantage of the fact that SQL can count SQL faster than Appian can, which is what the custom View would also do.
Yup, I'd opt for a Stored Proc that effectively performs a SELECT COUNT(*) FROM <yourTable>
Thanks a lot for all your inputs. will try and use it which ever is promising in our case.