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 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
), -- 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 doHave more control over indexes, which makes your query run faster than AggregatesAvoid multiple round-trips to the server Can connect joins in. way an aggregate can't do Can connect joins in. way an aggregate can't do Have more control over indexes, which makes your query run faster than Aggregates Have more control over indexes, which makes your query run faster than Aggregates Avoid multiple round-trips to the server 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.

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

Dash

Arweave

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

OutSystems With SQL: Low-code Meets Full Control

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

10 Strategic Technology Trends Businesses Should Plan for in 2021

130 Stories To Learn About Low Code

3 Types of Low-Code Development Platform That Every CIO Should Know About

5 Best Low-Code Development Platforms in 2022

5 Low/No-Code Tools For Freelance Mobile App Development in 2020

10 Strategic Technology Trends Businesses Should Plan for in 2021

130 Stories To Learn About Low Code

3 Types of Low-Code Development Platform That Every CIO Should Know About

5 Best Low-Code Development Platforms in 2022

5 Low/No-Code Tools For Freelance Mobile App Development in 2020

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps