Am 08.02.2012 23:08, schrieb Steinar Midtskogen:
Hello
I'm having trouble with one table in my database.
When I build my database from scratch using millions of inserts, one
table causes problems. Inserts get slower and slower. I have about
830,000 inserts for that table. It gets to 300,000 pretty fast, but
then it gets slower and slower, and eventually it will only do a few
inserts per second, and I then I have to kill sqlite3 as it will run
for hours if not days. The -echo option reveals that it gets slower
and slower. sqlite3 runs at 100% CPU.
I create other similar tables with 830,000 inserts the same way, but
inserts into them don't slow down.
The table in question is:
CREATE TABLE Voksenlia2 (
temp_in REAL,
pressure REAL,
rh_in REAL,
temp_in_2 REAL,
temp_in_3 REAL,
temp_in_4 REAL,
temp_in_5 REAL,
temp_ground_0cm REAL,
temp_ground_10cm REAL,
temp_ground_20cm REAL,
temp_ground_50cm REAL,
radiation INTEGER,
radiation_2 INTEGER,
uv REAL,
temp_uv REAL,
unix_time INTEGER, PRIMARY KEY (unix_time)
);
The commands start this way:
begin;
insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null,
null, null, null, null, null, null, null, null, 1072915200);
insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null,
null, null, null, null, null, null, null, null, 1075063152);
and eventually end in a "commit". So only one transaction.
I've tried:
* PRAGMA synchronous=OFF.
* Create the database file in /dev/shm/ (RAM disk).
* Break up the inserts into several transactions.
* Create and fill this table before everything else.
* Upgrade from version 3.4.2 to 3.7.10.
Nothing has made any difference. Any ideas? Anything I could try or
any ways to debug this?
It's the Primary Key that you're using cause for every INSERT it is
checked if unix_time is already present in a record.
So the question is if you really need unix_time as a PK
greetings
oliver
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
