23 apr 2016, E.Pasma: > Hello, > I tried the scripts but.. > > createBigTable.sh is beyond the capacity of my system. Instead I > used SQL script like in > www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg08044.html > > My point is that the definition of the table is a waste of capacity, > even though it serves on many systems. Because it has a primary key > and also rowid which are not the same. Is there any practical use of > retaining the rowid? > > I tested the 1E7 case WITHOUT ROWID. The size of the database is > then reduced to 222M. Drop table is a matter of seconds (for me too > now). > > I may do further testing with more rows. Until then I have the > feeling that this will scale linearly and not show instable timings > any longer. > > Below is the output of my tests. > > Thanks, E Pasma
testing with more rows learned me that the "WITHOUT ROWID" version does not scale linearly either (case 2b below). Started yet another version, where the uuid column is not defined as primary key and where a tradional unique index is added instead. This DOES scale linearly (case 3 and 3b). I can't see why this new version (in case 3b) does not suffer from excessive response times. After all the UI is bigger as the PK-only index. But it is awesome that these tests are possible on a minimal system. Thanks, E Pasma case indexes #rows DBsize insert drop 1 rowid+PK 1E7 505M 6043s 241s 2 PK only 1E7 222M 1141s 2s 3 rowid+UI 1E7 480M 304s 44s 2b PK only 2E7 445M 58035s 886s 3b rowid+UI 2E7 970M 802s 61s log of case 1 create table uuid (uuid blob, primary key (uuid)) ; insert into uuid with r as (select 1 as i union all select i+1 from r where i<10000000) select randomblob(16) from r ; Run Time: real 6043.491 user 332.250625 sys 671.583469 .sys du -h west1.db* 505M west1.db begin ; drop table uuid ; Run Time: real 241.746 user 2.296482 sys 5.978103 rollback ; log of case 2 create table uuid (uuid blob, primary key (uuid)) without rowid ; insert into uuid with r as (select 1 as i union all select i+1 from r where i<10000000) select randomblob(16) from r ; Run Time: real 1141.098 user 294.535994 sys 573.902807 .sys du -h west2.db* 222M west2.db begin ; drop table uuid ; Run Time: real 1.974 user 0.844361 sys 1.095968 rollback ; log of case 3 create table uuid (uuid blob) ; insert into uuid with r as (select 1 as i union all select i+1 from r where i<10000000) select randomblob(16) from r ; Run Time: real 65.923 user 44.141960 sys 2.980705 .sys du -h west3.db* 238M west3.db create unique index uuidprime on uuid(uuid) ; Run Time: real 304.453 user 70.280531 sys 11.903746 .sys du -h west3.db 480M west3.db begin ; drop table uuid ; Run Time: real 44.634 user 2.050981 sys 2.988656 rollback ; log of case 2b create table uuid (uuid blob, primary key (uuid)) without rowid ; insert into uuid with r as (select 1 as i union all select i+1 from r where i<20000000) select randomblob(16) from r ; Run Time: real 58035.455 user 673.901377 sys 1518.352532 445M west2b.db .sys du -h west2b.db* begin ; drop table uuid ; Run Time: real 886.570 user 2.287946 sys 9.943783 rollback ; log of case 3b create table uuid (uuid blob) ; insert into uuid with r as (select 1 as i union all select i+1 from r where i<20000000) select randomblob(16) from r ; Run Time: real 119.143 user 88.404970 sys 6.073330 .sys du -h west3b.db* 479M west3b.db create unique index uuidprime on uuid(uuid) ; Run Time: real 683.631 user 145.164905 sys 25.329360 .sys du -h west3b.db 970M west3b.db begin ; drop table uuid ; Run Time: real 61.205 user 4.096998 sys 5.253853 rollback ;
