Max records possible bug on Advanced Queries

Max records possible bug on Advanced Queries

  
Hi,

I think I've found a bug on the platform regarding to the max records property on advanced queries, at least when using the platform with an Oracle Database. 

I've done a simple test, creating a user action with an advanced query and a simple query both with the max records property set.

After executing the action I checked the  executed SQL by querying de view V$SQL:  for the simple query, everything was as expected, the executed SQL had an ROWNUM with the value of the max records; but for the advanced query, the executed SQL was exactly the same as defined in the service studio, with no ROWNUM property.

Anyone had the same issue?

I'm using platform version 8.0.1.12, with stack .Net and Oracle 11g 
Hi Bruno,

The Advanced Query text is never modified (apart from replacing the {Entities} and expand inline @parameters).
So, yes the Max Records in an Advanced Query never results in a TOP / ROWNUM operation being added to the query. It just means that the reader/cursor returned by the query will never be iterated more times than the Max Records.

If you want to improve performance on advanced queries, you should add it as a query input manually.

Regards,
João Rosado
Hi João,

First of all, thanks for your explanation.

But don't you think this fact should be more clear? Until now I always thought that by defining the max records attribute I was limiting the number of records on my query, not on the list returned by the query. 
Yes, I agree that it should be more explicit on the description, expecially since it has a different behavior than on Simple Queries (where it does what you expected).

Regards,
João Rosado
João Rosado wrote:
Hi Bruno,

The Advanced Query text is never modified (apart from replacing the {Entities} and expand inline @parameters).
So, yes the Max Records in an Advanced Query never results in a TOP / ROWNUM operation being added to the query. It just means that the reader/cursor returned by the query will never be iterated more times than the Max Records.

If you want to improve performance on advanced queries, you should add it as a query input manually.

Regards,
João Rosado
 
 Hi Joao, can this info be put in the documentation? It would help.

Joel

Hi!

This is very bad... The behavior is even more misleading because of poor documentation, here:

https://success.outsystems.com/Documentation/Best_Practices/Performance_Best_Practices/Performance_Best_Practices_-_Queries#Keep_Max_Records_consistent_with_your_needs


Keep Max Records consistent with your needs


Description

Keep the Max Records property of the Aggregates and SQL queries, consistent with the amount of data that you're displaying.



Solution 

When there are limitations to the amount of records that will be fetched from a query, you should fill the Max Records of the query accordingly to optimize the query execution time. This is especially useful in table records or when an Aggregate is used to get a single record.



Importance

Usually, we don't need to display thousands of records in a single screen, so there's no need to get all of them from the database. Only get the amount of rows that will be displayed. This improves screen loading.



Remarks

In previous versions of the OutSystems Platform (before version 4.2) the SQL queries didn't have the Max Records property. You had to add the clause in the query, but the same considerations apply.


Please notice that the original thread was from 4 years ago and it was related to platform version 8.0.1.12 . The current documentation is about 10.x versions of the platform. That time there was not Aggregates yet and probably the behavior was quite different from now.



Could someone from OutSystems R&D confirm that in P10 the use of the Max. Records attribute in an advanced query is equivalent to adding the TOP/ROWNUM restriction to the query? 

I did a small check by measuring the time it takes for an advanced query to retrieve 10.000 records from a table with around 1.000.000 records. The time was measured in milliseconds using the ticks forge component

I had a version of the advanced query where the Max. Records attribute of the advanced query was set to 10000. The query itself was a simple SELECT * FROM <Table Name>. I had another version of the advanced query where 10000 was passed as a parameter to the query and used in the query to limit the ROWNUM (SELECT * FROM <Table Name> WHERE ROWNUM < @MaxRecords). Note that in this version of the advanced query the Max. Records attribute was not set. 

Both versions of the query took about the same time to execute. Therefore, I assume that the platform itself modifies the actual query executed in the database if the Max. Records attribute is set.

Are there any flaws in my reasoning? 


Greetings,

pedro


Hi Pedro,

I'm not from OustSystems R&D, but from my tests I conclude that the behavior is still the same and the query is not changed and MaxRecords is applied in memory to the Result Set returned from the DB.

Cheers,

Tiago.

Hello Pedro,

Like Tiago, I'm not from Outsystems.

On advanced queries, the Outsystems platform does not add the ROWNUM filter to your SQL. I've just rechecked if this is still true by executing one advanced query with the Max Records set, and checking on the database what SQL was executed (if you're running on Oracle, you can check this with SELECT * from v$sql order by LAST_LOAD_TIME desc), and it confirms that the query executed didn't had the ROWNUM (I'm running on version 10.0.808.0)

I assume that the reason behind this, is that the developer could write more complex queries and in some situations it would not be straightforward to add the ROWNUM automatically.

So, like Tiago added, the Max Records on Advanced Queries only is applied to limit the number of rows that the cursor/reader will return.


Best Regards,

Bruno Lourenço

    

Hi Tiago and Bruno, thanks for your answers. From the tests I did, i don't expect that all records are loaded into memory in the case where the attribute Max Records is used. This would mean that all rows are transfered from the database server into the platform server and only afterwards filtered out. If this was the case, I would expect a large difference in response times between the query with rownum and the query with Max Records. This is not the case.

Instead, as Bruno states, I can imagine that Max Records limits the number of rows the cursor/ reader returns,  i.e. the number of rows transfered from the database server to the platform server. Assuming this is what the platform actually does, in which cases would it be advisable to use rownum instead of the Max Records attribute? In other words, in which cases should we see a difference in time took to retrieve the results between the two cases?

Thanks again, 

Pedro