Skip to main content

Optimize data preparation

Preparing data for a screen should be very efficient to ensure a pleasant user experience.

Data preparation is usually inefficient when:

  1. The Data Model does not support the use case of the screen - e.g., It is necessary to group large volume of data by complex criteria to get averages, counts and alikes or values must be calculated on the run for each record to be listed

  2. There are not enough SQL skills to design efficient SQL queries - e.g., instead of a single SQL query  to combine all the information, a simple aggregate is used to retrieve the data from the master entity and then an aggregate inside a loop collects the details for each row to be appended in a record list


Long preparations with lots of queries and calculations, sometimes inside loops, to compose the required information, results in considerable Web Server and Database Server load

Best practices


  • Know your data needs and design your DB model to contain all required information to support your screens, without having to calculate the information on the fly on each screen request
  • This requires all the calculations to be made in advance
    • When data is generated - e.g. every time you add a vacation period, immediately update the remaining available days in the employee detail instead of calculating it when you want to list the available vacation days per employee

    • In asynchronous jobs (Timers or BPT with automatic activities) to prepare data upfront - e.g. prepare agglomeration tables to support dashboards


  • Use proper joins and SQL functions to gather all required data in a single query
  • Make decisions in the preparation to get data with different queries, instead of making decisions on the screen to call different expressions with different queries

  • Create different specialized screens, instead of a power screen with inefficient data preparation that needs to cope with all situations

  • Was this article helpful?