Home
Dx insights
Optimising paged sql queries in outsystems

Optimising paged SQL queries in OutSystems

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.

John Salamat, Tech Lead + MVP, shows you how. Watch this 2-minute video now.

Team PhoenixDX

				
					
         
          1SELECT *, COUNT(*) OVER() AS FullCount 2 3FROM Table 4 5WHERE … 6 7ORDER BY … 8 9OFFSET @StartIndex ROWS FETCH NEXT @MaxRecords ROWS ONLY

Share on LinkedIn

Lastest DX Insights

Dev Tip: Creating Namespaces in OutSystems

Dev Tips . 4 Min read

Agentic AI is a double-edged sword. Here’s how to avoid getting cut

DX Insights . 2 Min read

Where agentic AI really moves the needle

DX Insights . 2 Min read

Optimising paged SQL queries in OutSystems

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.

John Salamat, Tech Lead + MVP, shows you how. Watch this 2-minute video now.

Explore more

Need a digital solution tailored to your needs?