ODAC DML Array vs. Database For Loop (SQL INSERT)

[HeinzKetchup]

Hi,

I'm trying to figure out how to increase performance performing SQL inserts.
Using DML Arrays in ODAC version 8.1.4 to me yields poor performance, when based on benchmarks I've been searching through google says it should be otherwise.
Basically < 10 seconds for the amount of records I want inserted.

Am I missing something here? Or is this really the level of performance from using the ODAC library? I don't know how well tuned the interface is between ODAC and Oracle 10i.

Any insight would be greatly appreciated.

// Testing Box Memory
PGA Memory 2.5 GB
SGA Memory 3 GB
Temp Table Space 1.15 GB


I have a SQL Block in Oracle like this:

// This takes 15 ~ 16 seconds
BEGIN
FOR MYVAL IN 1..204000 LOOP
INSERT INTO MYTABLE VALUES (SYSDATE, MYVAL, 'TEST', MYVAL);
END LOOP;
COMMIT;
END;

// This takes 40 seconds
TOraSQL *oraQ = new TOraSQL(NULL);
oraQ->SQL->Text = "INSERT INTO MYTABLE (DATA_TIMESTAMP, RUID, DTYPE, DVALUE) VALUES(:dt, :ruid, :dtype, :dvalue)";
oraQ->ParamByName("dt")->ParamType = ptInput;
oraQ->ParamByName("dt")->DataType = ftDateTime;
oraQ->ParamByName("ruid")->ParamType = ptInput;
oraQ->ParamByName("ruid")->DataType = ftInteger;
oraQ->ParamByName("dtype")->ParamType = ptInput;
oraQ->ParamByName("dtype")->DataType = ftString;
oraQ->ParamByName("dvalue")->ParamType = ptInput;
oraQ->ParamByName("dvalue")->DataType = ftFloat;
oraQ->Prepare();
oraQ->ArrayLength = 203000;

for (unsigned int k = 0; k < 203000; k++)
{
oraQ->ParamByName("dt")->ItemAsDateTime[k+1] = Now();
oraQ->ParamByName("ruid")->ItemAsInteger[k+1] = k;
oraQ->ParamByName("dtype")->ItemAsString[k+1] = "Blank";
oraQ->ParamByName("dvalue")->ItemAsFloat[k+1] = 1.0;
}

oraQ->Execute(203000);

[AlexP]

hello,

To increase performance when loading large amounts of data instead of TOraSQL, you should use TOraLoader, e.g. providing the following code in the onGetColumnData event:

Code: Select all

  
begin
if Row <= 203000 then begin
      case Column.Index of
         0: Value := Now();
         1: Value := Row;
         2: Value := 'Blank';
         3: Value := 1.0;
      else
         Value := Null;
      end;
   end
   else
      isEOF := True;
end;

OraLoader1.Load();

The performance when using this method is 3 times higher than when using the PL/SQL block you have provided.

[HeinzKetchup]

Awesome! Thank you! I will give it a try and post a results follow up.
Much Appreciated for the assistance!

[HeinzKetchup]

As a follow up.
With the TOraLoader method, loading in the data via "oraL->OnPutData = &LdrPutData;", with 203,000 records.

On average, I'm getting data in around the 27 seconds mark
Definitely faster than 40 seconds.

But I will take any speed increase where I can get it.

Thanks again!
Cheers,
Heinz

[AlexP]

hello,

On our test samples, the difference in data loading time between using TOraSQL and TOraLoader is 20 times: when using TOraSQL, the loading lasts ~20 seconds, and when using TOraLoader - ~1 second.
To increase speed, you can also experiment with loading modes in TOraLoader.LoadMode (Direct, DML)