Jump to content

FILTER/READ performance question and


Recommended Posts

Hi guys.

i've a question about scripting performance.

in some script i found the following syntax

Filter [classitm]where itmkey = value
Read [

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 by FabioDavide76
  • Like 1
Link to comment
Share on other sites

  • 3 weeks later...

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.

  # 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.
  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.
  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 by Richard Ikeda
Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in

Sign In Now

  • Create New...