Performance of Entity Backed Record

Question

If i have more than 100k rows in a table and i want to display it in the Record. As it is a huge data how can i handle it to maintain the performance of the record.

PhilB · Answer

Depending on the expression applied, using the default filters can actually perform badly as the expression is applied per row. Thus, using a grid for the record list and paginating to 25 rows won't necessarily help. 
 Do you expect the table to grow from 100k records? If so (and maybe regardless of this) I'd suggest using a service (expression) backed record as documented here ; they are a fair bit more complex to implement but you gain greater control over the search functionality and over what is actually retrieved by using a query entity expression. You can also use a (well-written) database view to join various tables together and produce a view that matches the record list you want to display - just make sure you include the things you wish to filter on as well! 
 I have seen service backed records handle anything from 500k to 1m rows when used correctly with a view and a properly indexed set of database tables.

aloks0189 · Answer

When you create a record and configure an entity, you can find an option about batch size under record view, which you can modify as per you need. 
 
Also may I know are you building this record over a view or table, because while comparing table and view, table performs way faster than the views. 
 
If your requirement is to join multiple tables then I recommend you to go for materialised view where you can prepare the rows as per your requirement similar like a view but performance wise a lot better. 
 
You can even check MySQL documentation about performance comparison between view and materialised view. 
 
Yes I agree with @philb service backed record can be one option here. Please correct me if I am wrong, when this is not an external entity, then I believe even we can write default filter which will act like where condition while fetching the data from db for this entity, similar to what an expression rule does while using service backed record. So will this really make an improvement in terms of performance if we switch to service backed record?

aloks0189 · Answer

Also make sure, you query only those columns which are needed to be available in record. Because I have seen many cases where one table has 70-80 columns, so it's always a good idea to query only those columns, in this case you can create a display cdt on top of the actual table and configure only those columns which are needed in entity backed record. Configure this cdt in data store entity and build the record on top of this entity. But this type of implementation is required only when you are dealing with a table which is having many columns, which you don't need in your record. This can also impact the performance

aloks0189 · Answer

Hi Mekail Ali as per my understanding, here the most important factor could be pagination. It doesn't matter whether you are dealing with 1000 rows or 100k but what matters is, you should not fetch and render huge data at a time instead of that, use the batch size of 25 or 50 hence Appian can make sure to retrieve only 25 or 50 rows from the last index and can avoid performance slowness.

Performance of Entity Backed Record

Top Replies