Hi Marcelo.

You might be surprised to know that this is done automatically. OutSystems analyzes your code to see which are the columns from your aggregate that you will need, and optimizes the SELECT statement for those columns only.

You might be even more surprised to know that it also optimizes the number of rows to fetch. So, if you are only interested in knowing if the aggregate list is empty or not, OutSystems is able to optimize your SELECT to return just the first row. Or, if you are displaying the first 10 results in a table, the SELECT is optimized to return the first 10 rows and not more.

So if i have and aggregate with multiple entities but I only want the list of one of them can I assign it directly to one variable of that type?

EX: aggregate with "a" inner join "b". i want to assign to the var "x" of type "a" aggregate.list. is this possible?

I already knew outsystem about the Outsystems optimizations. But i really would like to know if its possible to achieve what I want. If its possible i will just delete the ideia. If not i will update the ideia to explain me better.

Thx for your feedback.

Hi Marcelo,

You can indeed have an Aggregate that joins A with B and then have an Assign that only saves the List of As. The screenshot below shows this. The Type of the variable is A List. Let me know if you need any further help! 

Regards,

Henrique Henriques

Sorry, the image was smaller than expected. Here's a larger version so you can see what's in the Assign.

I work mostly on version 9. But good to know that its workins like that on 10.

Hello Henrique,

As your approach, we still get the full of column from database query and it is only the assignment the returned value to a list with your selected attributes. It's not a solution for this idea. I'm looking for having this idea on our Outsystems.

Thang Nguyen

Hi Thang.

How did you come to that conclusion? I ask this because, if you were watching at the debugger variables, then the values will not be optimized. But if no debugger is attached, then the aggregates will be optimized.

Because of that, in practice, there's no easy way to observe the optimizations taking place. The easiest way to observe it is to compare two screens, one with the optimization, and another without the optimization. Here's a recipe for doing that:

1. Create two entities, A and B. B should have a foreign key to A, and also an attribute of type binary data. We will use the binary data to populate B with large files, so that we can observe the optimization by comparing the performance between the two queries.

2. Populate the two entities. Scaffolding the entities might help to create the screens to populate them. Create a few entries in A, and a few entries of B with relationships to A. Make sure to upload large files (a few MB each) to all instances of B.

3. Reference the BinaryDataSize action comes from BinaryData extension. We will use this to read the binary and display some values on the screen.

4. Create a screen without the optimization. For this, place an aggregate with A JOIN B on the preparation. Then display the results in a table records, and add a column to display the size of the binary field.

5. Create a screen with the optimization. For this, place the same aggregate with A JOIN B on the preparation. Then, assign the aggregate's result to a variable with type List of A. Then display this variable in a table records.

6. Compare the performance of both screens, without running the debugger. If you have access to the database, you can also check the executed SQL, or check how many MB were transferred in each screen.

7. Conclusion: given that the aggregate is the same in both screens, the difference in performance can only be explained if the executed queries were different, and the second screen did not include the binary column in the SQL. We are, obviously, assuming that executing the BinaryDataSize function (which is the only differences between the screens) has negligible performance impact.