Speed MemDataTable

Hi

in my app i use many Reports.
(I am still in the process of moving to RemObjects.)

I use following code to prepare 5 MemDataTable as Datasource for my Report.
If i call the Report it takes about 30 Seconds until the Report shows up…
( In my Offline App i used FibPlus with Firebid and there the same to 2,5 Sec )
There are about 110 Rows in the MainTable and very few on the Detail Tables,
There almost no BLOBS ( Textfileds ) in the Tables.
Is this normal?

Thank you for any Tipps
Manfred

procedure TDM_hotel.print_all_detail(Sender: TObject);
var
  lDynWhere: TDAWhereBuilder;
  e1,e2,e3: TDAWhereExpression;
begin
 if camp_ident_part = '' then
  begin
    ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_camps,'SELECT * FROM CAMPS');
    with DM_reports.tbl_rep_camps do
    begin
      Close;
      lDynWhere := DynamicWhere;
      lDynWhere.Clear;
      lDynWhere.Expression := lDynWhere.NewBinaryExpression('','ZONE_NO',dboGreater,'0');
      IndexFieldNames := 'CITY';
      Open;
    end;
  end
  else
  begin
    ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_camps,'SELECT * FROM CAMPS');
    with DM_reports.tbl_rep_camps do
    begin
      Close;
      lDynWhere := DynamicWhere;
      lDynWhere.Clear;
      e1 := lDynWhere.NewBinaryExpression('','C_NO',dboEqual,camp_ident_part);
      e2 := lDynWhere.NewBinaryExpression('','ZONE_NO',dboGreater,'0');
      lDynWhere.Expression := lDynWhere.NewBinaryExpressionList([e1,e2], dboAnd);
      IndexFieldNames := 'CITY';
      Open;
    end;
  end;

  ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_default,'SELECT * FROM HOTEL');
  with DM_reports.tbl_rep_default do
  begin
    Close;
    lDynWhere := DynamicWhere;
    lDynWhere.Clear;
//    lDynWhere.Expression := lDynWhere.NewBinaryExpression('','ID',dboEqual,frm_hotel.ID.Field.DisplayText);
    IndexFieldNames := 'NAME';
    Open;
  end;

  ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_default_c,'SELECT * FROM HOTEL_C');
  with DM_reports.tbl_rep_default_c do
  begin
    Close;
    lDynWhere := DynamicWhere;
    lDynWhere.Clear;
    lDynWhere.Expression := lDynWhere.NewBinaryExpression('','STAT_DEL',dboEqual,'0');
    IndexFieldNames := 'C_NAME';
    Open;
  end;

  ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_default_d,'SELECT * FROM HOTEL_D');
  with DM_reports.tbl_rep_default_d do
  begin
    Close;
    lDynWhere := DynamicWhere;
    lDynWhere.Clear;
    lDynWhere.Expression := lDynWhere.NewBinaryExpression('','STAT_DEL',dboEqual,'0');
    IndexFieldNames := 'D_DATE';
    Open;
  end;

  ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_default_dd,'SELECT * FROM HOTEL_DD');
  with DM_reports.tbl_rep_default_dd do
  begin
    Close;
    lDynWhere := DynamicWhere;
    lDynWhere.Clear;
    lDynWhere.Expression := lDynWhere.NewBinaryExpression('','STAT_DEL',dboEqual,'0');
    IndexFieldNames := 'BIBLE_TYPE';
    Open;
  end;

  report_file := 'report_dist.fr3';
  report_var_name := '''' + frm_hotel.Caption +'''';
  DM_reports.print_dist(Sender);
end;

according to your code you do following:

  1. open tbl_rep_camps as “SELECT * FROM CAMPS”
  2. close tbl_rep_camps
  3. open tbl_rep_camps as “SELECT FROM CAMPS WHERE ZONE_NO > 0” and sort it with CITY field ;

as a result, you do 10 requests to Relativity. you can optimize and fill all 5 tables in one request.
it can be done via RDA.FillWithDASql method:

procedure FillWithDASql(aTables: array of TDADataTable; aSQLs: array of String; aParameters: array of DataParameterArray);

or via RDA.Fill method:

procedure Fill(aTables: array of TDADataTable; aSaveCursor: boolean = False; aIncludeSchema: boolean = False; aAppendMode:Boolean = False); overload; override;

usage:

RDA.FillWithDASql([table1, table2... tableN],[sql1, sql1... sqlN],[params1, params2,...paramsN]);
RDA.Fill([table1, table2... tableN]);

I think you mean this part:

  if camp_ident_part = '' then
  begin
    ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_camps,'SELECT * FROM CAMPS');
    with DM_reports.tbl_rep_camps do
    begin
      Close;
      lDynWhere := DynamicWhere;
      lDynWhere.Clear;
      lDynWhere.Expression := lDynWhere.NewBinaryExpression('','ZONE_NO',dboGreater,'0');
      IndexFieldNames := 'CITY';
      Open;
    end;
  end
  else
  begin
    ClientDataModule.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_camps,'SELECT * FROM CAMPS');
    with DM_reports.tbl_rep_camps do
    begin
      Close;
      lDynWhere := DynamicWhere;
      lDynWhere.Clear;
      e1 := lDynWhere.NewBinaryExpression('','C_NO',dboEqual,camp_ident_part);
      e2 := lDynWhere.NewBinaryExpression('','ZONE_NO',dboGreater,'0');
      lDynWhere.Expression := lDynWhere.NewBinaryExpressionList([e1,e2], dboAnd);
      IndexFieldNames := 'CITY';
      Open;
    end;
  end;

But this is just one or the other:

if camp_ident_part = ‘’ then

I will try your Tip, thank you :smile:
Shalom
Manfred

Did search a bit in the Wiki and the Forum…
( Is there any Sample Documentation on this? )
Found This, but i have some Problems with the Parameters…

procedure FillWithDASql(aTable: TDADataTable; aSQL: string; aParameters: DataParameterArray)

Do you mean something like this?
Somewhere is a Problem in this…,

  ClientDataModule.RemoteDataAdapter.FillWithDASql(
    [DM_reports.tbl_rep_camps, DM_reports.tbl_rep_default,
     DM_reports.tbl_rep_default_c,
     DM_reports.tbl_rep_default_d, DM_reports.tbl_rep_default_d],
    ['SELECT * FROM CAMPS', 'SELECT * FROM HOTEL',
     'SELECT * FROM HOTEL_C',
     'SELECT * FROM HOTEL_D', 'SELECT * FROM HOTEL_DD'],
    ['ZONE_NO > 0', 'STAT_DEL = 0',
     'STAT_DEL = 0',
     'STAT_DEL = 0','STAT_DEL = 0']);

I’ve already shown how to create DataParameterArray at SQL: Select With WHERE clause :

//  par: DataParameterArray;
//  dp: DataParameter;

  par := DataParameterArray.Create;
  dp := par.Add;
  dp.Name := UTF8Encode('param1');
  dp.Value := 1;
  lRemoteDataAdapter.FillWithDASql(lTable,DASQL1, par);

here you need to pass array of DataParameterArray as 3rd parameter of FillWithDASql:

ClientDataModule.RemoteDataAdapter.FillWithDASql(
    [DM_reports.tbl_rep_camps, DM_reports.tbl_rep_default,
     DM_reports.tbl_rep_default_c,
     DM_reports.tbl_rep_default_d, DM_reports.tbl_rep_default_d],
    ['SELECT * FROM CAMPS', 'SELECT * FROM HOTEL',
     'SELECT * FROM HOTEL_C',
     'SELECT * FROM HOTEL_D', 'SELECT * FROM HOTEL_DD'], 
    [par1, par2, par3, par4, par5]);

Hi again…

Still struggling with the Parameters
I get just the unfiltered Data

The Normal SQL for CAMPS should look like this:
SELECT * FROM CAMPS WHERE ( CITY IS NOT NULL AND C_NO = ‘I47710’) ORDER BY CITY

with param_ar2 i just try in he moment to filter for C_NO = ‘I47710’

  param_ar1 := DataParameterArray.Create;
  par_ar1 := param_ar1.Add;
  par_ar1.Name := UTF8Encode('REG_NO');
  par_ar1.Value := 3;

  param_ar2 := DataParameterArray.Create;
  par_ar2 := param_ar2.Add;
  par_ar2.Name := UTF8Encode('C_NO');
  par_ar2.Value := 'I47710';

  param_ar3 := DataParameterArray.Create;
  par_ar3 := param_ar3.Add;
  par_ar3.Name := UTF8Encode('STAT_DEL');
  par_ar3.Value := 0;

  param_ar4 := DataParameterArray.Create;
  par_ar4 := param_ar4.Add;
  par_ar4.Name := UTF8Encode('STAT_DEL');
  par_ar4.Value := 0;

  param_ar5 := DataParameterArray.Create;
  par_ar5 := param_ar5.Add;
  par_ar5.Name := UTF8Encode('STAT_DEL');
  par_ar5.Value := 0;

  ClientDataModule.RemoteDataAdapter.FillWithDASql(
    [DM_reports.tbl_rep_camps, DM_reports.tbl_rep_default,
     DM_reports.tbl_rep_default_c,
     DM_reports.tbl_rep_default_d, DM_reports.tbl_rep_default_dd],
    ['SELECT * FROM CAMPS', 'SELECT * FROM HOTEL',
     'SELECT * FROM HOTEL_C',
     'SELECT * FROM HOTEL_D', 'SELECT * FROM HOTEL_DD'],
    [param_ar1, param_ar2,
     param_ar3,
     param_ar4,param_ar5]);

you need to pass whole sql into FillWithDASql, like

SELECT * FROM CAMPS WHERE ( CITY IS NOT NULL AND C_NO = 'I47710') ORDER BY CITY

or

SELECT * FROM CAMPS WHERE ( CITY IS NOT NULL AND C_NO = :C_NO) ORDER BY CITY

instead of

SELECT * FROM CAMPS

it is a reason, why you receive non-filtered table.

Thank You very much…
This sound of course logical…

Shalom
Manfred