Speed improvements based on 64-bit MinGW.
SQLite 3.14.2, built with MinGW 64-bit. -
https://sourceforge.net/projects/mingw-w64/?source=typ_redirect
gcc -m64 -O2 -DSQLITE_THREADSAFE=0 shell.c sqlite3.c -o sqlite3.exe
gcc -g -shared extension-functions.c -o extension-function.dll
Built with SQLITE options of default
Module SQL Script (146k records) T1 Sec T2 Sec T3 Sec T4 Sec Delta
Delta Delta
Processing Module 1 of 8: (CTEs) 138 110 92 49
125% 150% 282%
Processing Module 2 of 8: 26 20 17 14 130%
153% 186%
Processing Module 3 of 8: 34 28 23 18 121%
148% 189%
Processing Module 4 of 8: 14 7 6 5 200%
233% 280%
Processing Module 5 of 8: 9 6 6 4 150%
150% 225%
Processing Module 6 of 8: 30 14 12 11 214%
250% 273%
Processing Module 7 of 8: (CTEs) 290 222 212 86 131%
137% 337%
Processing Module 8 of 8: 14 14 10 10 100%
140% 140%
Total Processing Time: 557 424 374 200 131%
149% 279%
Test 1: 32-Bit 3.13 (sqlite.org), No PRAGMA (Baseline)
Test 2: 32-bit 3.13 (sqlite.org), with PRAGMA but not temp_store = 2;
Test 3: 64 bit 3.14.2 MinGW, with PRAGMA but not temp_store = 2;
Test 4: 64 bit 3.14.2 MinGW, all PRAGMA
PRAGMAs used...
PRAGMA synchronous = 0;
PRAGMA read_uncommitted = 1;
PRAGMA locking_mode = exclusive;
PRAGMA journal_mode = Off;
PRAGMA temp_store = 2;
For reference both sets of tests on same machine; Windows 10, i5-3470 3.20GHz,
12GB RAM and on internal spinning HDD. There is a slight improvemnt (~5%
faster) going to SSD but wanted to stay consistent.
I don't plan on testing Cygwin, I am happy with the MinGW results.
Thanks everyone for your help.
Richard
-----Original Message-----
From: Rousselot, Richard A
Sent: Monday, August 15, 2016 12:20 PM
To: sqlite-users@mailinglists.sqlite.org
Cc: Rousselot, Richard A
Subject: Re: [sqlite] 64-bit SQLite3.exe
Speed improvements based on 64-bit Visual Studio build.
SQLite 3.14.0, built with MSVC 18.00.30723 for x64.
Built with VS options /O2 /Ot /Ox.
Built with SQLITE options of SQLITE_ENABLE_FTS3 SQLITE_ENABLE_STAT2
SQLITE_ENABLE_RTREE.
Module SQL Script (146k records) T1 Sec T2 Sec T3 Sec T4 Sec Sp Inc
Sp Inc Sp Inc
Processing Module 1 of 8: (CTEs) 138 110 96 56
125% 144% 246%
Processing Module 2 of 8: 26 20 18 15 130%
144% 173%
Processing Module 3 of 8: 34 28 26 21 121%
131% 162%
Processing Module 4 of 8: 14 7 7 6 200%
200% 233%
Processing Module 5 of 8: 9 6 5 4 150%
180% 225%
Processing Module 6 of 8: 30 14 13 11 214%
231% 273%
Processing Module 7 of 8: (CTEs) 290 222 194 100 131%
149% 290%
Processing Module 8 of 8: 14 14 11 11 100%
127% 127%
Total Processing Time: 557 424 372 227 131%
150% 245%
Test 1: 32-Bit 3.13 (sqlite.org), No PRAGMA (Baseline)
Test 2: 32-bit 3.13 (sqlite.org), with PRAGMA but not temp_store = 2;
Test 3: 64 bit 3.14.0 Visual Studio 18, with PRAGMA but not temp_store = 2;
Test 4: 64 bit 3.14.0 Visual Studio 18, all PRAGMA
PRAGMAs used...
PRAGMA synchronous = 0;
PRAGMA read_uncommitted = 1;
PRAGMA locking_mode = exclusive;
PRAGMA journal_mode = Off;
PRAGMA temp_store = 2;
I am still trying to figure out how to make a 64-bit build in MinGW. There is
a nice installer for 32-bit but not for 64-bit, go figure.
Hopefully I have the PRAGMAs right. If there is a better configuration for
speed, let me know.
Richard
This communication is the property of CenturyLink and may contain confidential
or privileged information. Unauthorized use of this communication is strictly
prohibited and may be unlawful. If you have received this communication in
error, please immediately notify the sender by reply e-mail and destroy all
copies of the communication and any attachments.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
