Which property to see if a Query has results?

There are three major checks you can use to look at if you want to know if a Query returned results (pretending we have a query called “GetCustomers”):

• GetCustomers.List.Empty — returns “true” or “false” if records were retreived
• GetCustomers.List.Length — returns the number of rows returned by the OutSystems query layer
• GetCustomers.Count — returns the number of records that match the query conditions

Note these distinctions! The first two are not operating directly on the data in the database, they work on the result set that the query system returns from the database. This gives us three main things to understand with regards of the first two:

1. The data gets pulled from the database before those properties are evaluated; if no data has been pulled across the wire yet, it will be when you evaluate those properties. If you are planning on using the results anyways, this is fine. If you do not use the results, the optimizer will select NULL values for the fields you are discarding, but you still have the performance hit of filling up a result set simply to test for emptiness or its size.
2. The data gets 100% retrieved to evaluate these properties. Normally the system would use a cursor to retrieve pages of data at a time, and only what is needed (like the record actually displayed on the screen when the query is bound to a ListRecords or TableRecords widget). Again, we have another potential performance hit because the system will be forced to get all possible records from the database to tell you the length of the result list, which can be a pretty bad performance issue.
3. Because the results are based on the result set itself, properties of the result set will impact the numbers returned. For example, if your query has a “Max Records” property of 50, GetCustomers.List.Length will never exceed 50… even if there are thousands of records in your database that match the query itself. As a result, you can be using the wrong numbers if you are trying to get an accurate count of records in the DB matching the query.

All of this makes it sound like GetCustomers.Count is always the best choice, but that is not quite right. That has the system run the query SELECT’ing COUNT(*) instead of records, which has considerations of its own. When do we use each?

GetCustomers.List.Length

• Use when you need to know the exact sizes of a result set (for example, you are accessing records by their index in the set instead of .Current).
• You have already, or will get all records in the result set anyways.
• Only use if you do not need to know the exact number of matching records in the database.

GetCustomers.List.Empty

• This is more readable in code than List.Length > 0, List.Length ≥1, or List.Length <> 0
• If you only need to test for existence of records, do not need to know how many there are, and do not need any of the data, then using this with the “Max Records” of the Query set to 1 will be the most performant test. Big thanks to Tiago in the comments for pointing this out!

GetCustomers.Count

• You need to get an accurate number of records in the database that match the criteria
• You only need the number and you do not need any of the data at all, GetCustomers.Count > 0 is a more performant test than GetCustomers.List.Length

J.Ja