I have simple JET database in my application: one table, 9 text columns with limited width, indexed by first column [ID] - see code. This table is accessed using connection string like "'Provider=Microsoft.Jet.O
ata Source=MyData.mdb;Persist Security Info=False" (more specifically I'm using Delphi 6 and TADOTable/TADOQuery). From time to time the content is to be replaced by external data (approximatively 13000 records in .csv file, 500 kb size). The way I'm doing it:
for every record read from .csv
INSERT // this produces an error if ID exists
And now the problem.
My database is about 1Mb. After content replacing it grows to 250Mb. When I compact it then it is back to 1MB. I can use algorithm like
if record exists then UPDATE else INSERT
and then database size is stable, but it is a 100 times slower.
Is it normal behaviour or I'm doing something wrong?. The program is designed to be run on various machines and sometimes this extra 250Mb can be a problem.
The enviroment is Delphi 6, Win XP SP3, Access 2010 installed.
CREATE TABLE [players] (
[ID] TEXT(5) NOT NULL DEFAULT '00000',
[IMIE] TEXT(20) NOT NULL,
[NAZWISKO] TEXT(30) NOT NULL,
PRIMARY KEY ([ID])