Hello,My team has a performance issue in a Advanced sql query, basicly we have 2 querys one for the records and another for a count, they look very similar, however the first query takes like 200-300ms to execute the total count query takes more than 5s.
Another strange fact is if in the 'TotalRecords' assign, if we change from the output of the query to a hardcoded value everything runs smoothly.
Any idea what's wrong here?
Main query:
Count Query:
Hi,
You can try to make merge them in just one query and get the total count (as an attribute) in the query where you get the subset of records.
Check this article https://johnalvinsalamat.medium.com/optimizing-paged-sql-queries-in-outsystems-22a13af083ba where this is explained in detail.
This way you are only going to the database once, and is also easier to maintain because right now if you want to change or add a filter you need to do it in 2 queries.
Hope this helps!
Hi,Thanks for the reply.
I totaly agree with that and that is something we are already working on. However i still dont see a reason why one query is fast and another takes too long, the Count query should be even faster.
Well, the first query only has to go through a small subset of the data, especially in the first pages of the pagination, while the second query has to get all the records, even though it's just counting them, so I think it makes sense to be taking more time.
yea that's make sense, even tho both querys are just returning like 6 records (i would assume it should be fast) but the tables without applying filters have Thousands.So yea that would probably make a lot of sense
Another thing is, if i remove this from the count, it's fast...
I only have a quick answer to your second question, if you set output hardcoded, you no longer use output of count query.
Outsystems optimizes for performance and does not execute select queries, unless their output gets used somewhere.
Dorine
That was my thought, but i wasn't sure if it was executed or not because it could be a Update or a delete and we still need to execute and dont use the output.
Thanks for the clarification!
Hi @Ruben Magalhães
Can you try with Count(1)
instead of using count(*)
I think it doesn't make any sense of using count (*) if we want only count result.
Thanks,
Gaurav Rawat
actualy initialy it was Count(1), in that case it makes no difference.
Actually Outsystems recommends what you have (a separate query) with count(1)
https://success.outsystems.com/documentation/11/managing_the_applications_lifecycle/manage_technical_debt/code_analysis_patterns/appropriate_record_counting/
But in my experience, sometimes this is slower than retreiving the COUNT OVER and sometimes is faster without any pattern.