Board index » delphi » Adding records is very slow.

Adding records is very slow.


2007-01-25 10:26:29 PM
delphi130
I've been playing with Access datafiles and Delphi 2006 and am either
missing something or am disappointed with the speed at which records can be
added to a database. I running on a fast computer (2 x 2.99 GHz cpu and 4G
ram) but simply adding 5,000 records (each record only has one field) to an
access file is taking more than 16 seconds. Is this normal or is there
something I am missing. I have attached the code I have been using to test below.
Note : the time I am measuring does not include the time to create the .mdb
file nor the time to add the new table, but only the time to write into the
table.
Thanks...
Function GetConnectionString:AnsiString;
begin
result:= 'Provider=Microsoft.Jet.OLEDB.4.0;'+
'Data Source=C:\zip\MyTest.mdb;';
end;
procedure TForm1.Button1Click(Sender: TObject);
var Adotable : TAdotable;
Catalog1 : TCatalog;
AdoCommand : TAdoCommand;
Rec :integer;
tick :Longint;
begin
// First Create the MDB file
Catalog1 := TCatalog.create(nil);
Catalog1.Create1(GetConnectionString);
Catalog1.free;
// now Create a table with one Field
AdoCommand := TAdoCommand.create(nil);
AdoCommand.ConnectionString := GetConnectionString;
AdoCommand.CommandText:= 'CREATE TABLE MyTable ( Field1 NUMBER )';
AdoCommand.Execute;
AdoCommand.free;
// Now Open the table
Adotable := TAdotable.create(nil);
Adotable.ConnectionString := getConnectionString;
Adotable.TableName := 'MyTable';
Adotable.Active := true;
tick := getTickCount;
// Now Write to the Table
for rec := 1 to 5000 do
begin
Adotable.Append;
Adotable.Fields[0].AsFloat := Rec;
end;
Tick := getTickCount-tick;
Adotable.Post;
Adotable.free;
ADODataSet1.CreateDataSet
end;
 
 

Re:Adding records is very slow.

AFAIK fastest approach to append data into Access table would be to use
TADODataSet in tabledirect mode. Something like this:
ADataSet := TAdoDataSet.create(nil);
ADataSet.ConnectionString := getConnectionString;
ADataSet.CursorLocation := clUseServer;
ADataSet.CommandType := cmdTableDirect;
ADataSet.CommandText := 'MyTable';
ADataSet.Active := true;