fastest way to delete a lot of record in a Paradox table
My application records in a table (events.db) some events.
One field of the table is EventDate (type: date/time) and the primary index
is based on this field.
To limit the size of the table, I have to run periodically a procedure to
delete the oldest events.
Here is how this procedure looks like:
MyTable := TTable.Create(nil);
Try
MyTable.DatabaseName := ChemStat;
MyTable.TableName := 'Events.db';
MyTable.Open;
Ok := False;
NbRec := MyTable.RecordCount;
if NbRec >= 1697792 then
begin
MessSys('Pack statistics, please wait...',1);
MyTable.First;
MyTable.MoveBy(NbRec-1400000);
Ok := True;
OlderDate := MyTable.FieldByName(' EventDate').AsDateTime;
end;
MyTable.Close;
finally
MyTable.Free;
end;
if Ok then
begin
MyQuery := TQuery.Create(nil);
Try
MyQuery.DatabaseName := ChemStat;
MyQuery.SQL.Clear;
MyQuery.SQL.Add('DELETE FROM "Events.db" WHERE EventDate < ');
MyQuery.SQL.Add(''''+FormatDateTime('mm/dd/yyyy hh:nn:ss',
OlderDate)+'''');
Try
MyQuery.ExecSQL;
Except
on e:Exception do
Begin
MessageDlg('Events: can not pack Events.db' + #13 +
'Error: ' + e.message, mtError, [mbOK], 0);
end;
End;
MyQuery.Close;
finally
MyQuery.Free;
end;
end;
The problem is that this procedure is very slow.
Is there a way to optimize the procedure or at least have something to
display the progression of the operation.
