Skip to main content
OutSystems

Control the number of records fetched from the DB

In a Web/Mobile application, when listing information, normally only a fix set of rows are presented and more records are only summoned with some pagination or infinite scroll mechanism. The 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 and IO consumptions on the Database and unnecessary memory consumption on the Web Server to hold the result of the entire query - imagine fetching 1K records 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, since this will not change the query itself to limit the number of rows, but 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 transformation, you can no longer rely on the <Query>.Count property to get the correct count of rows complying with the original query conditions and joins - see Appropriate record counting
  • Was this article helpful?