On 17/06/2009 11:52 AM, Dennis Cote wrote:
> Jens Páll Hafsteinsson wrote:
>> Closing and opening again did not speed up steps 1-4, it actually slowed
>> things down even more. The curve from the beginning is a bit similar to a
>> slightly flattened log curve. When I closed the database and started the
>> test again, a similar curve appeared again, but now starting from where the
>> first run left off.
>>
>> I've been running the same 3.6.15 since this afternoon (the previous test
>> was using 3.6.14) and it seems to flatten out fairly quickly but it is
>> significantly slower (2.3 to 1.3 times slower, depending on where you
>> measure it using the data I have). I'm not that worried about that for the
>> time being; I'm just hoping it will stay flat.
>>
>> JP
>> On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote:
>>> 1. start a transaction
>>> 2. insert 1000 records
>>> 3. commit
>>> 4. repeat steps 1-3 100 times
>>> 5. delete everything from the table
>>> 6. Start again at step 1
>
> I suspect that you may be using a autoincrement id field and then
> running into the extra work (both CPU load and increased disk space)
> needed to handle the variable sized integer storage method used by
> SQLite. This would lead to the type of logarithmic growth you are
> seeing. The first few iterations used short single byte integer values,
> the next bunch use 2 byte integer values, etc. The autoincrement field
> would cause SQLite to continue at the same speed after restarting the
> application as you have described, since the next field values used
> would continue from where it left off at the end of the previous run.
Even after "delete everything from the table"; see below.
>
> I would have expected the time to stabilize on 3 byte values fairly
> qucikly, and then only change again when switching to values that
> required 4 bytes.
>
> This may be a part of the answer even if it is not the complete answer.
From Jens's description, he is writing only 1000 * (1 + 100) = 101,000
records before "delete everything from the table". A 3-byte variable
integer will hold a number 16K to 2M approx. 4-byte: 2M to 268M approx.
On the surface, should be OK. UNDER the surface:
sqlite> create table t (p integer primary key autoincrement, x text);
sqlite> select * from sqlite_master;
table|t|t|2|CREATE TABLE t (p integer primary key autoincrement, x text)
table|sqlite_sequence|sqlite_sequence|3|CREATE TABLE
sqlite_sequence(name,seq)
sqlite> insert into t(x) values('blah');
sqlite> insert into t(x) values('yadda');
sqlite> select * from sqlite_sequence;
t|2
sqlite> delete from t;
sqlite> select * from sqlite_sequence;
t|2 <<<<<<<<============== whoops
Documented (and good behaviour) -- never re-issue a key while the table
exists.
Possible solution (apart from DROP TABLE):
sqlite> delete from sqlite_sequence where name = 't';
sqlite> select * from sqlite_sequence;
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
