Perfectly linear. The time waster in creating the records is the index with the completely separate copy of all the data and the native primary key (record number) into a duplicate structure (the index btree). Creating the index and the base table at the same time, while linear as well, is even slower (as would be expected since you are doing multiple times the I/O for each row inserted).
Anyway, behaviour is linear, both in data insertion, index generation, and dropping the table (which, as one would expect, takes only as much time as one would take to walk the pages and move them to the free list, which may include writing them to the journal). I don't see the issue you are having and "dropping" a table with 1e8 records and a single unique index takes about 30 seconds. Perhaps you have really slow busy-wait style I/O? The laptop this was run on has the same CPU as you do, and the single thread ran maxxed out (100% of a core) using about 12% total of the CPU (one core single execution unit). I/O is irrelevant for me as this has a very fast SSD. (As a side note, a very fast and well cached SSD is indeed faster than a well cached spinning disk -- not by a lot, but it is faster -- especially on cache misses -- which, with a good cache, only occur when the cache is cold). NOTE that I killed the 1e9 insert with the index update at insert time. Clearly doing it all in a single transaction does not work very well. >sqlite test.db < test.sql .timer on .eqp on select sqlite_version(); 3.13.0 Run Time: real 0.000 user 0.000000 sys 0.000000 Run Time: real 0.000 user 0.000000 sys 0.000000 Error: near line 5: no such table: x vacuum; Run Time: real 0.031 user 0.000000 sys 0.000000 pragma temp_store=1; Run Time: real 0.000 user 0.000000 sys 0.000000 pragma cache_size=65535; Run Time: real 0.000 user 0.000000 sys 0.000000 create table x (uuid blob not null); Run Time: real 0.016 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e2; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.031 user 0.000000 sys 0.000000 create unique index ux on x(uuid); Run Time: real 0.016 user 0.000000 sys 0.000000 drop table x; Run Time: real 0.031 user 0.000000 sys 0.000000 vacuum; Run Time: real 0.031 user 0.000000 sys 0.031250 create table x (uuid blob not null); Run Time: real 0.016 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e3; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.031 user 0.000000 sys 0.000000 create unique index ux on x(uuid); Run Time: real 0.015 user 0.000000 sys 0.000000 drop table x; Run Time: real 0.031 user 0.000000 sys 0.015625 vacuum; Run Time: real 0.032 user 0.000000 sys 0.000000 create table x (uuid blob not null); Run Time: real 0.031 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e4; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.031 user 0.015625 sys 0.000000 create unique index ux on x(uuid); Run Time: real 0.031 user 0.015625 sys 0.000000 drop table x; Run Time: real 0.016 user 0.000000 sys 0.000000 vacuum; Run Time: real 0.016 user 0.000000 sys 0.000000 create table x (uuid blob not null); Run Time: real 0.031 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e5; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.094 user 0.031250 sys 0.015625 create unique index ux on x(uuid); Run Time: real 0.109 user 0.078125 sys 0.000000 drop table x; Run Time: real 0.031 user 0.000000 sys 0.000000 vacuum; Run Time: real 0.031 user 0.000000 sys 0.000000 create table x (uuid blob not null); Run Time: real 0.032 user 0.000000 sys 0.015625 insert into x select randomblob(16) from generate_series where start=1 and stop=1e6; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.547 user 0.453125 sys 0.046875 create unique index ux on x(uuid); Run Time: real 1.230 user 1.125000 sys 0.046875 drop table x; Run Time: real 0.079 user 0.046875 sys 0.000000 vacuum; Run Time: real 0.047 user 0.000000 sys 0.015625 create table x (uuid blob not null); Run Time: real 0.031 user 0.000000 sys 0.015625 insert into x select randomblob(16) from generate_series where start=1 and stop=1e7; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 5.375 user 4.468750 sys 0.453125 create unique index ux on x(uuid); Run Time: real 15.948 user 14.671875 sys 0.812500 drop table x; Run Time: real 0.594 user 0.406250 sys 0.156250 vacuum; Run Time: real 0.125 user 0.000000 sys 0.093750 create table x (uuid blob not null); Run Time: real 0.015 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e8; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 51.468 user 45.500000 sys 4.656250 create unique index ux on x(uuid); Run Time: real 177.413 user 162.921875 sys 13.343750 drop table x; Run Time: real 7.219 user 3.843750 sys 3.328125 vacuum; Run Time: real 0.891 user 0.031250 sys 0.828125 create table x (uuid blob not null); Run Time: real 0.016 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e9; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 562.266 user 502.359375 sys 57.875000 create unique index ux on x(uuid); Run Time: real 3607.984 user 2197.453125 sys 478.156250 drop table x; Run Time: real 976.447 user 81.703125 sys 383.546875 vacuum; Run Time: real 2.527 user 0.046875 sys 2.390625 create table x (uuid blob not null primary key); Run Time: real 0.035 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e2; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.031 user 0.000000 sys 0.000000 drop table x; Run Time: real 0.032 user 0.000000 sys 0.000000 vacuum; Run Time: real 0.029 user 0.000000 sys 0.015625 create table x (uuid blob not null primary key); Run Time: real 0.030 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e3; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.034 user 0.000000 sys 0.000000 drop table x; Run Time: real 0.034 user 0.000000 sys 0.000000 vacuum; Run Time: real 0.036 user 0.000000 sys 0.000000 create table x (uuid blob not null primary key); Run Time: real 0.020 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e4; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.060 user 0.031250 sys 0.000000 drop table x; Run Time: real 0.040 user 0.000000 sys 0.000000 vacuum; Run Time: real 0.020 user 0.000000 sys 0.000000 create table x (uuid blob not null primary key); Run Time: real 0.028 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e5; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 0.387 user 0.328125 sys 0.015625 drop table x; Run Time: real 0.028 user 0.015625 sys 0.000000 vacuum; Run Time: real 0.034 user 0.000000 sys 0.000000 create table x (uuid blob not null primary key); Run Time: real 0.020 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e6; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 3.614 user 3.375000 sys 0.140625 drop table x; Run Time: real 0.100 user 0.062500 sys 0.000000 vacuum; Run Time: real 0.040 user 0.000000 sys 0.015625 create table x (uuid blob not null primary key); Run Time: real 0.043 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e7; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 46.307 user 42.921875 sys 2.812500 drop table x; Run Time: real 0.954 user 0.593750 sys 0.312500 vacuum; Run Time: real 0.184 user 0.015625 sys 0.140625 create table x (uuid blob not null primary key); Run Time: real 0.031 user 0.000000 sys 0.000000 insert into x select randomblob(16) from generate_series where start=1 and stop=1e8; --EQP-- 0,0,0,SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: Run Time: real 1765.842 user 801.453125 sys 889.921875 drop table x; Run Time: real 23.707 user 5.796875 sys 8.375000 vacuum; Run Time: real 1.031 user 0.015625 sys 0.984375 > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > Sent: Sunday, 17 April, 2016 06:44 > To: SQLite mailing list > Subject: Re: [sqlite] Is it possible that dropping a big table takes very > long > > 2016-04-17 12:03 GMT+02:00 Simon Slavin <slavins at bigfraud.org>: > > > > > On 17 Apr 2016, at 10:38am, Cecil Westerhof <cldwesterhof at gmail.com> > > wrote: > > > > > I start with a: > > > > > > > > > ?conn.setAutoCommit(false); > > > but that is not the same? > > > > Yes, that does the same as BEGIN ... END. At least, according to the > > documentation it does. > > > > But you caused me to look up how the JDBC works, especially for > operations > > which involve a lot of memory. It turns out that this is not a 'thin' > shim > > which just translates Java calls to SQLite. It's a 'thick' shim and > does > > lots of things between the two to make all its databases look like they > > work the same way. > > > > Another field I should expand my knowledge in. ;-)? > > > > The result of this is that almost everything you see resulting from your > > calls is done by JDBC, not SQLite. This includes whatever caused your > > initial query about some operations taking a long time. Whatever it is, > > it's probably some consequence of how JDBC works, not how SQLite works, > and > > experts on Java are going to understand it better than experts on > SQLite. > > > > You can probably verify this by downloading the SQLite shell tool and > > performing the same operations in it (e.g. DROP TABLE) as you do in your > > Java code. I'm betting you don't get the same slowdowns in the same > places. > > > > ?Another two hours before the database is filled and then I can start > experimenting on copies of it. > > ?Well the ?simple? exercise was not so simple, but it helps to understand > things better. :-) > > -- > Cecil Westerhof > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
