FabioDavide76 Posted May 9, 2023 Posted May 9, 2023 (edited) 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 1
Richard Ikeda Posted May 26, 2023 Posted May 26, 2023 (edited) 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
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now