Memory consumption inserting records into a table

[rburgstaler]

Inserting large numbers of records into a table using TSmartQuery causes large amounts of memory to be consumed. Depending on the number of records to be put into the table, the memory can grow without limit. Once the smartquery has been closed, the memory used is not released. I found that the memory is not in fact released until FreeOCI() is called during application finalization called in OraCalls.pas.

The following is sample code that can be setup to illustarte the problem:

Code: Select all

CREATE TABLE TESTTABLE(
  OBJECTID     NUMBER(9),
  LIDAR_COLL   DATE,
  LIDAR_DEL    DATE,
  LINE_NUMBE   VARCHAR2(254),
  LINE_NAME    VARCHAR2(100),
  STRA         VARCHAR2(254),
  STRB         VARCHAR2(254),
  STR_DESIGN   VARCHAR2(254),
  LOC_ZONE     VARCHAR2(254),
  ALERT_NUM    VARCHAR2(254),
  LIDAR_DIST   NUMBER(19,11),
  HEIGHT       NUMBER(19,11),
  HEIGHT_85    FLOAT(126),
  DENSITY      FLOAT(126),
  AREA         FLOAT(126),
  LAT          NUMBER(19,11),
  LON          NUMBER(19,11),
  SEL_ID       NUMBER(9),
  SEL_FLAG     VARCHAR2(50),
  TEMP_FLAG    VARCHAR2(10),
  GEOM         SDO_GEOMETRY,
  WORKZONE     VARCHAR2(3),
  COMPANY      VARCHAR2(30),
  IMPORTDATE   DATE,
  FALLINHEIGHT NUMBER,
  RECID        NUMBER);

//Sample code to illustrate the problem. Caling TestMemIssue(5000) will cause the memory
//on my computer to grow by 30 MB

Code: Select all

procedure TMainForm.TestMemIssue(ACount: Integer);
var
  lInc: Integer;
  x: Integer;
  lLocation: Double;
  lOraSession: TOraSession;
  lSmartQuery: TSmartQuery;
begin
  lOraSession:=TOraSession.Create(nil);
  lSmartQuery:=TSmartQuery.Create(nil);
  try
    lOraSession.LoginPrompt:=false;
    lOraSession.Password:=fPassword;
    lOraSession.Username:=fUserID;

    lOraSession.Options.Net:=false;
    lOraSession.Server:=fOracleServerAddress;
    lOraSession.Open;
    lOraSession.AutoCommit := false;
    fTransIncCount:=0;
    fInTransAction:=False;

    lSmartQuery.Session:=lOraSession;
    lSmartQuery.ObjectView:=true;
    lSmartQuery.Debug := False;

    lSmartQuery.close;
    lSmartQuery.SQL.clear;
    lSmartQuery.SQL.add('select T.*, T.ROWID from TESTTABLE T where 1=0');  // to get the fields
    lSmartQuery.open;

    lInc:=0;
    lOraSession.StartTransaction;
    while lInc<ACount do
    begin
      lSmartQuery.Append;
      with lSmartQuery do
      begin
        FieldByName('LIDAR_COLL').AsDateTime := 39644;
        FieldByName('LIDAR_DEL').AsDateTime := 39762;
        FieldByName('LINE_NUMBE').AsString := '246622';
        FieldByName('LINE_NAME').AsString := 'Aman - Keemmer';
        FieldByName('STRA').AsString := '232-274';
        FieldByName('STRB').AsString := '232-275';
        FieldByName('STR_DESIGN').AsString := 'AEP_Number';
        FieldByName('LOC_ZONE').AsString := 'P1';
        FieldByName('ALERT_NUM').AsString := '246622-P1-15069';
        FieldByName('LIDAR_DIST').AsFloat := 58.5629;
        FieldByName('HEIGHT').AsFloat := 48.5235;
        FieldByName('HEIGHT_85').AsFloat := 47.4409;
        FieldByName('DENSITY').AsFloat := 84.62;
        FieldByName('AREA').AsFloat := 110.384;
        FieldByName('LAT').AsFloat := 39.3819326378;
        FieldByName('LON').AsFloat := -81.1729436286;
        FieldByName('WORKZONE').AsString := 'P1';
        FieldByName('IMPORTDATE').AsDateTime := 39822.3565162037;
        FieldByName('RECID').AsInteger := 0;
      end;

      lSmartQuery.GetObject('GEOM').AttrAsInteger['SDO_GTYPE']:=2003;
      with lSmartQuery.GetObject('GEOM').AttrAsArray['SDO_ELEM_INFO'] do
      begin
        ItemAsInteger[0]:=1;
        ItemAsInteger[1]:=3;
        ItemAsInteger[2]:=1;
      end;

      lLocation:=45;
      with lSmartQuery.GetObject('GEOM').AttrAsArray['SDO_ORDINATES'] do
      begin
        for x:=0 to 71 do
        begin
          ItemAsFloat[x]:=lLocation;
          lLocation:=lLocation+0.0000001;
        end;
      end;

      lSmartQuery.Post;
      inc(lInc);
      if lInc mod 500=0 then
      begin
        lOraSession.Commit;
        lOraSession.StartTransaction;
      end;
    end;
    lOraSession.Commit;

    lOraSession.Close;
  finally
    lSmartQuery.Free;
    lOraSession.Free;
  end;
  ShowMessage('done');
end;

Other observations I have made.
1.) Calling: TestMemIssue(5000) and then immediately calling TestMemIssue(5000) again seems to re-use the same memory and does not increase memory.
2.) Calling: TestMemIssue(5000) and then immeidately calling TestMemIssue(10000) will cause the memory to grow very large.

Am I doing something incorrect to have so much memory being used? I am creating data conversion applications that are inserting large numbers of records using this sort of process and I am coming up with EOutOfMemory exceptions. What can I do to fix this or is this a bug in ODAC?

[Plash]

TSmartQuery holds in the memory all records you have inserted. So memory consumption is very large.

You should use INSERT statement directly. In this case no records are holded in memory. For example:

Code: Select all

    lSmartQuery.SQL.Clear;
    lSmartQuery.SQL.Add('INSERT INTO TESTTABLE(OBJECTID, LIDAR_COLL, ...)');
    lSmartQuery.SQL.Add('VALUES (:OBJECTID, :LIDAR_COLL, ...)');
 
    lSmartQuery.ParamByName('GEOM').AsObject.AllocObject(lOraSession.OCISvcCtx, 'MDSYS.SDO_GEOMETRY'); 

    while lInc<ACount do
    begin 
      lSmartQuery.ParamByName('LIDAR_COLL').AsDateTime := 39644;
      lSmartQuery.ParamByName('LIDAR_DEL').AsDateTime := 39762;
      ...
      lSmartQuery.ParamByName('GEOM').AsObject.AttrAsInteger['SDO_GTYPE']:=2003; 
      with lSmartQuery.ParamByName('GEOM').AsObject.AttrAsArray['SDO_ELEM_INFO'] do 
      begin
        ItemAsInteger[0]:=1;
        ItemAsInteger[1]:=3;
        ItemAsInteger[2]:=1;
      end;
      ...
      lSmartQuery.Execute;
    end;

You can generate INSERT statement using design-time SQL generator of TSmartQuery (open TSmartQuery editor and go to the SQL Generator tab).

[rburgstaler]

I tried doing the suggested approach to work around the memory consumption issue. It does not have the same memory issue and appears to be quicker.

However, I do not understand why the method that I was using was causing so much memory to be used that would not be freed, even after the TSmartQuery that was instantiated was freed. Is this a possible bug in ODAC or is it an OCI issue? We have quite a bit of code that was written this way that will most likely need to be re-written to work around the memory issue.

Either way.. thanks for the help. I now can work around this.

[Plash]

When you close the TSmartQuery component memory is freed partially (-10 MB in the test project). But probably some memory allocated by OCI is not freed. Maybe it is related to OBJECT (SDO_GEOMETRY) field used in the query because memory for objects is allocated by OCI. Try to run the test with the SDO_GEOMETRY field excluded from the SELECT statement.