Hub Server Simple Query Optimization

Hub Server Simple Query Optimization

  
Hello Everyone,

I know that simples queries in preparation screens have an excellent optimization mechanism, that is the fact that only the used ENTITY parameters are actually retrieved from the database by the Hub Server.

What I would like to know is: if I have a query from which I use only the Query.ReturnedRowCount property, does this mean that no records are retrieved from the Database? (making it possible to be a good alternative to a count in advanced queries).

My question is motivated by the fact that the Style Guide listing patterns use advanced queries to perform counts, what might become harder to maintain in case the advanced query gets complex.

Best Regards,

Daniel Lourenço
Hi Daniel,

I've seen in some of the recent versions of the platform that in debug mode if you inspect the records retrieved by a query that you'd see which columns were "optimized". You can try to do this in your scenario to see if it works.
Cheers,
André Vieira
Hi André,

I'v gone down the generated code and found that in version 4.1 the generated query is:

sql =
" SELECT TOP " + maxRecords +" " +
" NULL " +
", NULL " +
", NULL " +
", NULL " +
", NULL " +
", NULL " +
", NULL " +
", NULL " +
", NULL " +
", NULL " +
"";

So, the optimization is solely at the fields level, meaning that in case we have a huge number of records it might get slow. Nevertheless, I think it is a good alternative to advanced queries in tables that do not grow too much. Thanks for the tip!

Best Regards,

Daniel Lourenço
Hello Daniel,

Your findings are correct. When you use the ReturnRowCount of a query the optimizations are only made at the field level, and that is the main reason why the Style Guide uses an advanced query to make the “count”. Still, here’s a bit of background on the Platforms’ query optimizations for future reference:

There are two different kinds of query optimizations in the platform: Field optimizations and iteration optimizations. I think you already got the field optimizations… If the compiler is able to assert that a particular field is not going to be used in runtime, it doesn’t fetch it from the database. The iteration optimizations are a bit trickier.

When the compiler generates your query, it analyzes how the results of that query are going to be used in runtime. With this information, the compiler follows a set of rules to decide how the results of the query will be handled. Roughly speaking, the rules and their impact are as follows:

If you only use the 1st row of the query’s result, the compiler generates code to fetch this row and immediately close the database reader. This is highly efficient on both memory and database.

If you only iterate your query results once (e.g. if you only use it in a “Table Record” widget) the compiler is smart enough to generate code that makes use of a database cursor to read your data. This saves runtime memory and insures you only fetch the required data from the database.

In any other situation the runtime needs to have the entire query results in memory. This is obviously the least efficient scenario, but the compiler still uses field optimizations in this situation, so things are still pretty efficient in most cases.

Some scenarios where the fore mentioned situation occurs include: iterating the results of the query more than once (e.g. if you use the query in both a “foreach” element and in a table record widget); accessing the results of the query by index (e.g. if you need Query1[1].User.Name); using the entire query result as an action input or output parameter; or accessing the ReturnedRowCount of a query.

Hope this helps,
Rodrigo
Hi Rodrigo,

Thanks a lot for your information! The optimizations are great!

I thought a bit more about how to avoid the advanced query in the Style Guide and came to the conclusion that we can in fact do it with little penalty by simply executing one simple query (equal to the one we use to get the records for the table records) but with the Max. Records limited to the value:

ListNavigation_GetStartIndex.StartIndex + MaxPageNumbers*LineCount + 1

(I know we could optimize it a bit more because at a certain point we only have half the total pages over the current, but like this is good enough)

We can then pass the Query.ReturnedRowCount value to the TotalRowCount parameter of the ListNavigation web block and it works fine (because the web block in fact only needs to understand if the total number of pages is superior to the maximum pages to display)

To centralize this operation I created a WidgetLibrary40_Ext.oml that exposes a ListNavigation_GetTopTotalRowCount action that returns the value I showed before and placed its usage in the Style Guide listings.

Best Regards,

Daniel Lourenço
Hi Daniel,

That a nice exercise!
Nevertheless you still have two queries to maintain although they're more "built to change".
This would be great if it came directly in the simple query with as a configurable parameter. You'd configure 'return count' and the simple query would return the count to you :)
Cheers,
André
Hi André,

What about have one simple query only with its limit set to:

ListNavigation_GetStartIndex.StartIndex + MaxPageNumbers*LineCount + 1

and then use this query both to show in the table records and use the Query.ReturnedRowCount in the TotalRowCount parameter of the ListNavigation web block.

If you have a table with 5 pages shown in the navigation (that is the default value), this means that you are only retrieving 40 records more than if you were use the standard value (ListNavigation_GetStartIndex.StartIndex + LineCount + 1). Well, in most situations 40 records will have zero impact on your screen performance and by making one query only it will probably get faster).

Do you agree?

Best Regards,

Daniel Lourenço
Hi Daniel,

I agree with you. Now you just have to disregard Service Studio's warning that the limit should be ListNavigation_GetStartIndex.StartIndex + LineCount + 1 and not ListNavigation_GetStartIndex.StartIndex + MaxPageNumbers*LineCount + 1. I think one can live with it.

The other thing to take into account is that you're retrieving MaxPageNumber-1 times more records than you should and this may be ok for small tablerecords with few pages in the ListNavigation widget but it may become heavy on pages with more than the normal 10 to 15 records (say for instance 50 to 100) it also depends on the dimension of each record and of course the number of pages shown. But hey, this is a tunning phase exercise I think.

Amazing job Daniel. I think you're setting a standard :)
Cheers,
André
Thanks for your feedback André,

Indeed there is a Warning when you set the Max. Records to something different from StartIndex+LineCount+1, nevertheless I think it is worth it. As you say, I'll leave optimizations for the tunning phase (or when absolutely necessary).

Best Regards,

Daniel Lourenço