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?
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.
– dang
But is the code for you correct? Or is there any way to improve?
– Ramon Ruan
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
– dang
hahaha, I get it, but explain to me, right, an index? (Excuse me ignorance.) rsrs
– Ramon Ruan
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.
– dang
Thank you for the @Dang explanation, I will search on this, know where the problem of slowness is.
– Ramon Ruan
I am finishing the answer, put shortly
– Caputo