It depends on the specific implementation. Reading a single value is typically faster than filtering for a single value. The read operation uses the key of a table, while the filter operation uses the WHERE clause with table fields, which is less optimized (when considering database operations).
Let's take a look at the following examples:
I have a table in the XQBR addon (Brazil Legislation) that stores events associated with Sage X3 documents called XQRPSLOG.
This table has a composite index, XQLOG0, with the fields DOCNUM+TIPO+SEQ.
There are multiple ways to achieve the desired result, but I'll provide you with the commented implementation as a reference for your own code:
#**#* Example of FILTER and READ#*!LocalFile XQRPSLOG [XQLOG]#open table#---# In this example, I know all the values of the table's key:# So in this case, it's efficient to perform a direct read.## Index : XQLOG0# Descriptor index : DOCNUM+TIPO+SEQ#Read[F:XQLOG]XQLOG0="FV-PR002-2304-000003";1;6If fstat=0# Here, I can work with the values from the READ result.Endif#---# There's a possibility that I DON'T know the current SEQ for this document in this table.# In that case, I can choose to use a filter and retrieve the last SEQ.#Filter[F:XQLOG]Where[F:XQLOG]DOCNUM="FV-PR002-2304-000003" and [F:XQLOG]TIPO=1#Open filterRead[F:XQLOG]Last#First or LastIf fstat=0# With Last or First, I ensure that I will retrieve only one value from the FILTER result.EndifFilter[F:XQLOG]#Close filter.#---# The Filter allows me to work with multiple values using a loop. #Filter[F:XQLOG]Where[F:XQLOG]DOCNUM="FV-PR002-2304-000003"OrderBy[F:XQLOG]SEQ #Open filterFor[F:XQLOG]# In this example, each iteration will represent a row from my filter, which is ordered by the SEQ field.NextFilter[F:XQLOG]#Close filter.LogicCloseFile[F:XQLOG]#Close table
It all depends on your implementation needs. There are additional ways to optimize the code, such as adding new indexes to the table, refactoring the logic, or even approaching table readings in different ways.
FILTER/READ performance question and
in Support
Posted · Edited by Richard Ikeda
translations
Hello @ FábioDavide76,
It depends on the specific implementation. Reading a single value is typically faster than filtering for a single value. The read operation uses the key of a table, while the filter operation uses the WHERE clause with table fields, which is less optimized (when considering database operations).
Let's take a look at the following examples:
I have a table in the XQBR addon (Brazil Legislation) that stores events associated with Sage X3 documents called XQRPSLOG.
This table has a composite index, XQLOG0, with the fields DOCNUM+TIPO+SEQ.
There are multiple ways to achieve the desired result, but I'll provide you with the commented implementation as a reference for your own code:
It all depends on your implementation needs. There are additional ways to optimize the code, such as adding new indexes to the table, refactoring the logic, or even approaching table readings in different ways.
I hope this helps you.
Richard Ikeda.