Difference between Query.List.Empty and Query.Count <= 0 ?

Difference between Query.List.Empty and Query.Count <= 0 ?

  
Hi, anyone knows the difference  between Query.List.Empty and Query.Count <= 0 ?

Is there any impact on Performance for using the .Empty, while comparing to the other option?

Best Regards,

Diogo C S Cordeiro
Query.Count is calculated using a separate query from Query.List, because this Count is the total number of records in the database, and not the length of Query.List, which normally only returns the minimum needed subset of records needed for the screen where it is being used.
So, if the Count is not used anywhere else, it will be more performant to use Query.List.Empty, so the optimizer will discover that the Count is not used and not do that second query at all.

Best Regards,
Gustavo Guerra
Thanks Gustavo, that was exactly what i wanted to know.

Best Regards,

Diogo C S Cordeiro
Erm,

So If I understand it right, if you have pagination in the lists you are "screwed" because 2 queries will run no matter what?

Hi Joost,

No, in fact that should really be the best option as you'll have one optimized query to fetch the list to display and another one to get the count.

Cheers,
Tiago Simões
Hi all,

I think the most important thing here is that using ".count" to compare with "0" is never a good idea.
Count can be a very inefficient query if there are a lot of records in the database.
If you just want to know if there is any record always use the ".list.Empty".

If you want to know how many records there are (as for the list pagination) then you should use count.

best regards,
Vasco
Hi all,

Vasco is right count can be very inefficient. My question is if the count query is also optimized? For reference you can read this article.

Cheers,
André
Bump this old thread. So when should we use what? We have 1. Query.Count 2. Query.list.length 3. Query.list.Empty should we even consider Count? Or do we give Wotc a bump to drop it alltogether? And what optimizations/differences are there betweem .length =0 and .empty =true ?
Hello Joost

Well, bottom line is that each variable within the query output has different semantics, different internals and should be used on different scenarios. Let me try to explain the main differences about them, why they exist and when they should be used.

When having a query (Simple or Advanced) with the Max Records value set, the Agile Platform generates the application code to optimize the data fetch of the query (let's also abstract from the query performance or the number of results that can return). But before that, it's importante to define query fetched rows, returned rows and total possible rows.

The fetched rows is the query results returned by the query to the application, limited by the Max Records in simple and advacned queries.
The returned rows are the query results returned by the query on the database side, limited by Max records in simple queries, but not on advanced queries.
The total possible rows are the number of rows that the simple query would return if no Max Records is defined. In advanced queries is the same as the returned rows.

Query.Count

When you use the query.count in your espace, the query will be executed twice: one is the query as-is, and the other is to determine the total possible rows returned without the influence of the Max records. You see, in Simple Queries, the max records will set a TOP clause on the query ( or ROWCOUNT limit in Oracle), limiting the number of rows returned by the query, even if more rows could have been returned with that TOP or ROWCOUNT limit. This means that you can get 100 rows from a possible 1000, and the second query execution will execute the same query, without fetching the data, but just counting the number of total possible rows without any TOP or ROWCOUNT limit. This will allow you to know any many ROWS you actually have that match you query conditions, even though you fetch only a smaller amount.

In advanced queries however, is slightly different, the TOP nor the ROWCOUNT limit will be set since it would change the developer's query code, even though the Max Records is set. This means that the number of rows returned by the query is the same as the count. However, when iterating the query, only Max Records will actually be fetched to the application.

This variable should be used when you have a table records with navigation, and need to have the Total rows to show on the list navigation the total amount of items, even though you show only a few per page.

Query.Empty

This variable is a fast way to check if any row is returned. If it's Empty (Empty=True), then the query returned 0 rows. It's a short cut to rapidly check if there's any rows, and it doesn't execute any additional queries to count the rows.

It should be used to check if there are any rows or not. Like Vasco referred, it's best to use this variable then the count variable to check if there's any rows returned, since it's one less query to be executed.

Query.Length

This is the actual result of the fetched rows of the query so far. It is limited by the Max records, since it's the Max records that limits the rows of the query. This value is calculated based on the number of items within the query record list variable, so no additional queries are executed, since it's all in the application's memory.

This should be used to get the size of the list within the query, to size table records or list records, or to limit for each iterations, or even to calculate the number of items that are shown per page on a list navigation widget.


With all this in mind, the Query.Count should be used only when you really need to get the total possible rows of your query. And in practice, only on your Simple Queries, since in the advanced queries is identical to the Query.Length, with the exception of some optimizations described below.

When you have a query directly linked to a table record or list record, or are iterating the query list, the Agile Platform will only fetch the the data and update the Query.Length accordingly, meaning that if the iteration executes less times then the returned rows, it will have only the number of fetched rows.

Hope this information is helpful, and that I didn't confuse you even further :)

Cheers

Miguel Simões João
Very insightful Miguel. Kudos for that!!!!

Allow me to add just a side note, since your answer raised a question from a colleague of mine that I thought was very pertinent.

"In advanced queries however, is slightly different, the TOP nor the ROWCOUNT limit will be set since it would change the developer's query code"

This means that setting Maximum Rows on an advanced query will not affect the performance of the query itself, but rather just eventual data processing on the returned list. If you're aiming at performance with specific advanced queries, when using list navigation for instance, the way to do it is set an InLine parameter that will receive the maximum rows to return and include the parameter in your SQL.

Eg. Select TOP @MaxRowParameter tbl.attr1, tbl.attr2 FROM tbl

Just thought I'd put that out there, in case anyone else wonders as well.
Miguel
 
Good answer!
Great answer :)

It helps us (all developers I think) how, when, what we should use and why.
Especially because most people don't look under the hood :)

@Joost, nice you bumb this topic since a lot more about the .count become clear.

@Miquel, thanks for explaning which is doing what. When reading it all it all becomes a little bit confused about how to apply the best. I'm more thinking on how to use the paging on the best way with a table record (since the .count attbibute is mostly used for the paging component). To make myself more clear, when having a TR with 500 records but only 100 records are shown, would it be better to get the whole list once or just get the first 100 and when clicking the next page get the next 100?

Another thing I would like to know is about the platform optimalisation. You speak that the queries are optimized by the platform, but I know there are some conditions before that is done (the query must be in an action of the screen where the data is used and the platform must be in production mode). Are these conditions also needed to optimize the .count attribute? So when using a simple query in a public action (in it's own eSpace or a referenced eSpace), it will never be optimized (and then there will always be executed 2 queries)? And how about using the query in a webblock, would it still be optimized?

I must say that the logic of setting max records in a advanced query is an eye opener :O.

Kind regards,
Evert
Hello

@Antonio, that's exactly it. If you want to get best performance from an advanced query by limiting the returned rows, use the TOP with an input parameter. The Max Records will only limit the fetched rows.

@Joost, for the typical TR scenario with navigation, it is best to have a query returning 100 records at the time, even if the Query.Count would be 500. This will improve performance becauseyou'll only fetch 100 rows on the first page, 200 on the second, and so on ...

Regarding the optimization of the queries, they are optimized at several levels, depending on the use case and the location within the application (this subject would require a all new topic by the way :) ). Let me try to summarize some of them here:
  • Simple Queries only fetch used attributes: the Agile Platform compiler, optimizes the generated code to only fetch the attributes that are actually used by the application within that flow. If you pass the entire query output list into another action, all attributes will be fetched.
  • Query fetch as you demand: on both simple and advanced queries, after the query is executed, only when iterating the query will the rows be fetched into the application. This usually optimizes application memory when rendering query output in table or list records. If you iterate the query output in a for each cycle, it will also fetch one by each for each iteration. If you use the query output to pass on into another action, it depends on the action, but as soon as the list is iterated, the rows are fetched.
  • The Query.count tries to only count rows, and not fetch any data. This is well achieved in Simple Queries, but not in Advanced queries.
Both these optimizations occur in runtime only, on applicaiton published in production mode (without debug). If you attempt to debug the application, to have the query values available on the debugger, these optimizations are skipped.

Cheers

Miguel Simões João
Hello!
First, this is a good (kind of old, 2012) thread explaining "Empty", "Count", and "Length".
I'm bring it to live because I have a question regarding the use of TOP in advanced queries to improve performance (by limiting upfront the "returned rows" from the database, instead of limiting the "fetched rows" to the application with the use of a MaxRecords value), when using list navigation.
The example António Chinita gave was: Select TOP @MaxRowParameter tbl.attr1, tbl.attr2 FROM tbl
Since TOP returns only an indicated number of rows, that also corresponds to the count, so how can we determine the total number of rows existing in the database to show list navigation information like "1 to 10 of 100 records"?
The only answer I see is: We must also execute ourselves a second advanced query to specifically determine the count: Select COUNT(*) tbl.attr1, tbl.attr2 FROM tbl
Or am I incorrect?

António Chinita wrote:
Very insightful Miguel. Kudos for that!!!!

Allow me to add just a side note, since your answer raised a question from a colleague of mine that I thought was very pertinent.

"In advanced queries however, is slightly different, the TOP nor the ROWCOUNT limit will be set since it would change the developer's query code"

This means that setting Maximum Rows on an advanced query will not affect the performance of the query itself, but rather just eventual data processing on the returned list. If you're aiming at performance with specific advanced queries, when using list navigation for instance, the way to do it is set an InLine parameter that will receive the maximum rows to return and include the parameter in your SQL.

Eg. Select TOP @MaxRowParameter tbl.attr1, tbl.attr2 FROM tbl

Just thought I'd put that out there, in case anyone else wonders as well.
 
 
Hi Tiago,

You're right, you need a second query for the count