It has been almost three years since I began working as an OutSystems developer for a green tech firm. I have completed a number of fruitful projects over this time that have aided in the expansion of the business and helped us reach our net-zero objectives.
For those who are unaware, OutSystems is a low-code development platform that utilises a drag-and-drop interface to enable the creation of scalable, secure business solutions. A visual development environment with reusable components allows developers to easily create complicated programs rather than writing thousands of lines of code.
When we talk about OutSystems, we can't forget to talk about Aggregates, which is the crown of OutSystems, where we drag the tables and perform calculations visually, in which we can connect tables, apply filters, sum, Group, etc, which is optimised and very easy to use.
My OutSystems Screen: A Nightmare
I had been working on a Dashboard screen where we had almost 11 charts coming from 11 different data sources (aggregates), a pure OutSystems way. That made our Dashboard very slow to load, and it was so slow that we started having time-out errors on the front end.
Our customers started complaining about it, which was truly an embarrassment.
Initially, I attempted to improve existing Aggregates by reducing the amount of data and applying pagination to the output. However, despite these efforts, we were still experiencing the same timeout error after 40 seconds of loading. Then I realised to look at another angle, now I had only one option to opt for was SQL.
SQL: My Unexpected Saviour
Making a transition to SQL felt like flying back to the beginning. I developed a single effective SQL query that connected the tables, executed the filters and aggregated the data all at once rather than letting OutSystems Aggregates handle 11 distinct queries independently. They were very different. It now loads in 5–7 seconds instead of the previous 40 seconds (and frequently timed out). The charts displayed without lag, the timeouts were eliminated, and the complaints abruptly disappeared.
My project and, to be honest, my sanity were practically saved by SQL.
-- Step 1: Top 7 CPOs by FinalCost
WITH TopCPOs AS (
SELECT CPOName
FROM (
SELECT
dcr.[ChargepointOperatorName] AS CPOName,
SUM(dcr.[TotalEnergy]) AS TotalEnergy,
SUM(dcr.[FinalCost_incVAT]) AS TotalMetric
FROM {DriverChargingReceipt} dcr
WHERE
dcr.[StartDateTime] >= @FromDate
AND dcr.[StartDateTime] < DATEADD(DAY, 1, @ToDate)
AND dcr.[FleetId] = @FleetId
AND (
@CostCentres IS NULL OR @CostCentres = '' OR
',' + @CostCentres + ',' LIKE '%,' +
CAST(
CASE
WHEN dcr.[FleetGroupId] IS NULL THEN -1
ELSE dcr.[FleetGroupId]
END
AS VARCHAR) + ',%'
)
GROUP BY dcr.[ChargepointOperatorName]
) RankedCPOs
ORDER BY TotalMetric DESC
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
),
SQL Worked Better Here WHY?
Since the platform is optimising it, the aggregate is the recommended choice. But when you connect multiple sources to it, it makes you feel awful and causes you to suffer from a similar problem as mine.
With SQL, you:
- Can connect joins in. way an aggregate can't do
- Have more control over indexes, which makes your query run faster than Aggregates
- Avoid multiple round-trips to the server
In my case, the problem was too many server calls with multiple aggregate fetching similar data, and in that case, SQL consolidated everything into one optimised call — and that made all the difference.
Beyond this fix: What I have learned from Low-code
I learned more about OutSystems and low-code platforms in general after using SQL to solve this issue. Although they are excellent for speed, productivity, and starting a project, they can also hide what’s really happening under the hood. It feels very easy to drag and drop
and connect aggregates. However, the platform creates SQL for you in the background each time you do it
Conclusion
With over 3 years of experience as an OutSystems developer, I have learned numerous lessons, including best practices and effective and ineffective coding techniques. OutSystems taught me that low-code is powerful, but it does not mean low-code aggregates are the best. Like other OutSystems developers, I always thought aggregates could easily help us fetch any kind of data until I faced the serious load issues on my screen.
I learned from this experience that tools are simply tools. I had speed and agility with OutSystems aggregates, but I had control and accuracy with SQL. They worked together to transform a 40-second dashboard that was broken into a seamless 5-second experience.