Home » Developer & Programmer » Precompilers, OCI & OCCI » Performance issue with EXEC SQL OPTION SET (Oracle 10g R2 on Unix based system)
Performance issue with EXEC SQL OPTION SET [message #525478] Mon, 03 October 2011 07:34
ppeitersen
Messages: 1
Registered: October 2011
Location: Denmark
Junior Member
Hi, I am currently developing an application in c, which have to insert data into a table containing a varray column.
I am using the Pro*c precompiler incl. OTT for the object types.
The function inserting data into the table with a varray column i very simple. It contains only around 0.05% of the total number of executed codelines, and yet it consumes 6% of the processing time.
The function is just one of severeal, which inserts data in various tables, but the only one consuming noticable time.

Below I have pasted the timing I did on the function and the OBJECT/COLLECTION releated calls used:

30Sep11 09:19:39 Sourcefile.c/_Function (9776): Timer results:
FunctionTimerTotal <2906152> µSec
CollectionTimersTotal <2792309> µSec
ResetTimerTotal < 18993> µSec
SetTimerTotal <2720820> µSec
AppendTimerTotal < 52496> µSec

The SetTimerTotal is time used by EXEC SQL OBJECT SET alone.
The dataset consists of 13 rows, each having a collection of 56 objects. Each object have 4 fields set.

This is how the objects are set:
EXEC SQL OBJECT SET
Field1,
Field2,
Field3,
Field4
OF :objType[intObjectIndex]:objType_ind[intObjectIndex]
TO :intValue1,
:intValue2,
:intValue3,
:intValue4;

This is how the objects are appended to the collection:
EXEC SQL FOR :intRecordCount
COLLECTION APPEND
:objType:objType_ind
TO
:objArray[intRecordIndex];

Appending does not take much time as you can see from the measurement - only 52.5 mSec, compared to the EXEC SQL OBJECT SET which consumes an incredible 2Sec 720 mSec

How can that be? That will kill the application I am working on or force me to work around it, using additional tables and PL/SQL functions to get the column populated, which will not make the application easier for others to understand or work with.
The process executed here, is just one of several thousands, and have a fairly small amount data input/output. Other will have a lot more to do and a lot more output, which will be even more time consuming.

Note that the output table is populated correctly, so this post only has to do with processing time.

Am I doing something wrong?
Any suggestions?

[Updated on: Tue, 04 October 2011 01:50]

Report message to a moderator

Previous Topic: Pro*c Error
Next Topic: pro*c Error "Invalid initialization"
Goto Forum:
  


Current Time: Thu Mar 28 12:20:33 CDT 2024