Skip to main content

Trusted Advisor

OutSystems

Control the number of records fetched from the DB

In a Web/Mobile application usually only a fixed set of rows is returned when listing information. Additional records are fetched with the use of pagination or infinite scroll mechanism. Your Aggregates or SQL queries should always take into account the required number of records.

Impact

Not limiting the number of rows in a query results in unnecessary CPU use, IO consumptions on the database, and unwanted memory take on the Web Server - just to hold the result of the entire query. Imagine fetching 1K records in every call when all you need is the first 10.

Best Practices

With aggregates:

  • Set the Max. records property of the aggregate to the required usage

  • If required, the <Aggregate>.Count will hold the total count of records, without the Max. records limitation

With SQL queries:

To limit the number of Rows to the required usage in a SQL query, it is not enough to set the Max. Records. This will not change the query itself to limit the number of rows (it will only limit the number of rows that are copied to the output record list. i.e., the unnecessary load on the database is not saved).

The solution is to control the number of rows inside the query:

 

With this change you no longer rely on the <Query>.Count property to get the correct count of rows complying with the original query conditions and joins (also see Appropriate record counting).

  • Was this article helpful?