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

Parents
  • 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.
  • +1
    Certified Lead Developer
    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.

  • Hello,
    I have a question. We are using the service backed record and using stored procedure in that as a expression. By your suggestion how can we increase the performance. Can you help me in that.
    What I believe in Entity Backed record by using the paging record will fetch only that data at that time and click no next button it will fetch other batch on that click
    Thanks in advance.

    Regards
    Sahil Batra
Reply
  • Hello,
    I have a question. We are using the service backed record and using stored procedure in that as a expression. By your suggestion how can we increase the performance. Can you help me in that.
    What I believe in Entity Backed record by using the paging record will fetch only that data at that time and click no next button it will fetch other batch on that click
    Thanks in advance.

    Regards
    Sahil Batra
Children
  • 0
    Certified Lead Developer
    in reply to sahilb346

    What is the performance of the stored procedure like? Are you stuck with a stored procedure or could you use a view or materialised view? Once you've tuned the stored procedure / view, make sure you're returning only the fields required for the record list view as well as a correctly paged datasubset (rather than returning everything and then paging it within the source expression).

    As I understand it, it is true to say that the entity backed record fetches only the data you required but it's how it does that fetch that can be a problem. For example, if you're returning 25 rows of a 1 million row dataset, the default filter can be written in such a way that it has to be applied per row rather than as a simple where clause.

    Obviously with an entity backed record, we don't have control over how this happens - but with a service-backed record we do. In fact, when using a service-backed record its often a good idea to move the default filter to inside the source expression so that you have control over how the filter is applied and can make sure it is done efficiently.