How to improve file recording speed for a Clientdataset?

Question

I’m integrating a system with a bank file, and I’m having a problem with the process. I receive from the credit card operator a plain text file with approximately 1300Kb and about 5,500 lines.

I’m reading this file and storing it in a ClientDataSet, in memory only, I do not enter in the database at any time. However I’m finding that the reading process is very slow, since I’m managing to record at a rate of about 13 lines per second, I don’t have much experience with ClientDataSet and I don’t know if this rate is acceptable.

To read the file I import the text file to a StringList, then loop based on the number of lines in the file, importing each type of record to its respective ClientDataSet (Inside the file can have 9 different types of records, and each one I put in one ClientDataSet different).

Loop in the archive:

  EnableDisableControls(False);
  { Percorre todas as linhas do arquivo verificando o tipo e executando uma rotina para cada tipo de registro. }
  for I := 0 to (Extrato.Count - 1) do
  begin
    CurrentLine := I;
    case StrToInt(Copy(Extrato.Strings[I], 01, 01)) of
      0: LerHeader;
      1: LerRegistroDetalheRO;     //Resumo de Operação
      2: LerRegistroDetalheCV;     //Comprovante de Venda
      3: LerRegistroDetalheIDROSA; //Informativo detalhe do RO do Saldo em Aberto
      4: LerRegistroDetalheIBSA;   //Informativo por bandeira do Saldo em Aberto
      5: LerRegistroDetalheIOAR;   //Informativo de Operação de Antecipação de Recebíveis
      6: LerRegistroDetalheIRODA;  //Informações de RO da data antecipada
      7: LerRegistroDetalheIDRODA; //Informações de débitos de ROs da data antecipada
      9: LerTrailer;
    end;
  end;

  { Reativar todos os controles após a inserção. }
  EnableDisableControls(True);

If the record is type 2, for example, I call the respective recording time ClientDataSet:

procedure ThreadProcessarExtrato.LerRegistroDetalheCV;
begin
  with FrmExtratoEletronicoCielo, Extrato, DSDetalheCV.DataSet do
  begin
    Insert;
    FieldByName('TIPO_REGISTRO').AsString := Copy(Strings[CurrentLine], 001, 1);
    FieldByName('ESTAB_SUBMISSOR').AsString := Copy(Strings[CurrentLine], 002, 10);
    FieldByName('NUMERO_RO').AsString := Copy(Strings[CurrentLine], 012, 7);
    FieldByName('NUMERO_CARTAO').AsString := Copy(Strings[CurrentLine], 019, 19);
    ...
    ...
    ...
    //Aqui existem muitos outros campos que são atribuídos, retirei para ficar menor...
    ...
    ...
    ...
    Post;
    Inc(TotalRegistrosCV);
  end;
end;

So, any idea how to expedite this process? Or a better way to accomplish this process?

Answer 1

Hello, I’ve already had to do something similar, there are some suggestions that have greatly improved the performance in my case.

1 - Change the property LogChanges of the Clientdataset for False (this greatly decreases the time of mass Inserts)

2 - If you are not displaying Clientdataset data in any visual control run ClienteDataset.DisableControls before starting the Inserts

3 -If the Clientedataset is not indexed, preferably by using the Insert instead of Append, because Append has to place the new record inserted at the end of the dataset

4 - If you have many fields in Dataset delete the use of FieldByName can help a lot too. If Fields are already created in the design time dataset you can access it directly from the object TField (example: ClienteDataset1NOME.AsString)

5 - Clientdataset is not good for inserting many records, if it is indexed the curve in the operation of many inserts is exponential, this can be evidenced through the Asmprofiler tool. An option that helps in performance is the elimination of indexes before starting these batch operations and restoring indexes at the end of the operation so the cost becomes more linear.

Answer 2

I don’t know the details of your system, but 780 lines of a text file per minute seems low to me, unless you’re pulling the file from somewhere on the network.

From what I’ve seen, all the comments so far have focused on clientdataset. I think it’s worth changing the focus a little.

For archives "small", use a StringList to read the txt file is very valid. It is usually faster, easier, looks perfect.

But the fact is: Using StringList, you bring to memory your text file. From what I understand, the file you are using is huge, and in cases like this, I realized that using the file structure, not bringing all the txt to memory, things flow better.

I tested in a file with 500,000 lines (Input.txt).

I tested first the StringList: (nor need I say that it did not happen, right?)

Then using the form I proposed:

---

Following example for test:

You usually call the function this way:

procedure TForm1.Button2Click(Sender: TObject);
var
  arquivo : Tstringlist;
  I: Integer;
  linha : string;
begin
  arquivo := TStringList.Create;
  arquivo.LoadFromFile('c:\Entrada.txt');
  for I := 0 to arquivo.Count - 1 do
  begin
    linha := arquivo.Strings[i];
    . . . (tratamento da linha)
  end;
  showmessage('pronto');
  arquivo.Destroy;
end;

Test doing this way:

var
   arquivo : TextFile;
   linha   : string;
begin
  AssignFile(arquivo,'c:\Entrada.txt');
  Reset(arquivo);
  while not eof(arquivo) do
  begin
    readln(arquivo,linha);
    . . . (tratamento da linha)
  end;
  showmessage('pronto');
  CloseFile(arquivo);

I don’t know if this will solve your slowness, but maybe consuming less memory will help a little.

Answer 3

For opening a text file I prefer direct approach, so the file is released soon after its reading:

  function StreamToString(Arquivo: String): String;
  var
    oMemoryStream: TMemoryStream;
  begin
    Result := '';
    if FileExists(Arquivo) then
    begin
      oMemoryStream := TMemoryStream.Create;
      try
        oMemoryStream.LoadFromFile(Arquivo);
        SetString(Result, PChar(oMemoryStream.memory), oMemoryStream.Size);
      finally
        FreeAndNil(oMemoryStream);
      end;
    end;
  end;

As for Dataset, is it really necessary? In my view we use only for aesthetic effect in this case, which can be replaced by byte control and not line control. Transferring the contents of the file to a variable and from this variable directly to the bank your process should have a considerable gain. Working with 200mb files with data from a credit card I made the breaks and ran the COMMIT every 10,000 record on an Oracle database and recorded something around 60 record per second. Example:

procedure TForm1.BitBtn1Click(Sender: TObject);
type
  TDados = record
    Nome  : String;
    Obs   : String;
  end;

var
  rDados    : TDados;
  sTexto    : String;
  sAuxiliar : String;
  nCount,
  nIndex    : Integer;
  nCommit   : Integer;

  function Grava(Dados: TDados): Boolean;
  begin
    Result := True;
    // processo de gravação
  end;

  function Commit: Boolean;
  begin
    Result := True;
    // processo de COMMIT
  end;

  function StreamToString(Arquivo: String): String;
  var
    oMemoryStream: TMemoryStream;
  begin
    Result := '';
    if FileExists(Arquivo) then
    begin
      oMemoryStream := TMemoryStream.Create;
      try
        oMemoryStream.LoadFromFile(Arquivo);
        SetString(Result, PChar(oMemoryStream.memory), oMemoryStream.Size);
      finally
        FreeAndNil(oMemoryStream);
      end;
    end;
  end;

begin

  sTexto := StreamToString('C:\Arquivo.csv');
  if Trim(sTexto) = EmptyStr then
  begin
    Raise Exception.Create('Arquivo vazio!');
  end
  else
  begin
    for nCount := 1 to Length(sTexto) do
    begin
      if (sTexto[nCount] in [#10, #13]) and (sAuxiliar <> '') then
      begin
        rDados.Obs := sAuxiliar;
        sAuxiliar  := '';
        Grava(rDados);
        if nCommit < 10000 then // Vai depender do banco de dados
          nCommit := nCommit + 1
        else
        begin
          Commit;
          nCommit := 0;
        end;
      end
      else if (sTexto[nCount] = ';') then
      begin
        nIndex := nIndex + 1;
        case nIndex of
          0 : rDados.Nome := sAuxiliar;
          // demais campos com exceção do ultimo;
        end;
        sAuxiliar := '';
      end
      else if not (sTexto[nCount] in [#10, #13]) then
        sAuxiliar := sAuxiliar + sTexto[nCount];
    end;
  end;
end;