speed up queries

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
roozgar
Posts: 12
Joined: Thu 24 May 2018 13:05

speed up queries

Post by roozgar » Sun 28 Apr 2019 10:52

hello
i have a text file with 86400 lines it contains time in second and long and lat of a day (for example a car locations in a day)
these 86400 must saved on a table named `points`
also for each line i must do some process and save about 10 record on table `points_prop` and i must delete records in table if exists
my code is like this:

Code: Select all

AssignFile(myFile, 'OutputSGP4.txt' );
Reset(MyFile);
while (not Eof(myFile)) and (not Terminated) do
begin
	UniQueryRead.SQL.Text := 'SELECT * FROM `gss`;';
	UniQueryRead.Open;
	UniQueryRead.First;
	while (not UniQueryRead.Eof) do begin
                      UniQueryWrite.SQL.Text := 'Delete from `fds_gs` WHERE ......';

                      UniQueryWrite.Execute;
                      UniQueryWrite.SQL.Text := 'INSERT INTO `fds_gs` .....;';
                      UniQueryWrite.Params[0].DataType := ftString;
                      UniQueryWrite.Params[1].DataType := ftString;
			UniQueryWrite.Execute;
	end;
              UniQueryWrite.SQL.Text := 'Delete from `fds_data` WHERE ....;';
              UniQueryWrite.Params[0].DataType := ftString;
              UniQueryWrite.Params[0].AsString := thePointDateStamp.ToString;
              UniQueryWrite.Execute;

              UniQueryWrite.SQL.Text := 'INSERT INTO `fds_data` (`type`,`x`,`y`,`z`,`vx`,`vy`,`vz`,`rtime`,`rgroup`,`ecfx`,`ecfy`,`ecfz`,'+
              '`ecfvx`,`ecfvy`,`ecfvz`,`lat`,`lng`,`alt`,`sma`,`ecc`,`incl`,`argop`,`raan`,`truea`,`lighting`,`loctime`,`status`) VALUES '+
              '(:f0,:f1,:f2,:f3,:f4,:f5,:f6,:f7,:f8,:f9,:f10,:f11,:f12,:f13,:f14,:f15,:f16,:f17,:f18,:f19,:f20,:f21,:f22,:f23,:f24,:f25,:f26);';
              UniQueryWrite.Params[0].DataType := ftString;
		UniQueryWrite.Execute;
end;

This code need about 0.5 second for each line! how can i speed up my proccess?

ViktorV
Devart Team
Posts: 2794
Joined: Wed 30 Jul 2014 07:16

Re: speed up queries

Post by ViktorV » Tue 30 Apr 2019 08:53

You can use the following recommendations to accelerate your task fulfilment:
- specify the UniQueryRead.SQL.Text property setting and the UniQueryRead.Open method calling before the code

Code: Select all

while loop (not Eof(myFile)) and (not Terminated) do;
- for each repeated SQL expression, use a separate TMyQuery instance;
- set the TMyQuery.SQL.Text property and call the TMyQuery.Prepare method for all the above TMyQuery inatnces, before the code

Code: Select all

while loop (not Eof(myFile)) and (not Terminated) do;
- in the loop, set the required parameter values for the TMyQuery instances and call the TMyQuery.Execute method.
Also you can get more information about performance improving when using our components at our website: https://www.devart.com/mydac/docs/index ... rmance.htm

roozgar
Posts: 12
Joined: Thu 24 May 2018 13:05

Re: speed up queries

Post by roozgar » Thu 02 May 2019 14:36

thank you
is there any document and example for TMyLoader
i searched but not lucky!

ViktorV
Devart Team
Posts: 2794
Joined: Wed 30 Jul 2014 07:16

Re: speed up queries

Post by ViktorV » Fri 03 May 2019 09:48

You can get more information about TMyLoader in MyDAC help: https://www.devart.com/mydac/docs/devar ... loader.htm
Also you can look at the example of using TMyLoader in MyDACDemo.

Post Reply