Skip to Content
0

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

Apr 06 at 09:24 AM

95

avatar image
Former Member

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 :)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Yatsea Li
Apr 06 at 01:12 PM
1

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.

Company.GetBusinessOjectFromXml()
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:

<BOM>
<!--1st AR document-->
<BO>
<AdmInfo>

<Object>13</Object>
<Version>2</Version>
</AdmInfo>
<Documents>
<row>
<CardCode>...

</row>
....

<DocumentLines>

<row>

<ItemCode>...

</row>

</DocumentLines>

</BO>

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

<BO>
....
</BO>
....

</BOM>

Sub ImportBPfromXmlFile(ByVal filePath As String)
        Try
            If FileIO.FileSystem.FileExists(filePath) = False Then
                'file not exist
                MsgBoxWrapper(String.Format("File doesn't exist.{0}", filePath))
                Return
            End If             
            Dim count As Integer = oCompany.GetXMLelementCount(filePath) - 1
            Dim oBP As SAPbobsCOM.BusinessPartners = Nothing
            Dim oBP2 As SAPbobsCOM.BusinessPartners = _
                oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oBusinessPartners)
            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
                    Else
                        'it is a new BP, just add it
                        lRetCode = oBP.Add
                    End If
                    DIErrorHandler(String.Format("Importing BP {0}", oBP.CardCode))
                End If
            Next
        Catch ex As Exception
            MsgBoxWrapper(ex.Message)
        End Try
    End Sub 

Kind Regards, Yatsea

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Yatsea,

Ok, thank you for your info, will try to test that.

I cannot open the SAP Notes link

http://service.sap.com/sap/support/notes/1444532

0
Agustin Marcos Cividanes Apr 06 at 10:39 AM
0

Hi

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

Agustin

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Agustin,

- did you find blocked tables in the execution? no, doesn't have any blocking

- how many lines (average) do the documents have? small, only 1-2 lines per document

I've debugging the code, & debug from SQL also, the longest process in each entry is in document.add DI API SAP Business One, not in other SQL query.

Hmm..., database growth for mdf or ldf ? it's autogrowth 10%, I think it's good enough ?

0

Hi

are you using Transactions in your code?

Check the support note 2617060 - Deadlocks/Slow Performance Issues

Kind regards

Agustín

1