Skip to Content

Perfomance insert / update slow on DI API 9.2 (SQL)

Dear Experts,

We have addon that doing batch process creates Sales Order, DO, AR Invoice in SAP Business One, I've monitored for each module insert, the average document insert into SAP Business One is only 100 document / 1 minute.

everyday we need to generate 10.000 AR Invoice, which means now, it can run 100 minutes / about 2 hours. the projected document will increase twice each year.....

is there any thing to do to improve perfomance ? is this normal ? what's your usually benchmark when inserting DI SAP ?

We run this on a server with a good spec, memory 32 gb, procesor 8 core,

Notes : this is for SBO SQL version, please dont suggest to use service layer HANA :)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Apr 06, 2018 at 01:12 PM

    Hi Hendra,

    Please take the following general best practice

    1. Use DI API 64 bit instead of 32 bit.
    2. Open multiple add-on processes to running in parallel to increase the system throughput if the AR invoices don't have to be in sequence as the original order. You will need to test out how many process is the best breed. The more processes of creating AR in parallel, the higher possibility of deadlock. You may need to implement a retry mechanism in case of deadlock, Please refer to this note 1444532 - SDK Deadlock Handling just without the global transaction. Please don't use the global transaction, which is not necessary in this case and will slow down the performance and increase the possibility of deadlock.
    3. Load a list of AR invoices object from xml file, then add it one by one.

    Here is an example of importing a list of BP from XML file for your reference, which you can easily replace with Documents object.
    The input structure of a list of AR in one xml file:

    <!--1st AR document-->









    <!--2nd AR document: Repeat the BO structure-->



    Sub ImportBPfromXmlFile(ByVal filePath As String)
                If FileIO.FileSystem.FileExists(filePath) = False Then
                    'file not exist
                    MsgBoxWrapper(String.Format("File doesn't exist.{0}", filePath))
                End If             
                Dim count As Integer = oCompany.GetXMLelementCount(filePath) - 1
                Dim oBP As SAPbobsCOM.BusinessPartners = Nothing
                Dim oBP2 As SAPbobsCOM.BusinessPartners = _
                For i As Integer = 0 To count
                    If oCompany.GetXMLobjectType(filePath, i) = SAPbobsCOM.BoObjectTypes.oBusinessPartners Then
                        'it is a BP object, then save it.
                        oBP = oCompany.GetBusinessObjectFromXML(filePath, i)
                        'Check if the BP exist
                        If oBP2.GetByKey(oBP.CardCode) Then
                            'already exist.update
                            'if the xml file contain multiple BP, then call update, 
                            'otherwise if only one BP in the xml, it is better to call updateFromXml()
                            lRetCode = oBP.Update
                            'it is a new BP, just add it
                            lRetCode = oBP.Add
                        End If
                        DIErrorHandler(String.Format("Importing BP {0}", oBP.CardCode))
                    End If
            Catch ex As Exception
            End Try
        End Sub 

    Kind Regards, Yatsea

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 06, 2018 at 10:39 AM


    the first point to check is if you are using the garbage collector to free the memory.

    I have more questions:

    - did you find blocked tables in the execution?

    - how many lines (average) do the documents have?

    Can you check how the database growth is configurated. Perhaps you are thinking the problem is in your code, and when you are adding this amount of information the database needs to grow and all the system is slow.

    Check the SQL Logs.

    Check the configuration of ldf file for this database: the growth and the free space. Shrink this file often.

    Kind regards


    Add comment
    10|10000 characters needed characters exceeded