Nice Keith, and very topical as well as being informative. Note a couple of things that got my curiosity chip activating:
*subquery in result set* test produces interesting outcome with the 64 bit version bucking the trend. Any ideas there? Also it would appear *select* clauses demonstrate an above average gain from 64 bit. Lack of disk activity sprung to my mind but interested to hear any thoughts there. excellent information and thanks for sharing it. On 18 August 2016 at 14:38, Keith Medcalf <kmedc...@dessus.com> wrote: > > Same code, same compile options, same compiler version > options -s -O3 -pipe -march=native -mtune=native -falign-functions=16 > -falign-loops=16 -flto > > 32-bit GCC 4.9.3 > > >speedtest1 --size 1000 > -- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39 > a861713cc6a3868a1c89240e8340bc > 100 - 500000 INSERTs into table with no index..................... > 0.781s > 110 - 500000 ordered INSERTS with one index/PK.................... > 1.266s > 120 - 500000 unordered INSERTS with one index/PK.................. > 1.672s > 130 - 25 SELECTS, numeric BETWEEN, unindexed...................... > 1.281s > 140 - 10 SELECTS, LIKE, unindexed................................. > 3.031s > 142 - 10 SELECTS w/ORDER BY, unindexed............................ > 3.032s > 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed.................. > 3.063s > 150 - CREATE INDEX five times..................................... > 2.641s > 160 - 100000 SELECTS, numeric BETWEEN, indexed.................... > 8.814s > 161 - 100000 SELECTS, numeric BETWEEN, PK......................... > 9.282s > 170 - 100000 SELECTS, text BETWEEN, indexed....................... > 3.241s > 180 - 500000 INSERTS with three indexes........................... > 2.781s > 190 - DELETE and REFILL one table................................. > 3.111s > 200 - VACUUM...................................................... > 2.282s > 210 - ALTER TABLE ADD COLUMN, and query........................... > 0.094s > 230 - 100000 UPDATES, numeric BETWEEN, indexed.................... > 8.969s > 240 - 500000 UPDATES of individual rows........................... > 1.641s > 250 - One big UPDATE of the whole 500000-row table................ > 0.453s > 260 - Query added column after filling............................ > 0.078s > 270 - 100000 DELETEs, numeric BETWEEN, indexed.................... > 2.235s > 280 - 500000 DELETEs of individual rows........................... > 2.078s > 290 - Refill two 500000-row tables using REPLACE.................. > 5.110s > 300 - Refill a 500000-row table using (b&1)==(a&1)................ > 2.798s > 310 - 100000 four-ways joins...................................... > 5.320s > 320 - subquery in result set...................................... > 22.936s > 980 - PRAGMA integrity_check...................................... > 4.969s > 990 - ANALYZE..................................................... > 1.657s > TOTAL....................................................... > 104.616s > > 64-bit GCC 4.9.3 > > >speedtest1 --size 1000 > -- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39 > a861713cc6a3868a1c89240e8340bc > 100 - 500000 INSERTs into table with no index..................... > 0.797s > 110 - 500000 ordered INSERTS with one index/PK.................... > 1.250s > 120 - 500000 unordered INSERTS with one index/PK.................. > 1.609s > 130 - 25 SELECTS, numeric BETWEEN, unindexed...................... > 0.969s > 140 - 10 SELECTS, LIKE, unindexed................................. > 2.859s > 142 - 10 SELECTS w/ORDER BY, unindexed............................ > 2.860s > 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed.................. > 2.813s > 150 - CREATE INDEX five times..................................... > 2.219s > 160 - 100000 SELECTS, numeric BETWEEN, indexed.................... > 6.751s > 161 - 100000 SELECTS, numeric BETWEEN, PK......................... > 7.229s > 170 - 100000 SELECTS, text BETWEEN, indexed....................... > 2.719s > 180 - 500000 INSERTS with three indexes........................... > 2.266s > 190 - DELETE and REFILL one table................................. > 2.266s > 200 - VACUUM...................................................... > 1.735s > 210 - ALTER TABLE ADD COLUMN, and query........................... > 0.062s > 230 - 100000 UPDATES, numeric BETWEEN, indexed.................... > 7.329s > 240 - 500000 UPDATES of individual rows........................... > 1.516s > 250 - One big UPDATE of the whole 500000-row table................ > 0.437s > 260 - Query added column after filling............................ > 0.047s > 270 - 100000 DELETEs, numeric BETWEEN, indexed.................... > 2.047s > 280 - 500000 DELETEs of individual rows........................... > 1.938s > 290 - Refill two 500000-row tables using REPLACE.................. > 4.438s > 300 - Refill a 500000-row table using (b&1)==(a&1)................ > 2.360s > 310 - 100000 four-ways joins...................................... > 5.109s > 320 - subquery in result set...................................... > 23.006s > 980 - PRAGMA integrity_check...................................... > 4.563s > 990 - ANALYZE..................................................... > 1.501s > TOTAL....................................................... > 92.695s > > CPU Core at 100% for entire test, x64 code is ~10% faster than x32 code. > > > --- > Life should not be a journey to the grave with the intention of arriving > safely in a pretty and well preserved body, but rather to skid in broadside > in a cloud of smoke, thoroughly used up, totally worn out, and loudly > proclaiming "Wow! What a Ride!" > -- Hunter S. Thompson > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
