Efficient Logging of data

[siik]

I need to log data to a table every half a second. I would like to do this in the most efficient way. So far i have come up with:

Code: Select all

Q1.Close;
Q1.SQL.Clear;
Q1.SQL.Add('SELECT * FROM `MyTable` WHERE 0 = 1');
Q1.Open;
Q1.AppendRecord(VarArrayOf([my data....]));

Is there a better way? Ideally i would like to buffer this data and then do a bulk insert, however i cannot lose any of this - so committing first is the only option.

If I avoid the opening and closing of the Query, will the AppendRecord() method store the data in the query object (i.e. keep using memory)?

[AndreyZ]

You can use the INSERT statement instead of opening a dataset. Here is a code example:

Code: Select all

Q1.SQL.Text := 'INSERT INTO `MyTable` ... ';
Q1.ParamByName('first_param').AsInteger := 1;
Q1.ParamByName('second_param').AsString := 'test';
Q1.Execute;

To increase performance, you can prepare the INSERT statement. Here is a code example:

Code: Select all

Q1.SQL.Text := 'INSERT INTO `MyTable` ... ';
Q1.Prepare;
for i := 1 to 10 do begin
  Q1.ParamByName('first_param').AsInteger := i;
  Q1.ParamByName('second_param').AsString := 'test';
  Q1.Execute;
end;

Also, you can use the TMyLoader component that serves for fast loading of data to the server. TMyLoader work is based on generation of INSERT statements that insert data by several rows at once (you can control it using the TMyLoader.RowsPerQuery property). For more information about TMyLoader, please read the MyDAC documentation.