Edit a DBgrid-Query faster
lazarus / 16-Databases

----------------------------------------------------------------------
donnie · 2017-02-20 14:13

Hi there,
I have a dbgrid wich is connected with a datasource which is connected
with a Query(Dataset). In my database a have a table with the
following records : ID, X,Y,Z,CalcX, CalcY, CalcZ. As I read my Query
I call a boolean function 'CalcTabs' which takes inputs theID, X,Y,Z
and gives output  CalcX, CalcY, CalcZ.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
CalcTabs(myid: integer; x: double; y: double; z: double; out mycalcx:
double; out mycalcy: double; out mycalz: double): boolean; So When I
press the button of calculation I want to read all the lines of the
query and take the x,y,z and write the results to CalcX, CalcY, CalcZ.
The thing is that using the Query.Edit into the loop is too slow.
Maybe I shouldn't use the query that is connected to the DBGrid. This
is the procedure of Calculation.
 Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMyFormFrm.CalcAllXYZTABS(Sender: TObject);var  tabuniqueid, i:
integer;  tabx, taby, tabz, tabcalcx, tabcalcy, tabcalcz: double; 
calcbool: boolean;  begin   MyQuery.First;    try    
MyGridDB.BeginUpdate;    while not MyQuery.EOF do     begin      
tabuniqueid := MyQuery.FieldByName('ID').Value;      tabx :=
MyQuery.FieldByName('X').Value;      taby :=
MyQuery.FieldByName('Y').Value;      tabz :=
MyQuery.FieldByName('Z').Value;      calcbool := CalcTabs(tabuniqueid,
tabx, taby, tabz, tabcalcx, tabcalcy, tabz);      if calcbool = True
then      begin        MyQuery.Edit;       
MyQuery.FieldByName('CALCX').Value := tabcalcx;       
MyQuery.FieldByName('CALCY').Value := tabcalcy;       
MyQuery.FieldByName('CALCZ').Value := tabz;      end;     
MyQuery.Next;    end;   finally  //MyQuery.Upda]"]>Blockedde :=
umUpdateChanged;    MyGridDB.EndUpdate(True);  end;end;
I tried to calculate the position of the fields outside the loop but
it is much slower(don't know why).

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMyFormFrm.CalcAllXYZTABS(Sender: TObject); var IDField, CALCXField ,
CALCYField , CALCZField , XField, YField, ZField: TField;tabuniqueid,
i: integer;tabx, taby, tabz, tabcalcx, tabcalcy, tabz:
double;calcbool: boolean; begin   IDField :=
MyQuery.FieldByName('ID');  XField := MyQuery.FieldByName('X'); 
YField := MyQuery.FieldByName('Y');  ZField :=
MyQuery.FieldByName('Z');  CALCXField :=
MyQuery.FieldByName('CALCX');  CALCYField :=
MyQuery.FieldByName('CALCY');  CALCZField :=
MyQuery.FieldByName('CALCZ');  MyQuery.First;    try      
MyGridDB.BeginUpdate;     while not MyQuery.EOF do    begin          
      if not (XField.IsNull) and not (YField.IsNull) then      begin 
     tabuniqueid := IDField.Asinteger;        tabx := XField.AsFloat; 
      taby := YField.AsFloat;        tabz := ZField.AsFloat;       
calcbool := CalcTabs(tabuniqueid, tabx, taby, tabz, tabcalcx,
tabcalcy, tabz);        if calcbool = True then         
//UpdateTable( tabcalcx, tabcalcy, tabz, tabmeasid);        begin    
    //MyQuery.prepare;          MyQuery.Edit;          CALCXField
.AsFloat := tabcalcx;          CALCYField .AsFloat := tabcalcy;      
  CALCZField .AsFloat := tabz;        end;       end;     
MyQuery.Next;    end;      finally       MyGridDB.EndUpdate(True); 
end;  //procedure TMyFormFrm.UpdateTable(mycalcx, mycaly, mycalcz:
double; myID: integer);//begin//  UpdateMeas.SQL.Clear;//
 UpdateMeas.SQL.Text := 'UPDATE MEAS SET CALCX= :MCALCX, CALCY=
:MCALCY, CALCZ= :MCALCZ WHERE CALCTABLE.ID =:MID';//
 UpdateMeas.Prepare;//  UpdateMeas.ParamByName('MCALCX').Value :=
mycalcx;//  UpdateMeas.ParamByName('MCALCY').Value := mycaly;//
 UpdateMeas.ParamByName('MCALCZ ').Value := mycalcz;//
 UpdateMeas.ParamByName('MID').Value := myID;//  UpdateMeas.ExecSQL;//
 UpdateMeas.Close;//end; 
I also tried to call an Update procedure but it didn't worked faster
at all.
Thanks in advance for reading.

----------------------------------------------------------------------
rvk · 2017-02-20 14:28

First of all... the second try, with calculating your number outside
the loop, isn't much good... You loop through all X,Y and Z so you
should calculate the CALCX, Y and Z inside the loop (otherwise you
have the same values each time).

Next... what do you call slow?

And finally... the UpdateTable should be the fastest but you shouldn't
call prepare each time. That's what takes the most time.

Do something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMyFormFrm.CalcAllXYZTABS(Sender: TObject);var  tabuniqueid: integer; 
tabx, taby, tabz, tabcalcx, tabcalcy, tabcalcz: double;  calcbool:
boolean;begin  // prepare the update  UpdateMeas.SQL.Clear; 
UpdateMeas.SQL.Text := 'UPDATE MEAS SET CALCX= :MCALCX, CALCY=
:MCALCY, CALCZ= :MCALCZ WHERE CALCTABLE.ID =:MID'; 
UpdateMeas.Prepare;   MyQuery.First;  try    MyGridDB.BeginUpdate;   
while not MyQuery.EOF do    begin      tabuniqueid :=
MyQuery.FieldByName('ID').Value;      tabx :=
MyQuery.FieldByName('X').Value;      taby :=
MyQuery.FieldByName('Y').Value;      tabz :=
MyQuery.FieldByName('Z').Value;      calcbool := CalcTabs(tabuniqueid,
tabx, taby, tabz, tabcalcx, tabcalcy, tabcalcz);      if calcbool =
True then      begin        UpdateMeas.ParamByName('MID').Value :=
tabuniqueid;        UpdateMeas.ParamByName('MCALCX').Value :=
tabcalcx;        UpdateMeas.ParamByName('MCALCY').Value := tabcalcy; 
     UpdateMeas.ParamByName('MCALCZ ').Value := tabcalcz;       
UpdateMeas.ExecSQL; // no need for .Close !!      end;     
MyQuery.Next;    end;  finally    MyGridDB.EndUpdate(True);  end;end;
(if you work with transaction you shouldn't forget to commit them)

B.T.W. you posted non working code !!!
You had   tabx, taby, tabz, tabcalcx, tabcalcy, tabz: double;
Please DO NOT post non-working code.
Try to work out the compile errors first yourself.

----------------------------------------------------------------------
howardpc · 2017-02-20 14:38

If the field order is known, accessing Values using Fields[ x ] rather
than FieldByName('name') may be quicker.

----------------------------------------------------------------------
GAN · 2017-02-20 20:54

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
procedure TMyFormFrm.CalcAllXYZTABS(Sender: TObject);    var     
tabuniqueid, i: integer;      tabx, taby, tabz, tabcalcx, tabcalcy,
tabz: double;      calcbool: boolean;         begin     
MyQuery.DisableControls; <---- Add      MyQuery.First;           try 
          MyGridDB.BeginUpdate;        while not MyQuery.EOF do      
begin               tabuniqueid := MyQuery.FieldByName('ID').Value;  
      tabx := MyQuery.FieldByName('X').Value;          taby :=
MyQuery.FieldByName('Y').Value;          tabz :=
MyQuery.FieldByName('Z').Value;          calcbool :=
CalcTabs(tabuniqueid, tabx, taby, tabz, tabcalcx, tabcalcy, tabz);   
     if calcbool = True then          begin            MyQuery.Edit; 
         MyQuery.FieldByName('CALCX').Value := tabcalcx;           
MyQuery.FieldByName('CALCY').Value := tabcalcy;           
MyQuery.FieldByName('CALCZ').Value := tabz;          end;         
MyQuery.Next;        end;           finally       
MyQuery.EnableControls; <---- add      //MyQuery.Upda]"]>Blockedde :=
umUpdateChanged;        MyGridDB.EndUpdate(True);      end;    end;
Hi, you can gain speed with MyQuery.DisableControls because  the Query
stop sending data to all the compenents. Then MyQuery.EnableControls
at the begining of the finally code.

Regards.

----------------------------------------------------------------------
donnie · 2017-02-22 07:32

Hi rvk,
QuoteB.T.W. you posted non working code !!!
You had   tabx, taby, tabz, tabcalcx, tabcalcy, tabz: double;
Please DO NOT post non-working code.
Try to work out the compile errors first yourself.Yes there is a
mistake. I changed the procedure before copying it and I made the
fields more simple in order not to confuse you more. (The true name of
the fields were not that. Sorry for the mistake). The code was working
ok. Yes the MyQuery.Prepare was slowing it down. I removed it and it
is much better. When I say slow I mean that for 1000 records it was
taking about 10 minutes.

Hi howardpc,
QuoteIf the field order is known, accessing Values using Fields[ x ]
rather than FieldByName('name') may be quicker.I had it with  Fields[
x ] and the difference was almost the same as I counted with timer.
The Fields[ x ] was quicker from the fieldbyname('X').value, just a
sec in 1000 records which is something that I can't explain. The
Fields[ x ] should have been much quicker.

Hi GAN,
QuoteHi, you can gain speed with MyQuery.DisableControls because  the
Query stop sending data to all the compenents. Then
MyQuery.EnableControls at the begining of the finally code.Yes i tried
the disable-enable controls and it is quicker. I had it in previous
versions but removed it don't know why as the Query is connected to
the DBGrid live. I thought that with DBGrid.BeginUpdate and .EndUpdate
was enough. But it wasn't.

Thank you all for your comments. They help me a lot. The major problem
seems to be the following. Inside the CalcTabs() which I was calling
inside the loop I was calling the GetTempFileName all the time for
every record. That was a really speed killer. Removing that and adding
your comments the ten minutes became 58 secs. That's a huge
improvement.
Thanks once again

----------------------------------------------------------------------
rvk · 2017-02-22 09:04

Quote from: donnie on February 22, 2017, 08:32:08 amInside the
CalcTabs() which I was calling inside the loop I was calling the
GetTempFileName all the time for every record.
You might want to check how many files your %temp% directory has.

GetTempFileName gives back a unique filename but it has to check the
%temp% (temporary directory) to see if the file doesn't already
exists. If there are thousands of files in that directory it could
take a while. Even though a while might be milliseconds each time,
they can add up in the end.

Pro-tip: Always make sure your %temp% doesn't grow too large.
(also see question and answer here:
http://superuser.com/q/238008/235723)

----------------------------------------------------------------------
donnie · 2017-02-22 09:47

Exactly that rvk,
The temp was big enough(3,5GB) and it was checking for all the 1000
records the temp file.Too much speed waste. Thanks for the reply.