Optimizing Imports with Multithread Batch Processing in Sage X3 to Boost Performance

In Sage X3 installations, standard import slowdowns are becoming increasingly apparent, especially in environments where multiple verticals coexist. This issue is exacerbated by the fact that Sage, as the software provider, does not always control the quality and integrity of the developments provided for each vertical, which can lead to conflicts and slowdowns. The lack of verification between the various developments included in the catalog often leads to performance issues.

To solve this problem, it is possible to parallelize imports through a batch server, allowing multiple tasks to run simultaneously and avoiding the single-task nature of standard imports. Below is an explanation of the solution and the code that enables this optimization.

Classic Scenario

Let’s take a typical example: importing a single piece takes about 2 seconds. In a single-task situation, importing 100 pieces would take 200 seconds. However, by parallelizing the import using 4 separate tasks, we can divide that time by four, reducing it to 50 seconds.

The only limitation to consider is the batch server’s polling time, which is set to 30 seconds by default. This may cause a slight delay in execution, but it is largely offset by the overall performance gain provided by parallelization.

Code Example for Parallelizing Imports in Sage X3

Here’s the code to parallelize imports using multithread batch tasks in Sage X3:

Funprog   YTRX_IMP_X3_BATCH(YTHDTRX, YFILELIST, YAOETPL)
  Value Integer     YTHDTRX
  Value Char        YFILELIST()()
  Value Char        YAOETPL()

  Local File ="X3.ABATRQT"      [F:YABR]
  Local File ="X3.ABATRQTL"     [F:YABL]
  Local Integer YX3BATCHOK    : Raz YX3BATCHOK
  Local Integer YX3BATCHSLEEP : Raz YX3BATCHSLEEP
  Local Integer YTHXTRXID
  Local Integer YNUMREQ(YTHDTRX)
  Local Integer YIBAL

  Trbegin [F:YABR], [F:YABL]
  For YTHXTRXID=0 To (YTHDTRX-1)
    # Query header
    YNUMREQ(YTHXTRXID) = uniqid([F:YABR]) # max(ABATRQT.NUMREQ)+1
    [F:YABR]NUMREQ=YNUMREQ(YTHXTRXID)
    [F:YABR]DAT=date$
    [F:YABR]DFIN=date$
    [F:YABR]DOSSIER=nomap
    [F:YABR]FLAG=1
    [F:YABR]HEURE=format$("D:hhmm", date$)
    [F:YABR]MESSAGE=1
    [F:YABR]MONO=1
    [F:YABR]TACHE="IMPORT"
    [F:YABR]USER=[V]GUSER
    [F:YABR]CREUSR=[V]GUSER
    [F:YABR]UPDUSR=[V]GUSER
    [F:YABR]CREDATTIM=datetime$
    [F:YABR]UPDDATTIM=datetime$
    [F:YABR]LAN=[V]GLANGUE
    # Query Parameters
    For YIABL=1 To 7
      [F:YABL]NUMREQ=YNUMREQ(YTHXTRXID)
      [F:YABL]NUM=YIABL
      [F:YABL]CREUSR=[V]GUSER
      [F:YABL]UPDUSR=[V]GUSER
      [F:YABL]CREDATTIM=datetime$
      [F:YABL]UPDDATTIM=datetime$
      If YIABL = 1
        [F:YABL]PARAM="MODIMP"
        [F:YABL]VALEUR=YAOETPL
      Elsif YIABL = 2
        [F:YABL]PARAM="NOMIMP"
        [F:YABL]VALEUR=left$(filpath("tmp",[L]YFILELIST(YTHXTRXID),"csv"),30)   # ABATRQTL.VALEUR = 30 Alpha max
      Elsif YIABL = 3
        [F:YABL]PARAM="NOMIMP"
        [F:YABL]VALEUR=mid$(filpath("tmp",[L]YFILELIST(YTHXTRXID],"csv"),31,30) # ABATRQTL.VALEUR = 30 Alpha max
      Elsif YIABL = 4
        [F:YABL]PARAM="NOMIMP"
        [F:YABL]VALEUR=right$(filpath("tmp",[L]YFILELIST(YTHXTRXID],"csv"),61)  # ABATRQTL.VALEUR = 30 Alpha max
      Elsif YIABL = 5
        [F:YABL]PARAM="TYPEXP"
        [F:YABL]VALEUR="2"
      Elsif YIABL = 6
        [F:YABL]PARAM="VOLFIL"
        [F:YABL]VALEUR=left$("[TMP]/"+[L]YFILELIST(YTHXTRXID]+".csv",30)        # ABATRQTL.VALEUR = 30 Alpha max
      Elsif YIABL = 7
        [F:YABL]PARAM="VOLFIL"
        [F:YABL]VALEUR=right$("[TMP]/"+[L]YFILELIST(YTHXTRXID]+".csv",31)       # ABATRQTL.VALEUR = 30 Alpha max
      Endif
      Write [F:YABL]
    Next YIABL
    Write [F:YABR]
  Next YTHXTRXID
  If !fstat : Commit : Else : Rollback : Endif

  # We loop on the queries until they finished to been executed
  # Otherwise VAT will not be available
  If find(YAOETPL,"YTRXSIHVAT","YTRXSIHCST")
    Repeat
      Read [F:YABR]NUMREQ = YNUMREQ(YX3BATCHOK)
      # 1 = Standby (max 30s)
      # 2 = In progress
      # 3 = Finished
      # 7 = Error
      If [F:YABR]FLAG>2 : YX3BATCHOK += 1
      Else : Sleep 1 : YX3BATCHSLEEP += 1 : Endif
    Until YX3BATCHOK = [L]YTHDTRX
  Endif
End YX3BATCHSLEEP

Solution Explanation

The code above illustrates how to set up batch queries to handle multiple import files in parallel. Each file is processed in a separate task, identified by a unique NUMREQ. The parameters associated with each query are specified in the ABATRQTL table.

The script then loops until all tasks have completed their execution (with possible statuses of "Standby", "In progress", "Finished", or "Error"). This loop ensures that necessary data, such as VAT, is available before validating the import.

Conclusion

Thanks to this import parallelization technique in Sage X3, it is possible to divide the total import execution time by four (or more), making the process much more efficient in environments where multiple verticals coexist. The main advantage is a considerable reduction in processing times while maintaining a standard architecture on the batch server.