Performance of Entity Backed Record

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.

  Discussion posts and replies are publicly visible

  • Hi Mekail,

    In entity backed records, we have an option called "Default Filters" where we can filter the data based upon the conditions provided.
    1. For example for a particular user we can display his related records instead of displaying the entire data.
    2. We can also fetch the records based upon the dates i.e. (today()-365) will fetch last one year records.
  • Hi Vikas,

    Suppose i have already applied the filter and i have more than 100k of rows than how will i handle it?

    Thanks

  • Hi Mekail,

    you can handle the data at the data base level also while creating the view ( Select Top 1000 Rows) and fetch last modified 1000 rows while fetching the data itself.

    And by using those 2 default filters ( based upon user and date (today()-365)) we can filter the data to maximum level even then if you have too much data add filters based upon requirement so that optimize the data to increase performance.

    I hope this will help you.

    Thanks
  • 0
    Certified Lead Developer
    Hi 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.
  • 0
    Certified Lead Developer
    Use pagingInfo to fetch a batch of data at a time. eg. In batches of 25 or 50.
  • As suggested above the use of pagination 25-50 or depends on your requirement will not give performance of the record.
    As per my Understanding, I would recommend the following points to be considered.
    1. Retrieve only required number of columns.
    2. Display the additional columns data in the record summary view.
    3. Provide User filters, so that user can filter down the records displaying.
  • +1
    A Score Level 1
    in reply to vikass619

    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.

  • Hi Aloks,

    How to Fetch Data in the form of batches while fetching the data from Data Base for records.

    Thanks
  • 0
    Certified Lead Developer
    in reply to krishnaprasadd
    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?
  • 0
    A Score Level 1
    in reply to aloks0189
    Yes, you can write a condition that acts like a where clause in the default filter, but as I understand it will be applied per row and so performs badly across larger datasets. A service backed record can use a query entity which applies the where clause "properly", and so performs much better.