When using OutSystems, SQLs help address complex data requirements. We often consider optimisation by retrieving only the needed data, also known as “lazy loading”. I’ve often seen 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 means two things – more processing time is needed at the data source, and you now have two elements to maintain, which means when you need to modify the query, you need to do it on both SQLs. Using the following syntax, please see the example below, you can get the total count of the rows beyond the max records.
1SELECT *, COUNT(*) OVER() AS FullCount
2
3FROM Table
4
5WHERE …
6
7ORDER BY …
8
9OFFSET @StartIndex ROWS FETCH NEXT @MaxRecords ROWS ONLY
John Salamat, Tech Lead + MVP, shows you how. Watch this 2-minute video now.
Sign up for our newsletter and stay tuned for more Dev Tips.
Team PhoenixDX
