Optimizing paged SQL queries in OutSystems

In OutSystems, SQLs help address complex data requirements. Often, we consider optimization by retrieving only data that’s needed a.k.a lazy loading . One of the common ways to do this is paging the data.

Implementation wise — if we are to show the all the pages available, we need to get the total number of records. I’ve seen a lot of times a pattern where two separate queries have been made to achieve this. The first query is to retrieve the paged data and the second is to get the total count.

This setup smells two things — more processing time is needed for the data source and two elements to maintain which means when you need modification on the query, you need to do it on both SQLs. Using the following syntax below, we are able to get the total count of the rows more than the max records.

SELECT *, COUNT(*) OVER() AS FullCount


FROM Entity


WHERE …


ORDER BY …


OFFSET @StartIndex ROWS FETCH NEXT @MaxRecords  ROWS ONLY

Take into account that another field needs to be added to the result for the total number of rows to be part of the result.

Merging the count with the output query can cut response time in half and provides a more maintainable query.

Full demo https://phoenix-dx.com/optimising-paged-sql-queries-in-outsystems/