importing data procedure
lazarus / 16-Databases

----------------------------------------------------------------------
tatamata · 2008-08-19 21:33

In my program I use a form with grids for mapping source and
destination tables and fields. Source tables are in DBase format
(TDbf), while destination tables are in Firebird database. Besides
destination tables, there are also two tables in the Firebird database
that hold information for destination table/source table and
destination fields/source fields mapping.
All dataset components are placed on the main form. I have a procedure
that performs data input, called ImportData. Although it works OK, I
have serious performance issues when importing DBase tables with many
rows.
Could someone propose how to improve performance?
Here is the simplified code of the procedure:
Code: [Select]procedure ImportData;
{Data are being imported from DBase tables into Firebird database
tables.
Mapping between destination and source tables and fields are provided
by ZReadOnlyQueryDstnTbls and ZReadOnlyQueryDstnFlds datasets.
ZReadOnlyQueryDstnTbls has fields: DSTN_TBL, SRC_TBL, SRC_TBL_PATH.
ZReadOnlyQueryDstnFlds has fields: DSTN_FLD, DSTN_FLD:TYPE,
DSTN_FLD_DFLT_VAL, SRC_FLD.}
type
  {One record holds information about one destination field and
corresponding source field.}
  TDstnFlds=Record
    vDstnFldsId:Integer;
    vDstnFldName:String[255];
    vDstnFldType:String[255];
    vDstnFldDefaultValue:String[255];
    vSrcFldName:String[255];
  end;
var
  vDstnTblName:String;
  vSrcTblName:String;
  vSrcTblPath:String;
  {One array of records holds information about all destination fields
and corresponding source fields.
   The array length corresponds to number of fields in the destination
table}
  vDstnFlds:array of TDstnFlds;
  {Variables for performance monitoring}
  vElapsedTime:TDateTime;
  {Variable for iterations}
  I:Integer;
begin
  try
    vElapsedTime:=Now;
    with FormMain do begin
      //Query DSTN_TBLS.
      with ZReadOnlyQueryDstnTbls do begin
        Close;
        Open;
        First;
      end;
      //Iterate through rows in table DSTN_TBLS.
      while not ZReadOnlyQueryDstnTbls.EOF do begin
        try
         
vDstnTblName:=ZReadOnlyQueryDstnTbls.FieldByName('DSTN_TBL').AsString;
         
vSrcTblName:=ZReadOnlyQueryDstnTbls.FieldByName('SRC_TBL').AsString;
          vSrcTblPath:=ZReadOnlyQueryDstnTbls.FieldByName('SRC_TBL_PAT
H').AsString;
          //Assign source table.
          with DbfSourceTable do begin
            Active:=False;
            TableName:=vSrcTblName;
            FilePathFull:=vSrcTblPath;
            //Set datetime handling.
            DbfSourceTable.DateTimeHandling:= dtDateTime;
            Active:=True;
            //Position source table to the first row.
            First;
          end;
          //Delete all records from the destination table
          with ZQueryTemp do begin
           Active:=False;
           SQL.Clear;
           SQL.Add('DELETE FROM '+vDstnTblName+';');
           ExecSQL;
          end;
          //Assign destination table.
          with ZTableDestinationTable do begin
            Active:=False;
            TableName:=vDstnTblName;
            Active:=True;
          end;
          //Query table DSTN_FLDS.
          with ZReadOnlyQueryDstnFlds do begin
            Close;
            ParamByName('P_DSTN_TBL').AsString:=vDstnTblName;
            Open;
            First;
          end;
          //Create array of records for destination fields.
          try
            //Dimension the array vDstnFlds to the number of fields in
the table DSTN_FLDS.
            SetLength(vDstnFlds,ZReadOnlyQueryDstnFlds.RecordCount);
            I:=0;
            //Iterate through rows in the table DSTN_FLDS.
            while not ZReadOnlyQueryDstnFlds.EOF do begin
              //Map DSTN_FLD fields to vDstnFlds array of records.
              with vDstnFlds[I],ZReadOnlyQueryDstnFlds do begin
                 //Map current record in array of records vDstnFlds to
the current row in the table DSTN_FLDS.
                 vDstnFldsId:=FieldByName('DSTN_FLDS_ID').AsInteger;
                 if FieldByName('DSTN_FLD').IsNull then
                   vDstnFldName:=''
                 else
                   vDstnFldName:=FieldByName('DSTN_FLD').AsString;
                 {ShowMessage('vDstnFldName: ' + vDstnFldName);}
                 if FieldByName('DSTN_FLD_TYPE').IsNull then
                   vDstnFldType:=''
                 else
                 
 vDstnFldType:=FieldByName('DSTN_FLD_TYPE').AsString;
                 {ShowMessage('vDstnFldType: ' + vDstnFldType);}
                 if FieldByName('DSTN_FLD_DFLT_VAL').IsNull then
                   vDstnFldDefaultValue:=''
                 else
                 
 vDstnFldDefaultValue:=FieldByName('DSTN_FLD_DFLT_VAL').AsString;
                 if FieldByName('SRC_FLD').IsNull then
                   vSrcFldName:=''
                 else
                   vSrcFldName:=FieldByName('SRC_FLD').AsString;
                 {ShowMessage('vSrcFldName: ' + vSrcFldName); }
              end;
              ZReadOnlyQueryDstnFlds.Next;
              I:=I+1;
            end;
          except
            ShowMessage('Error while mapping destination fields to the
vDstnFlds array of records.');
          end;
          //Iterate through rows in the source table.
          while not DbfSourceTable.EOF do begin
            try
              //Append records into the destination table.
              ZTableDestinationTable.Append;
                //Iterate through elements in the array vDstnFlds.
                I:=0;
                for I:=Low(vDstnFlds) to High(vDstnFlds) do begin
                  try
                    //Set the destination field value.
                    with ZTableDestinationTable, vDstnFlds[I] do begin
                      try
                        if (((vSrcFldName=Null)or (vSrcFldName=''))
and ((vDstnFldDefaultValue='') or (vDstnFldDefaultValue=Null))) then
                          begin
                            try
                              //Use Clear method to assign NULL value
to the field
                              {FieldByName(vDstnFldName).Clear;}
                            except
                              ShowMessage('Error while trying to
assign Null value to the destination field.'
                              +' Destination table: '+vDstnTblName+',
source table: '+vSrcTblName+'.'
                              +' Destination field: '+vDstnFldName+',
field type:'+vDstnFldType+'.');
                            end;
                          end
                        else
                          begin
                            try
                              begin
                                if ((vSrcFldName='') or
(vSrcFldName=Null)) then
                                 
FieldByName(vDstnFldName).AsVariant:=vDstnFldDefaultValue
                                else
                                  if
((DbfSourceTable.FieldByName(vSrcFldName).IsNull) or
(DbfSourceTable.FieldByName(vSrcFldName).AsString='')) then
                                   
FieldByName(vDstnFldName).AsVariant:=vDstnFldDefaultValue
                                  else
                                    FieldByName(vDstnFldName).AsVarian
t:=DbfSourceTable.FieldByName(vSrcFldName).Value;
                              end;
                            except
                              {ShowMessage('Error while assigning
value to the destination field.'
                                   +' Destination table:
'+vDstnTblName+', source table: '+vSrcTblName+'.'
                                   +' Destination field:
'+vDstnFldName+', field type:'+vDstnFldType+'. ');}
                            end;
                        end;  //End begin of the ELSE part
                      except
                        ShowMessage('Error while evaluating value to
be assigned to the destination field.'
                        +' Destination table: '+vDstnTblName+', source
table: '+vSrcTblName+'.'
                        +' Destination field: '+vDstnFldName+', field
type:'+vDstnFldType);
                      end;
                    end; //End "with ZTableDestinationTable do begin"
                  except
                    ShowMessage('Error while navigating through
ZReadOnlyQueryDstnFlds');
                  end;
                end; //End "for I:=0 to Length(vDstnFlds)-1 do begin"
              ZTableDestinationTable.Post; //Posts inserted record
into the destination table.
            except
              ShowMessage('Error while navigating through
DbfSourceTable');
            end;
            DbfSourceTable.Next;
          end; //End "while not DbfSourceTable.EOF do begin"
        except
          ShowMessage('Error while navigating through
ZReadOnlyQueryDstnTbls.');
        end;
        ZReadOnlyQueryDstnTbls.Next;
      end; //End "while not ZReadOnlyQueryDstnTbls.EOF do begin"
    end; //End "with FormMain do begin"
  finally
    vElapsedTime:=Now-vElapsedTime;
    ShowMessage('Import finished! Elapsed time:
'+FormatDateTime('hh:nn:ss',vElapsedTime));
  end; //End try..finallly block.
end; //End procedure block.



Thanks,

Zlatko

----------------------------------------------------------------------
arnoldb · 2008-08-20 14:32

You have the field ID in your array - rather than using
FieldByName(vDstnFldName).AsVariant := ... , could you not use
Fields.AsVariant?
Doing a FieldByName lookup takes a lot longer.