SAP PI to Oracle with batch insert – Improved performance

SAP PI to Oracle with batch insert – Improved performance

We were implementing a bulk-data transfer from SAP into an Oracle database. Easily upwards of 15 000 materials were being uploaded to the database via a stored procedure (company policy). Normally we like this approach because it decouples PI from the database’s underlying table structure, but we were getting terrible performance.
 
In testing, the entire workflow took almost 2 hours. Whilst this in itself wasn’t an issue (the process runs in the middle of the night), it was unnecessary load on both systems, and the extended duration put the process at increased risk of failure (e.g. due to network issues).
 
Keen to improve this, we looked at PI’s “batch insert” capabilities. In order to maintain the decoupling, and in order to protect the destination tables, we created an interface table to temporarily contain the material data, and a procedure that safely updated the destination table.
 
Testing showed a 30-to-60-fold performance improvement during the PI-DB exchange, and the entire process ended up taking just 10 minutes.
 
Author: Edwin


Working at i8c
i8c is a system integrator that strives for an informal atmosphere between its employees, who have an average age of approx 30 years old. We invest a lot of effort in the professional development of each individual, through a direct connection between the consultants and the management (no multiple layers of middle management). We are based in Kontich, near Antwerp, but our customers are mainly located in the triangle Ghent-Antwerp-Brussels and belong to the top 500 companies in Belgium (Securex, Electrabel, UCB, etc…).
Quality Assurance
i8c is committed to delivering quality services and providing customer satisfaction. That’s why we invested in the introduction of a Quality Management System, which resulted in our ISO9001:2000 certification. This guarantees that we will meet your expectations, as a reliable, efficient and mature partner for your SOA & integration projects. i8c also signed the eTIC Benelux charter, which proves our commitment to ethical service delivery.