Optimize query in database and compress code

Question

Optimize query in database and compress code

Navigation

#1 by (2 votes)

1

Follow 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 consequently faster? When I do 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

asked by anonymous 13.05.2014 / 21:42

1 answer

Establish at least one number in EditText About the TADCommand and TADTableAdapter components, what are they, what are they for?

score 2 · Accepted Answer

The problem with your code is that for every line of the text file you are loading all the records in the database. I did a refactoring to make it easier to understand and it looked 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 open Query out of the while and find the records with a locate command in the query