PeopleSoft Learning

Inserting rows into record using Peoplecode with better performance

When inserting rows using Peoplecode, you can either use the Insert method with a record object, or create a SQL Insert statement using the SQL object.

• If you're doing a single insert, use the Record Insert method

• If you're in a loop, and therefore calling the insert more than once, use the SQL object.

Why? Because the SQL object uses dedicated cursors and if the database you're working with supports it, bulk insert.

A dedicated cursor means that the SQL only gets compiled once on the database, so People Tools only looks for the meta-SQL once. This can mean better performance.

For bulk insert, inserted rows are buffered and only sent to the database server when the buffer is full or a COMMIT occurs. This cuts down on the number of roundtrips to the database. Again, this can mean better performance.

The following is an example of using the record insert method:

&REC = CreateRecord (Record.GREG);

&REC.DESCR.Value = "Y" &I;

&REC.EMPLID.Value = &I;

&REC.Insert();

The following is an example using a SQL object for doing an insert:

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

&REC = CreateRecord(Record.GREG);

&SQL.BulkMode = True;

For &I = 1 to 10

&REC.DESCR.Value = "Y" &I;

&REC.EMPLID.Value = &I;

&SQL.Execute(&REC);

-Ganesh A.M