FILTER/READ performance question and

[FabioDavide76]

Hi guys.

i've a question about scripting performance.

in some script i found the following syntax

Filter [classitm]where itmkey = value
Read [classitm]First

instead of

Read[classitm]itmkey= value

Which syntax is faster?

Omitting Filter [class] statements after a Filter [class] where field=value.
Could it cause a program to degrade in performance?

Thank u in advance.

 

Edited May 9, 2023 by FabioDavide76

[Richard Ikeda]

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:

  #**
  #* Example of FILTER and READ
  #*!
  Local File 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;6
  If 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 filter
  Read [F:XQLOG] Last #First or Last
  If fstat=0
    # With Last or First, I ensure that I will retrieve only one value from the FILTER result.
  Endif
  Filter [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" Order By [F:XQLOG]SEQ #Open filter
  For [F:XQLOG]
    # In this example, each iteration will represent a row from my filter, which is ordered by the SEQ field.
  Next
  Filter [F:XQLOG] #Close filter.

  LogicClose File [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.

I hope this helps you.

Richard Ikeda.
 

 

Edited May 26, 2023 by Richard Ikeda
translations