speed up queries

[roozgar]

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]

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]

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

[ViktorV]

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.