Adding New functionality To Advance SQL Widget

Aggregates & Queries

I really like advance SQL widget . If very useful for situation where we cannot create the logic using the aggregate directly. 

But as we know SQL query can be written in many ways . Can we have any tool or logic where we can see the performance of the query that we have written (for complex query it can point that which part of query is taking more time ).

 Also if possible it would be better if widget is able to point to the line number if any error occurred while executing the query. 

[Kind of similar  functionality like sql profiler or Sql analyzer]

Created on 21 Feb
Comments (8)

Although i like the idea; I think it would be even better to keep focused on the Aggregate and have more functionality in it, so we don't have to use Advanced SQL; this will promote Visual / Low-Code.

Advanced SQL Widget must only be used by exception.

Changed the category to Aggregates & Queries

The problem is that when you use an Advanced SQL, you are also likely to be writing complex SQL. Since so many OutSystems customers are using the cloud offering which does not allow access to the database to use a tool like SQL Profiler, functionality like this is very important to solve problems.


I understand what you're saying but that is the opposite way that Outsystems is evolving.  Like Matthias wrote, maybe the best is to give more functionality to aggregates.



I like the idea, but like Matthias and Ricardo post we must focus in the aggregate evolution.


Remember that if you are using the Outsystems Cloud platform you can request direct SQL access which then lets you use SSMS to test queries. We do this whenever we want to check poor queries by switching on the "Show Execution Plan" option in SSMS 

John -

That is absolutely, 100% something which I have never seen offered or advertised. That's a *great* approach... but I am floored that such functionality exists.


Direct access is available via VPN upon request to OutSystems.

I agree that the focus should rather be to extend Aggregate functionality, as they are easier to maintain, more readable for new/junior developers not too familiar with advanced SQL (pun intended).

The big thing is also optimisation that is done by the platform when using aggregates. The platform optimises, for example (there are many examples) a select query when you don't use all the columns from all tables in an aggregate. With Advanced SQL queries, you need to specify the columns selected (especially SELECT {Entity}.* due to column order inconsistency) because the platform cannot optimise this (advanced queries are executed on SQL Server with the sp_executesql command which, from experience, will yield inconsistent results).