TOP in advanced Queries

TOP in advanced Queries

  
In an Advanced Query when we use the Max. Records Property there will no TOP done to the query and when we use TOP it shows a warning that we should use the Max Records. Anybody knows how it works? Because the final query that is sent to the Database doesn't uses TOP and that means that all the records are returned and makes the queries slow.

Thanks in Advance,
RC

Aye, I agree.

Hi Rodrigo,

The platform does in fact optimize this parameter.
You'll need to have an advanced query result assign directly, or indirectly through a variable, to the tablerecord. You should not have that result assign to anything else, for instance if you use the same variable to export as an excel than the max records optimization is no longer done.
Furthermore the tablerecord needs to have pagination meaning (and this part am not 100% sure) that the Start Index parameter must be assigned to something not a literal, if you use the richwidgets pagination it works.

Hope this helps you.

Cheers,
André
Hi,
In fact the final query that is sent to the Database doesn't use TOP, so it will return all the records of that table, meaning that can occur timeout.
This problem ocorred to me in a client with a table with millions of records. So if you use advance queries using TOP will resolve this issue.



Hello

As André already stated, although the TOP statement is never introduced by the Agile Platform on an Advanced Query with a Max Records value, the OutSystems Compiler Optimizer will detect the context in which the Advanced Query output is used and only fetch the rows based on that context. As documented on the Service Studio help topic Advanced Query Properties, the Agile Platform will fetch a specific number or rows, based on the following semantics:

If a value is not specified, the Agile Platform uses the following as Max. Records:
  • When mapped directly on widgets: StartIndex+Linecount+1
  • When Exporting to Excel: No limitof records
If you duplicate the Advanced Query output, or pass it onto an User Action as an input parameter, the effect will be the same as when Exporting to Excel.

So, in a nutshell:
  • If you associate the Advanced Query output directly to a screen widget (List Records or Table Records), the Agile Platform will optimize the data fetch of the Advanced Query, based on the properties of those widgets (Startindex and Linecount).
  • If you use the Advanced Query output in any other way, the Agile Platform will attempt to fetch all data rows returned by that query, which in this case, in order to avoid timeouts or low performance queries, one should set the TOP clause on the Advanced Query, even if there's a warning about that clause. I believe that's a minor bug of the Advanced Query SQL Parser, and it will be addressed by OutSystems R&D.
Hope this information is helpful in boosting the performance of your Advanced Queries.

Cheers

MIguel Simões João

If preparation have multiple advanced queries based on some condition and if you put the same condition on the source of table records source record list , would it get all the records or only the records needed on a page ? 
Hello

If your table record source is directly from an advanced query, when rendering the page, only the records up to the ones shown on the page will be fetched.

But if your table records is using a local recordlist variable where you copy the records from an advancaed query output, then all records will be fetched.

If you still have questions about this pattern, can you share your example so I can do a more precise analysis?

Thanks

Cheers


Thanks Miguel for prompt reply.

Let me explain the scenario. I have a screeen with an input parameter , depending on which I execute 1 of 5 advacned queries, all of which have the same output structure. Now what I was doing that I was saving the output in a common recordList local variable . I was then setting it as the source of the table records. Now the number of records return is rather large (in millions). What I was noticing that the time it was taking to render the screen on the first time was pretty large. Also jumping from page to page was also taking the same amount of time. So I researched here and found that If you save the output of an advanced query in a local variable, it will return all the records. 

So I tried the second approach. I deleted the local variable and set the source of the table records to one of the advanced queries using if and the input parameter. I am using the same logic for total rowcount parameter in the List_Navigation and List_Counter widget. I checked in the profiler and it seems that it is still returning all the records on the first screen rendering and the moving between pages. 

Thanks
Hello

That is the expected behavior because there's a difference between the query execution and the data fetch.

When you have an advanced query, regardless of the max records value, the query will be executed in the database engine as if there isn't any records limit, but only when fetching the records the Platform will limit to the max records value.

This means that if you have a table with 10 million records and execute the advanced query over that table, regardless of the max records, the database engine will execute the query over all 10 million records. Then, the platform will only fetch the first Max Records. So if the query takes too long to run over those 10 million records, changing the max record values will not have ny effect in the performance of the query.

The solution here is to implement a TOP clause (SQL Server) or rownum clause (Oracle) in the advanced query, linked to the pagination values. For instance, if you create an input parameter in the advanced query (i.e.,  maxrecords) and add the TOP or ROWNUM clause like this:

For SQL:

SELECT TOP @maxrecords * FROM table;

For Oracle:

SELECT * FROM table WHERE ROWNUM < @maxrecords;

You will limit the number of records processed at the database engine, improving significantly the performance of the query.

You should set the maxrecords value equal to List_Navigation, so it can match the number of rows in the table record.

Overall performance for the first pages should be much better, but if you attempt to navigate to page number 1 million, it might get slower, as the query will be executed over a million records on the database.

Hope this information is helpful to overcome that performance problem.

Cheers
If you implement TOP in the advanced query , pagination doesn't work as the advancedquery.count = tablerecords.linecount (it showing only the first page and think that there is only one page). 
Hello

Yes, that's correct, using the TOP will affect the Advanced Query count variable, and if the pagination is bound to this value it will be affected as well.

If you really need the total number of records, a second advanced query with a count() aggregate might be required.

This is the low side of advanced queries vs simple queries. With the simple queries, all this is done automatically by the Platform. Have you considered using simple queries, or aggregates in version 9, instead of advanced queries?

Cheers
Thanks Miguel, We are still on version 8 and regarding simple query , I have some left joins with multiple conditions and they are not supported by simple query. 
Max Records property works the same for Aggregates? I mean, the query will be executed but the Platform will fetch only the max records value and no TOP is added, right?
In Aggregates, much like in the former Simple Queries, the Platform will optimize it for you based on the properties of the aggregate, including the max records. So internal, it may use the TOP clause to limite the rows returned, unlike the advanced queries where you'll need to explicitly set the TOP.

More information on the "long" thread Difference between Query.List.Empty and Query.List.Count <=0.

Cheers
Thanks Miguel! I'll check it out :)
Miguel João wrote:
In Aggregates, much like in the former Simple Queries, the Platform will optimize it for you based on the properties of the aggregate, including the max records. So internal, it may use the TOP clause to limite the rows returned, unlike the advanced queries where you'll need to explicitly set the TOP.

More information on the "long" thread Difference between Query.List.Empty and Query.List.Count <=0.

Cheers
 Miguel -

Then what does "Max Records" on an Advanced Query do?

J.Ja
 
Hello Justin,

The Max Records on the Advanced Query limits the number of records fetched by the application from the database reader that's opened for that query, but doesn't limit the number of records returned by the query on the database side.

This means that if you have an Advanced query that can return 2 million rows, but has Max Record set to 100, the execution plan of the query on the database side will be executed for those 2 million rows, but the application will only fetch the first 100 rows.

The impact is mainly performance: in advanced queries the max records cannot optimize the query in the background, as the aggregates (or the former simple queries) can, so to get the best performance on complex queries processing large datasets, it's best to limit at the advanced query sql statements (hence the use of TOP for SQL Server), as the max records will not affect the query execution plan on the database engine.

Hope this is provides more info on the subject.

Cheers
Gotcha, thanks!

J.Ja