1193
Views
5
Comments
Solved
Significance of Max Records in Aggregate or Advanced SQL
Question

Hello Guys, 


Need your expert advice on my one simple query. 


What is the significance of max Record in Aggregate or Advanced SQL.? 

Does it restrict the actual count of data that is being retrieved from the database tables? 

If it does so then How is the count variable returned? 

Using it is a right approach towards implementing custom infinite scroll?  

mvp_badge
MVP
Rank: #5
Solution

Hi Atul, no problem.

1. Never noticed any slow down. In any case, the rows will not be fetched, just counted.

2. That depends a lot. I would say no for most situations... In your case, unless you're sure it will solve your issue, and I think it will not, I would not spend time on this. Instead, I would investigate what is the problem, like, is the query fetching data the bottleneck?

3. Pagination done right (if using the RichWidget and Aggregates) will fetch only the records to show plus 1.

4. Data refresh will execute the aggregate again, so, the query will be sent to the database again. Database engines are pretty good at caching and so on, so... 

I think the problem is probably more in the query itself than the pagination. Avoid doing typecasts/conversions in the aggregate, for example. Even the joins itself can be causing problems.

Check the logs to see how long takes to this query to execute, to see if it is taking longer than desired, and in case yes, try to optimize it.

Remember, the database itself must be adequately maintained, with regular index refresh, statistics, etc. This also can cause significant impact in query performance.

Cheers.


mvp_badge
MVP
Rank: #5

Hello Atul,

Beat way to learn something is to test it. In tour case, set the Max records and see the executed SQL.

So, Max Records will limit the maximum number of records returned by the aggregate/sql.

If your query jas the potential to retrieve 100 records and you set this property to 25, only the first 25 records will be retrieved.

The Aggregate/Sql List.Lenght will show 25.

If you use the Aggregate/Sql Count somewhere in your page or logic, a second query will be executed without limitation, to count the total records the query is able to return.

In our example, the Count will show 100.

The online documentation talks about this. It's worth reading.

Cheers

EDIT:

Personally I hate infinite scroll. But take a look in forge, as I think there is at least one component to do it there, and, if I am not mistaken, The system itself has a widget for that (never used)

Rank: #124

Thanks Eduardo, 

 It is always delightful to hear from you. 

Few question over here: 

1. "If you use the Aggregate/Sql Count somewhere in your page or logic, a second query will be executed without limitation, to count the total records the query is able to return.

In our example, the Count will show 100." 

Does this not reduce the performance and unnecessary fetch all 100 rows which we did not needed in first hand. 


2." The system itself has a widget for that " I am trying to modify the same widget and use it to implement a optimized infinite scroll. Is it worth spending time? 


3. We have more than 90K rows in our database and currently system (with pagination) slows down like hell during page loading,  so going with optimized infinite scroll is the solution I can think of for now. Does pagination retrieves whole data in one go? 


4. Does refreshing a Aggregate or SQL query again does the same amount of calculations? or is it uses the same set of data to operate. 


Sorry to load you with lots of queries but I am curious. 


Thanks and Regards 

Atul Patel

mvp_badge
MVP
Rank: #5
Solution

Hi Atul, no problem.

1. Never noticed any slow down. In any case, the rows will not be fetched, just counted.

2. That depends a lot. I would say no for most situations... In your case, unless you're sure it will solve your issue, and I think it will not, I would not spend time on this. Instead, I would investigate what is the problem, like, is the query fetching data the bottleneck?

3. Pagination done right (if using the RichWidget and Aggregates) will fetch only the records to show plus 1.

4. Data refresh will execute the aggregate again, so, the query will be sent to the database again. Database engines are pretty good at caching and so on, so... 

I think the problem is probably more in the query itself than the pagination. Avoid doing typecasts/conversions in the aggregate, for example. Even the joins itself can be causing problems.

Check the logs to see how long takes to this query to execute, to see if it is taking longer than desired, and in case yes, try to optimize it.

Remember, the database itself must be adequately maintained, with regular index refresh, statistics, etc. This also can cause significant impact in query performance.

Cheers.


Rank: #1644

For future reference:

"Setting the Max Records property in SQL elements will not change its SQL statement. This limit is only applied at the application level to the results returned by the database. Unlike Aggregates, you will need to add a SQL clause to the statement of the SQL element (e.g. TOP) to filter the results at the database level."

Quoted from: https://success.outsystems.com/Documentation/Best_Practices/Performance_Best_Practices/Performance_Best_Practices_-_Queries

---------------------------------------------------------------------------------------------------------------

And from "Master Class on Best Practices and Timers" it is understood that when a table is paginated, the widgets take care of setting the aggregate's 'Max Records' in the background. However, when you load a page of the table, it will retrieve all the records of that page, plus all records from the previous pages, meaning that the last page will load the entire set. And this is also hinted by the warning you get when setting the property of the aggregate used in a paginated table:

"GetTests 'Max. Records' should be set to 'List_Navigation_GetStartIndex(TestTable.Id, True) + TestTable.LineCount + 1' to match the number of records presented by TestTable."