Devart Forums

Posted: **Fri 23 Jan 2009 14:56**

I have created some simple loops in which I add 10K records using a transaction and without a transaction.

This I do with NexusDB (My current DB engine)
and Oracle using ODAC

We mean to implement oracle as an alternative to NexusDB because one of our customers insists out application data is stored using oracle.

So I am evaluating several possibilities to make my delphi app communicate with oracle. One of them is Devart ODAC.

Locate and scan of TOratable are fast enough. But insert/post and edit/post are way to slow. I have added a key field, and set the keyfield property accodingly. but no luck.

Below is some output of my testapp.

Since the difference between NexusDB inserts/edits and ODAC Edits/Inserts is so big, I guess I have missed something. Please put me on the right track for fixing this.

UPDATE 20090126
Finally managed to connect using OCI mode. But this does not change performance. (I didn't really expect it to make a big difference)

Regards

Hans

//\//\//\//\//\ SELECT ENGINE //\//\//\//\//\
TdmNexusDB selected
//\//\//\//\//\ CONNECT //\//\//\//\//\
//\//\//\//\//\ DROP TABLES //\//\//\//\//\
Found 4 tables in TdmNexusDB
Dropped UserGroups
Dropped Users
Dropped UsrGrpLnk
Dropped TestDataTable
//\//\//\//\//\ CREATE TABLES //\//\//\//\//\
Found 4 definitions in metadata in TdmNexusDB
UserGroups created
Users created
UsrGrpLnk created
TestDataTable created
//\//\//\//\//\ FILL TABLES //\//\//\//\//\
Created 10000 GUIDs in 31 ms
Added 10000 records in 672 ms
START TRANSACTION
Appended 10000 records in 140 ms
TRANSACTION COMITTED 10000 records 16 ms
Added 10000 records in 156 ms
//\//\//\//\//\ SCAN TABLES //\//\//\//\//\
sequential scan 20000 records took 94 ms
//\//\//\//\//\ LOCATE TEST //\//\//\//\//\
random locate 10000 records took 110 ms. Misses: 0
//\//\//\//\//\ RANDOM EDIT //\//\//\//\//\
random locate/edit 10000 records took 515 ms. Misses: 0
//\//\//\//\//\ DISCONNECT //\//\//\//\//\
//\//\//\//\//\ SELECT ENGINE //\//\//\//\//\

TdmODAC selected
//\//\//\//\//\ CONNECT //\//\//\//\//\
//\//\//\//\//\ DROP TABLES //\//\//\//\//\
Found 13 tables in TdmODAC
Dropped UserGroups
Dropped Users
Dropped UsrGrpLnk
Dropped TestDataTable
//\//\//\//\//\ CREATE TABLES //\//\//\//\//\
Found 4 definitions in metadata in TdmODAC
UserGroups created
Users created
UsrGrpLnk created
TestDataTable created
//\//\//\//\//\ FILL TABLES //\//\//\//\//\
Created 10000 GUIDs in 15 ms
Added 10000 records in 17516 ms
START TRANSACTION
Appended 10000 records in 78406 ms
TRANSACTION COMITTED 10000 records 0 ms
Added 10000 records in 78406 ms
//\//\//\//\//\ RANDOM EDIT //\//\//\//\//\
random locate/edit 10000 records took 317734 ms. Misses: 0

Posted: **Wed 28 Jan 2009 10:19**

You can try the TOraLoader component for inserting data. This component allows loading at maximum speed. But it supports only main data types: string, integer, double, date. It has two modes: Direct Path and DML Array. Direct Path mode is faster but table constraints are not checked in this mode.

Posted: **Wed 28 Jan 2009 12:11**

An other question is:
How did you create the records?
Appending into a TOraTable??
Executing an INSERT Statement?

Greetings
Jens

Posted: **Thu 29 Jan 2009 15:01**

Thx for the reply

I am inserting the records using the TOraTable component.

for i:=0 to 10000 do
begin
I call the TOraTable.Append
Fill some fields
Call Toratable.Post method
end

Currently I am not using the batch update mode.

What wonders me most is that when I surround the append loop with a starttransaction/commit call, the process slows down even more. In nexusdb, surrounding such an operation with startsaction usually speeds up the code by factor 5.

Using the TOraLoader will probably not be possible because there is no counterpart in nexusdb for this.

By the way: I find I am also missing SetRange, CancelRange, findkey and findnearest methods in TOraTable. Any ideas how to fix/implenet this?

Regards

Posted: **Fri 30 Jan 2009 13:30**

This is an Oracle peculiarity. Oracle server works slowly when there are many uncommited changes. So, the optimal way for Oracle is commiting each record separately (the TOraTable component does this automatically if you do not call StartTransaction).

TOraTable has the FindKey and FindNearest methods.

The SetRange and CancelRange methods does not exist in TOraTable. You can resolve this issue by linking the TOraTable component to TDataSetProvider and TClientDataSet. In that case you can use the SetRange and CancelRange methods of TClientDataSet.

Posted: **Tue 03 Feb 2009 10:26**

Ah yes, forgot to mention.
Indeed there are findkey and findnearest methods. But their parameter list is different.

TDataset.FindKey(const KeyValues: array of const);

TORATable.FindKey(const Keyvalues: array of variant);

I actually expected there was a rtl/vcl function to convert an array of const into an array of variant, but couldn't find one. So I came up with this solution to turn an array of const into an array of variant... Maybe someone has already tackled this and is willing to share it with me?

I have reservations whether booleans will work, and if findkey with dates will work correctly.

Code: Select all

procedure TORATableIntfObj.FillFindKeyValues(const KeyValues: array of const);
var i:integer;
begin
  SetLength(FFindKeyValues,length(KeyValues));
  for I := Low(KeyValues) to High (KeyValues) do
  begin
    case KeyValues[i].VType of
      vtInteger:    FFindKeyValues[i]:=KeyValues[i].VInteger;
      vtBoolean:    FFindKeyValues[i]:=KeyValues[i].VBoolean;
      vtChar:       FFindKeyValues[i]:=KeyValues[i].VChar;
      vtExtended:   FFindKeyValues[i]:=KeyValues[i].VExtended^;
      vtString:     FFindKeyValues[i]:=KeyValues[i].VString^;
//      vtPointer:    FFindKeyValues[i]:=KeyValues[i].VPointer;
      vtPChar:      FFindKeyValues[i]:=KeyValues[i].VPChar^;
//      vtObject:     FFindKeyValues[i]:=KeyValues[i].VObject;
//      vtClass:      FFindKeyValues[i]:=KeyValues[i].VClass;
      vtWideChar:   FFindKeyValues[i]:=KeyValues[i].VWideChar;
      vtPWideChar:  FFindKeyValues[i]:=KeyValues[i].VPWideChar^;
      vtAnsiString: FFindKeyValues[i]:=PAnsiString(KeyValues[i].VAnsiString)^;
      vtCurrency:   FFindKeyValues[i]:=KeyValues[i].VCurrency^;
      vtVariant:    FFindKeyValues[i]:=KeyValues[i].VVariant^;
//      vtInterface:  FFindKeyValues[i]:=KeyValues[i].VInterface;
      vtWideString: FFindKeyValues[i]:=PWideString(KeyValues[i].VWideString)^;
      vtInt64:      FFindKeyValues[i]:=KeyValues[i].VInt64^;
      else raise EORATableIntf.CreateFmt('%s.FillFindKeyValues Unsupported findkey parameter type [%d] array index [%d]',[ClassName,ord(KeyValues[i].VType),i]);
    end;
  end;
end;

{-
    }
function TORATableIntfObj.DoFindKey(const KeyValues: array of const): Boolean;
begin
  FillFindKeyValues(KeyValues);
  Result:=TORATable(FDataset).FindKey(FFindKeyValues);
end;{ .DoFindKey }

Regards - Hans

Posted: **Wed 11 Feb 2009 12:49**

The FindKey method of TOraTable has a parameter of type 'array of const' (same as BDE TTable). So the convertion is not needed.

Posted: **Wed 11 Feb 2009 13:42**

Well according to my code completion window, and my compiler errors, a const array of variants is expected. I am still evaluating, so I have no access to the ODAC sources to verify...

Regards

Devart Forums

TORaTable slow on updates and inserts

TORaTable slow on updates and inserts