Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

2016-08-24 Thread Warren Young 
On Aug 17, 2016, at 10:38 PM, Keith Medcalf  wrote:
> 
> Same code, same compile options, same compiler version
> options -s -O3 -pipe -march=native -mtune=native -falign-functions=16 
> -falign-loops=16 -flto

Ah, good, actual science this time instead of apples-to-oranges. :)

Thank you for doing this and posting the results.

Even better, thank you for using a test program that comes with SQLite, so we 
can get numbers we can compare.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

2016-08-24 Thread J Decker 
CPU Core at 100% for entire test, x64 code is ~10% faster than x32 code.

So; IO bound things 32/64 doesn't matter so much; but compute bound,
through data already in memory gets significant improvments because of
addtional general purpose registers and optimal calling conventions for
things of fewer than 4 parameters. yields a better usage of your CPU?

and at 10% I said I'd be surprised if more than 5%; but potentially more if
I have a thing that fits in cache and I scan a lot?

subquery in a select was slower oddly enough... which was mentioned already
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

2016-08-19 Thread Dominique Devienne 
On Fri, Aug 19, 2016 at 3:47 AM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> *subquery in result set* test produces interesting outcome with the 64 bit
> version bucking the trend. Any ideas there?
>

One cannot tell, unless comparing profiles of both.

One thing to remember is that pointer-heavy data structures can be up to
twice as big
in memory in 64 bit, sometimes not fitting in cache anymore, and therefore
negatively
affecting performance at times. So gains from using the 64-bit chip
"natively" can be
lost to those cache misses. Just a broad generic statement of 32 vs 64-bit.
--DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

2016-08-18 Thread Michael Falconer 
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  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 - 50 INSERTs into table with no index.
> 0.781s
>  110 - 50 ordered INSERTS with one index/PK
> 1.266s
>  120 - 50 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 - 10 SELECTS, numeric BETWEEN, indexed
> 8.814s
>  161 - 10 SELECTS, numeric BETWEEN, PK.
> 9.282s
>  170 - 10 SELECTS, text BETWEEN, indexed...
> 3.241s
>  180 - 50 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 - 10 UPDATES, numeric BETWEEN, indexed
> 8.969s
>  240 - 50 UPDATES of individual rows...
> 1.641s
>  250 - One big UPDATE of the whole 50-row table
> 0.453s
>  260 - Query added column after filling
> 0.078s
>  270 - 10 DELETEs, numeric BETWEEN, indexed
> 2.235s
>  280 - 50 DELETEs of individual rows...
> 2.078s
>  290 - Refill two 50-row tables using REPLACE..
> 5.110s
>  300 - Refill a 50-row table using (b&1)==(a&1)
> 2.798s
>  310 - 10 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 - 50 INSERTs into table with no index.
> 0.797s
>  110 - 50 ordered INSERTS with one index/PK
> 1.250s
>  120 - 50 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 - 10 SELECTS, numeric BETWEEN, indexed
> 6.751s
>  161 - 10 SELECTS, numeric BETWEEN, PK.
> 7.229s
>  170 - 10 SELECTS, text BETWEEN, indexed...
> 2.719s
>  180 - 50 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 - 10 UPDATES, numeric BETWEEN, indexed
> 7.329s
>  240 - 50 UPDATES of individual rows...
> 1.516s
>  250 - One big UPDATE of the whole 50-row table
> 0.437s
>  260 - Query added column after filling
> 0.047s
>  270 - 10 DELETEs, numeric BETWEEN, indexed
> 2.047s
>  280 - 50 DELETEs of individual rows...
> 1.938s
>  290 - Refill two 50-row tables using REPLACE..
> 4.438s
>  300 - Refill a 50-row table using (b&1)==(a&1)
> 2.360s
>

[sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

2016-08-17 Thread Keith Medcalf 

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 - 50 INSERTs into table with no index.0.781s
 110 - 50 ordered INSERTS with one index/PK1.266s
 120 - 50 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, unindexed3.032s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..3.063s
 150 - CREATE INDEX five times.2.641s
 160 - 10 SELECTS, numeric BETWEEN, indexed8.814s
 161 - 10 SELECTS, numeric BETWEEN, PK.9.282s
 170 - 10 SELECTS, text BETWEEN, indexed...3.241s
 180 - 50 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 - 10 UPDATES, numeric BETWEEN, indexed8.969s
 240 - 50 UPDATES of individual rows...1.641s
 250 - One big UPDATE of the whole 50-row table0.453s
 260 - Query added column after filling0.078s
 270 - 10 DELETEs, numeric BETWEEN, indexed2.235s
 280 - 50 DELETEs of individual rows...2.078s
 290 - Refill two 50-row tables using REPLACE..5.110s
 300 - Refill a 50-row table using (b&1)==(a&1)2.798s
 310 - 10 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 - 50 INSERTs into table with no index.0.797s
 110 - 50 ordered INSERTS with one index/PK1.250s
 120 - 50 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, unindexed2.860s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..2.813s
 150 - CREATE INDEX five times.2.219s
 160 - 10 SELECTS, numeric BETWEEN, indexed6.751s
 161 - 10 SELECTS, numeric BETWEEN, PK.7.229s
 170 - 10 SELECTS, text BETWEEN, indexed...2.719s
 180 - 50 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 - 10 UPDATES, numeric BETWEEN, indexed7.329s
 240 - 50 UPDATES of individual rows...1.516s
 250 - One big UPDATE of the whole 50-row table0.437s
 260 - Query added column after filling0.047s
 270 - 10 DELETEs, numeric BETWEEN, indexed2.047s
 280 - 50 DELETEs of individual rows...1.938s
 290 - Refill two 50-row tables using REPLACE..4.438s
 300 - Refill a 50-row table using (b&1)==(a&1)2.360s
 310 - 10 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