Bulk Insert in PeopleSoft

When you have applications or process that inserts large number of rows into database tables, you might have noticed a lag in the performance of the application\process. Apart from the normal db insert time, the major time consuming part is the presenting of data to the database (database trips).

PeoplesSoft offers some mechanism to further fine tune the performance in case of bulk inserts. For this PeopleSoft has introduced a method called Bulk Mode/Bulk Insert.

If you are using Peoplecode to insert the data, then you can use the BulkMode property of the SQL class. To use this you need to create a sql statement and set the BulkMode property as true. Now each time you use the execute statement, PeopleSoft is not going to present the data to the database. Instead, it will cache the data in the buffer until the sql is closed or the buffer is full. Now peoplesoft will present the entire data to the database in single stretch, thus reducing the data base trips and thereby increasing the performance of the application.

But not all databases supports bulk inserts, major db’s like Oracle, DB2 & MS SQL Server will support bulk inserts. Incase if your db will not support bulk insert, then PeopleSoft will ignore this property and processing will happen as normal (with each trip to db with each execute statement).

Below is one of the example code piece from PeopleBooks demonstrating the usage of BulkMode.

Local SQL &SQL;

Local array of Record &RECS;

/* Set up the array of records.  */

   .  .  .

   /* Create the SQL object open on an insert */

   /* statement, and unbound.*/

   &SQL = CreateSQL("%Insert(:1)");

   /* Try for bulk mode.  */

   &SQL.BulkMode = True;

   /* While the array has something in it… */

   While &RECS.Len

      /* Insert the first record of the array, */

      /* and remove it from the array.  */

      If not &SQL.Execute(&RECS.Shift) then

         /* A duplicate record found, possibly */

         /* in bulk mode.  There is no way to  */

         /* tell which record had the problem. */

         /* One approach to recovery is to fail*/

         /* the transaction and retry it with a*/

         /* process that does only one record  */

         /* at a time, that is, doesn’t use    */

         /* bulk mode.*/

         .  .  .;

      End-If;

End-While;

/* If buffer is not filled yet, db insert will happen at the below step. */

   &SQL.Close();

Now we have seen how to handle bulk inserts in applications using peoplecode. Now if you are running an application engine process to insert data, how can you handle bulk inserts?

PeopleSoft has provided a solution for the same as well. There is a property in the application engine SQL action called “Reuse Statement”. You should select the property to “Bulk Insert” in case your sql action is going to insert huge number of rows. This will start buffering data and presenting it as a single set as we have seen in the BulkMode property of the SQL object.