Optimize database query and compress code

Asked

Viewed 785 times

1

Follows the code:

procedure TfrmGrid.btnLoadClick(Sender: TObject);
var
 txt: TextFile;
 treg, lreg: integer;
 valortxt, valorbd : double;
 dt1, dt2,lTemp, valor, dtcompratxt: String;
 dtcompra: TDateTime;
begin
lreg:= 1;
treg:= 0;

StatusBar1.Panels[0].Text:= 'Abrindo arquivo de txt';
AssignFile(txt, frmSelection.FileListBox1.FileName);
Reset(txt);
 while not eof(txt) do
 begin
   Readln(txt, lTemp);
   if  (copy(lTemp, 1, 3) = 'E14') then
   begin
     StatusBar1.Panels[0].Text:= 'Executando a query';
     dtcompratxt := copy(lTemp,65,2)+'/'+copy(lTemp,63,2)+'/'+copy(lTemp,59,4);

     DModuleGrid.ZQuery1.Close;
     DModuleGrid.ZQuery1.SQL.Clear;
     DModuleGrid.ZQuery1.SQL.Add('SELECT dtcompra, numnf, numcupom, ccf, valor FROM tdcupant');
     //DModuleGrid.ZQuery1.SQL.Add('WHERE dtcompra BETWEEN '+dt1+' AND '+dt2+';');
     DModuleGrid.ZQuery1.Open;
     DModuleGrid.ClientDataSet1.SetProvider(DModuleGrid.DataSetProvider1);
     DModuleGrid.ClientDataSet1.Open;

     if not (DModuleGrid.ZQuery1.IsEmpty) then
     begin
       StatusBar1.Panels[0].Text:= 'Executando o loop de consulta/comparação';
       DModuleGrid.ZQuery1.First;
       while not DModuleGrid.ZQuery1.Eof do
       begin
         if (copy(lTemp,53,6) = DModuleGrid.ZQuery1.FieldByName('numcupom').AsString)
         and (copy(lTemp,47,6) = DModuleGrid.ZQuery1.FieldByName('ccf').AsString)
         and (StrToDateTime(dtcompratxt) = DModuleGrid.ZQuery1.FieldByName('dtcompra').AsDateTime)
         or (copy(lTemp,4,20) = DModuleGrid.ZQuery1.FieldByName('numnf').AsString)
         then
         begin
          StatusBar1.Panels[0].Text:= 'Incrementando registros';
          inc(lreg);
          //Valor no BD
          valorbd := DModuleGrid.ZQuery1.FieldByName('valor').AsFloat;
          //Valor no TXT
          valortxt := StrToFloat(copy(lTemp, 109, 14))/100;
          //Diferença nos valores
          if (valorbd <> valortxt) then
          begin
           inc(treg);
           if (valor = '') then
           begin
            valor := IntToStr(0);
           end
           else
            valor := IntToStr(treg);
            Label1.Caption:='Divergências Cupons: '+valor;
          end;

         end;
         StatusBar1.Panels[0].Text:= 'Próximo registro';
         DModuleGrid.ZQuery1.Next;
       end;
     end;
   end;
   ProgressBar1.Position := ProgressBar1.Position+treg;
 end;
  StatusBar1.Panels[0].Text:= 'Existem '+ IntToStr(lreg) + ' linhas de Cupons(E14)';
  CloseFile(txt);
  CloseQuery;
end;

How can I make the code more compact and therefore faster? When I perform this query, and it will fetch and compare the values in the database, it takes about 5~6mins to return to work normally.

Does anyone have any idea how I can do this?

delphi

  • Have you tried running the query(s) in the database(s) outside the program? If the bottleneck is there, it may be more important to optimize the base (by creating an index, for example) than to move the code.

  • But is the code for you correct? Or is there any way to improve?

  • I do not know Delphi, I just gave a suggestion to avoid you lose a lot of time optimizing code to later discover that was just create an index. rs

  • hahaha, I get it, but explain to me, right, an index? (Excuse me ignorance.) rsrs

  • 1

    Indexes are objects in the database that speed up the search for records in tables. But to know if this will help, it is important to see where it is taking, whether it is in access to the bank or in the processing of the same Delphi code.

  • Thank you for the @Dang explanation, I will search on this, know where the problem of slowness is.

  • I am finishing the answer, put shortly

Show 2 more comments

1 answer

2


The problem with your code is that for each line of the text file you are loading all the records from the database. I did a refactoring to make it easier to understand and it was like this:

procedure TfrmGrid.btnLoadClick(Sender: TObject);
begin
    CarregarDados;
end;

procedure PrepararQuery;
begin
    DModuleGrid.ZQuery1.Close;
    DModuleGrid.ZQuery1.SQL.Clear;
    DModuleGrid.ZQuery1.SQL.Add('SELECT dtcompra, numnf, numcupom, ccf, valor FROM tdcupant WHERE numnf = :numnf AND ccf = :ccf and numcupom = :numcupom');
    DModuleGrid.ZQuery1.Prepare;    
end;

function EhLinhaCupom(const Linha: string): boolean;
begin
    Result := (copy(Linha, 1, 3) = 'E14');
end;

procedure AbrirQuery(const NumNf, CCF, NumCumpom: string);
begin
    DModuleGrid.ZQuery1.Close;
    DModuleGrid.ZQuery1.ParamByName('NumNf').AsString := NumNf;
    DModuleGrid.ZQuery1.ParamByName('CCF').AsString := CCF;
    DModuleGrid.ZQuery1.ParamByName('NumCumpom').AsString := NumCumpom;
    DModuleGrid.ZQuery1.Open;

    //Não entendi a necessidade deste dataset
    DModuleGrid.ClientDataSet1.SetProvider(DModuleGrid.DataSetProvider1);
    DModuleGrid.ClientDataSet1.Open;    
end;

procedure ProcessaLinha(const Linha: string; var TotalDivergencia: integer);
var
    NumCupom, CCF, NumNF: string
    dtCompra: TDateTime;
    valorbd, valortxt: Currency;
begin
    if not EhLinhaCupom(Linha) then
        Exit;

    NumCupom := copy(lTemp,53,6);
    CCF := copy(lTemp,47,6);
    NumNF := copy(lTemp,4,20);
    dtCompra := StrToDate(copy(lTemp,65,2)+'/'+copy(lTemp,63,2)+'/'+copy(lTemp,59,4));

    AbrirQuery(NumNf, CCF, NumCumpom);
    if DModuleGrid.ZQuery1.IsEmpty then
        Exit;

    StatusBar1.Panels[0].Text:= 'Incrementando registros';
    inc(lreg);

    valorbd := DModuleGrid.ZQuery1.FieldByName('valor').AsFloat;
    valortxt := StrToFloat(copy(lTemp, 109, 14))/100;

    //Diferença nos valores
    if (valorbd <> valortxt) then
    begin
        inc(TotalDivergencia);
        Label1.Caption:='Divergências Cupons: ' + IntToStr(TotalDivergencia);
    end;

    StatusBar1.Panels[0].Text:= 'Próximo registro';
    DModuleGrid.ZQuery1.Next;   
end;

procedure CarregarDados;
var
 txt: TextFile;
 TotalDivergencia, lreg: integer;
 lTemp: string;
begin
    PrepararQuery;
    lreg:= 0;   
    TotalDivergencia := 0;

    StatusBar1.Panels[0].Text:= 'Abrindo arquivo de txt';
    AssignFile(txt, frmSelection.FileListBox1.FileName);
    try
        Reset(txt);
        while not eof(txt) do
        begin
            Readln(txt, lTemp); 
            ProcessaLinha(lTemp, TotalDivergencia);
            Inc(lreg);
            StatusBar1.Panels[0].Text:= 'Próximo registro';
        end;

        StatusBar1.Panels[0].Text:= 'Existem '+ IntToStr(lreg) + ' linhas de Cupons(E14)';
    finally     
        CloseFile(txt); 
    end;
end;

But a better result can be achieved if you change the Query open out of the while and locate the records with a locate command in the query

  • your answer was very helpful, took some questions of mine, now I just could not run, because of the parameters of the database, it presents only nothing in my Dbgrid.How to solve?

  • @Ramonruan try to use the parameters as Integer and use a Trim when assigning. Probably doing the query with the spaces of the text file or with the zeroes on the left

  • but if you use Strtoint(), tbm will not eliminate zeros?

  • 1

    @Ramonruan Post the structure of your table and format of the fields and some rows of the table and text file as an example of data. Then we will see the best way to mount the parameters of the query

  • could not be removed the zeros, in this case, the zeros tbm count, they are 6 characters that take from the txt file. But I will post your code that I adapted to my site, and I will try to explain briefly how it works ok?

  • @Ramonruan You need to make the value passed to the parameter equal to the contents of the database

  • 1

    My friend, it worked the comparison, now I have to show is the divergences in Dbgrid :(, thank you very much, by the way, your post helped a lot :)

Show 2 more comments

Browser other questions tagged delphi

You are not signed in. Login or sign up in order to post.