AllocObject gets a lot of time in loop

[bas]

Hi All,

I need to fill Table param with over 2000 elements.
And I have the following situation:

Code: Select all

with FOraDataSet as TOraSql do
begin
.......
// allocate memory for table type
with ParamByName('ip_tb_futrate').AsArray do
begin
  OCISvcCtx:= FOraSession.OCISvcCtx;
  AllocObject(TThreadFlin(FOwner).getSchemaName+'.T_TB_FUTRATE');
  ParamByName('ip_tb_futrate').Clear;
end;
.......
for i:=0 to 2000 do
begin
  with ParamByName('ip_tb_futrate').AsArray do
  begin
    with ItemAsObject[v_cnt] do
    begin
      OCISvcCtx:= FOraSession.OCISvcCtx;
      // allocate memory for object type; get a lot of time
      AllocObject(TThreadFlin(FOwner).getSchemaName+'.T_OB_FUTRATE');
      // end of get a lot of time
      AttrAsFloat['IND']          := v_cnt+1;
      ........
    end;
  end
end;
.......
end;

In row

Code: Select all

AllocObject(TThreadFlin(FOwner).getSchemaName+'.T_OB_FUTRATE');

I have the big delay (about 5-7 sec. per 2000 elements). It is very critical code for us. If I remove this row, execution time of loop is ok.
How to resolve this problem? Can I allocate memory one time for all elements of tables?

[Guest]

Hi All,

Okay but i dont that will suit everyone.

[Alex]

No, you can't allocate memory at once for all elements. Please describe your task more precisely maybe we will suggest you some other solutions. It will be better if you send us complete sample to demonstrate it and include script to create server objects.

[bas]

I would like to send PL/SQL TABLE OF OBJECT to procedure where this PL/SQL Table is inserted to physical table faster then when we insert row by row.

My object type is:

Code: Select all

create or replace 
TYPE T_OB_FUTRATE IS OBJECT( IND			NUMBER(10),		-- sequence number 
	 			  	 		 NF_ID 	   		NUMBER(10), 	-- Fut_ID        : extended; // fut_id : extended; 
  					   	   	 NF_DEALRATE 	NUMBER(11,6), 	-- Last_Price    : extended; // 
  					   		 NF_DEALVOL  	NUMBER(10),		-- Last_Size     : extended; // 
  					   	   	 DF_LINETIME 	DATE			-- Line_Time 	 : TDateTime;// linetime:TDateTime;) 
						   );

My table type is

Code: Select all

create or replace TYPE T_TB_FUTRATE AS TABLE OF T_OB_FUTRATE;

My function is

Code: Select all

CREATE OR REPLACE FUNCTION put_rates( [b]ip_tb_futrate[/b]	T_TB_FutRate, 
					ip_futrate_cnt 	Int, 
				  	op_Serr OUT VARCHAR2
				   ) RETURN Integer
IS				   
BEGIN
  RETURN 0;
END;
/

When I send this param to function I need to allocate memory for Table Type and then for each Object Param.

My test programm is:

test.pas:

Code: Select all

unit u_debug;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DBAccess, OdacVcl, DB, Ora;

type
  TForm1 = class(TForm)
    Button1: TButton;
    OraSession: TOraSession;
    ConnectDialog1: TConnectDialog;
    Memo: TMemo;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
const
  C_ONE_SECOND = 1/(24*60*60);
var
  FOraDataSet : TOraSql;
  v_cnt, i         : Integer;
  v_time        : TDateTime;
begin
  FOraDataSet     := TOraSql.Create(nil);
  with FOraDataSet as TOraSql do
  begin
    Session := OraSession;
    SQL.Clear;
    // add sql
    begin
      SQL.Add('BEGIN :rop:=Put_Rates(:ip_tb_futrate, :ip_futrate_cnt, :op_Serr); END;');
      ParamByName('rop').DataType      := ftInteger;
      ParamByName('rop').ParamType     := ptOutput;
      ParamByName('op_serr').DataType  := ftString;
      ParamByName('op_serr').ParamType := ptOutput;
    end;
  end;
  OraSession.Connect;
////////////////////////////////////////////////////////////////////////////
  v_cnt := 0;

  with FOraDataSet as TOraSql do
  begin
       // debug - begin
         v_time := now;
       // debug - end
       // initialize array params
       with ParamByName('ip_tb_futrate').AsArray do
       begin
         OCISvcCtx:= OraSession.OCISvcCtx;
         AllocObject('T_TB_FUTRATE');
         ParamByName('ip_tb_futrate').Clear;
       end;
       // debug - begin
       Memo.Lines.Add('Init Collect time is "'+Format('%f', [(now-v_time)/C_ONE_SECOND])+'"'+'; Cnt='+IntToStr(v_cnt));
       v_time := now;
       // debug - end
       // set array params
       for i := 1 to 2000 do
       begin
         with ParamByName('ip_tb_futrate').AsArray do
         begin
                 // set ip_tb_futrate param
                 with ItemAsObject[v_cnt] do
                 begin
                   OCISvcCtx:= OraSession.OCISvcCtx;
///////////////////// get a lot of time //////////////////////////
{get a lot of time}AllocObject('T_OB_FUTRATE');{get a lot of time}
///////////////////// end of get a lot of time ///////////////////
                   AttrAsFloat['IND']           := v_cnt+1;
                   AttrAsFloat['NF_ID']         := 251;
                   AttrAsFloat['NF_DEALRATE']   := 100+i;
                   AttrAsFloat['NF_DEALVOL']    := 200+i;
                   AttrAsDateTime['DF_LINETIME']:= now;
                 end;
         end;
         inc(v_cnt);
       end; // while FRateData.getItem(v_DataArray) = 0 do
       ParamByName('ip_futrate_cnt').AsInteger := v_cnt;
       // execute stored proc
       // debug - begin
       Memo.Lines.Add('Loop time is "'+Format('%f', [(now-v_time)/C_ONE_SECOND])+'"'+'; Cnt='+IntToStr(v_cnt));
       v_time := now;
       // debug - end
       If Not Prepared Then Prepare;
       Execute;
       // debug - begin
       Memo.Lines.Add('Exec time is "'+Format('%f', [(now-v_time)/C_ONE_SECOND])+'"'+'; Cnt='+IntToStr(v_cnt));
       // debug - end
  end; // with FOraSql do
end;

end.

test.dfm:

Code: Select all

object Form1: TForm1
  Left = 192
  Top = 107
  Width = 750
  Height = 480
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 32
    Top = 48
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Memo: TMemo
    Left = 32
    Top = 80
    Width = 321
    Height = 185
    Lines.Strings = (
      'Memo')
    TabOrder = 1
  end
  object OraSession: TOraSession
    ConnectDialog = ConnectDialog1
    Left = 32
    Top = 16
  end
  object ConnectDialog1: TConnectDialog
    Retries = 3
    Caption = 'Connect'
    UsernameLabel = 'User Name'
    PasswordLabel = 'Password'
    ServerLabel = 'Server'
    ConnectButton = 'Connect'
    CancelButton = 'Cancel'
    LabelSet = lsEnglish
    Left = 64
    Top = 16
  end
end

[bas]

Alex or Anyone, Do you have any suggestions to decrease this delay?