Performance comparison (2016)

[brody4hire]

Here are the results I get when running https://github.com/brodybits/Cordova-sqlite-perftest on Android with cordova-sqlite-storage (built with sqlite 3.8.10.2) and this version (built with sqlite 3.12.2):

Device: Acer Z520 (dual-SIM) with Android 4.4.2

Rebooted before the following tests:

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
18911	59205	18235	4182	123132

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.12.2):

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
3863	13020	4599	1416	112183

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
8127	36331	5740	3921	95598

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.12.2) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
3930	14057	3279	1489	106826

So far the bulk insert and volume read tests execute on Android in less than half the time in this plugin version compared to cordova-sqlite-storage.

Next steps:

• test with most recent sqlite version (3.14.1)
• comparison test on iOS and Windows

[brody4hire]

Here are more results testing customized builds with recent sqlite versions:

Rebooted again before the following tests:

Custom legacy version based on cordova-sqlite-storage 1.4.7, built with sqlite 3.14.1:

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
13691	41189	7120	3941	160825

Custom cordova-sqlite-evcore-extbuild-free 0.8.1, built with sqlite 3.14.1:

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
3921	15095	6944	1428	133724

Custom legacy version based on cordova-sqlite-storage 1.4.7, built with sqlite 3.14.1 repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
7952	33648	7140	3893	142785

Custom cordova-sqlite-evcore-extbuild-free 0.8.1, built with sqlite 3.14.1 repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
5200	13741	5773	1535	154074

Rebooted yet again before the following tests:

Custom legacy version based on cordova-sqlite-storage 1.4.7, built with sqlite 3.12.2:

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
11393	36202	9886	3927	141333

Custom legacy version based on cordova-sqlite-storage 1.4.7, built with sqlite 3.12.2 repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for volume read (ms)	volume read test (ms)	repeated update / read (ms)
8183	36316	5673	3913	152965

Using a newer version of sqlite does not seem to significantly affect the bulk insert or volume read tests. But for some reason, newer versions of sqlite seem to lead to longer repeated update/read test times.

One important explanation is at: http://sqlite.org/releaselog/3_13_0.html. The default cache size was increased from 1024 to 4096, documented as a "potentially disrupting change". Since this project already uses 3.14.0 for iOS and Windows, I expect these platforms to be affected by this change. I think the solution would be to redefine SQLITE_DEFAULT_PAGE_SIZE in the iOS and Windows versions to be more consistent with the other project versions.

Next steps:

• performance measurements and comparisons on iOS and Windows
• fix SQLITE_DEFAULT_PAGE_SIZE on iOS and Windows then test again
• consider upgrading the Android build to 3.14.1 with SQLITE_DEFAULT_PAGE_SIZE fixed

[brody4hire]

Preliminary Windows 10 mobile test results from yesterday:

Device: Microsoft Lumia 550 with Windows 10 Mobile 1 GB Version: 1607 OS build 10.0.14393.67

Rebooted before the following tests:

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
15046	53604	5210	1632	43328

cordova-sqlite-evcore-extbuild-free 0.8.0 (sqlite 3.12.2 3.8.10.2):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
14994	53397	5017	1643	44419

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.14.0):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
14127	47848	4997	1939	46203

cordova-sqlite-evcore-extbuild-free 0.8.1 custom built with sqlite 3.14.1:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
14015	48826	5019	1697	46930

[brody4hire]

Current iOS results:

Device: iPhone 4S, iOS 8.1.2

Rebooted before the following tests:

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
11603	75387	3260	1482	73423

cordova-sqlite-evcore-extbuild-free 0.8.0 (sqlite 3.12.2 3.8.10.2):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
12224	77781	3540	1493	74260

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.14.0):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
11196	71364	3129	1909	79410

cordova-sqlite-evcore-extbuild-free 0.8.1 custom built with sqlite 3.14.1:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
11348	70712	3116	1721	79627

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
11850	74416	3261	1505	74289

cordova-sqlite-evcore-extbuild-free 0.8.0 (sqlite 3.12.2 3.8.10.2) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
12506	77119	3798	1480	74235

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.14.0) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
11266	70123	3235	1594	79381

cordova-sqlite-evcore-extbuild-free 0.8.1 custom built with sqlite 3.14.1 repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
11161	72436	3225	1734	79487

[brody4hire]

Test notes:

• extra bulk insert test inserts 20K records with just over 100 characters to a new test table
• bulk insert test inserts 70K records with just over 100 characters to a new test table
• populate for read step adds 5K records with just over 1000 characters to a new test table
• read test first does SELECT COUNT (*) to check the count then reads the 5K records and checks the data
• repeated update / read test first INSERTs a single record with just over 200 characters into a new test table then repeats the following 2000 times: UPDATE the data, SELECT the data, and check the result

The test table is deleted after every step except for populate for read.

General note(s):

• The iOS and Windows platform versions are almost the same as cordova-sqlite-storage. It is desired to port the iOS flat JSON enhancements from https://github.com/litehelpers/Cordova-sqlite-evplus-legacy-free to this version (iOS memory fix #3).
• In this version, cordova-sqlite-storage, and some other versions it is possible to use the built-in (AOSP) Android SQLite database classes using the androidDatabaseImplementation: 2 setting. This scenario is not covered by the performance measurements so far and is expected to be slower.
• The SQLCipher version uses https://github.com/sqlcipher/android-database-sqlcipher which is based on the AOSP SQLite database classes and is expected to be slower (especially when encryption is actually used).
• I recently discovered that SQLite 3.12.0 changed the default page size from 1024 to 4096, though it is possible to use compiler flags to go back. Ref: http://sqlite.org/pgszchng2016.html

Conclusions so far:

• On Android this plugin version can handle big data transactions in less than half the time as cordova-sqlite-storage, both for bulk INSERT transactions and large SELECT (read) queries. This would remain true even if cordova-sqlite-storage would use a more recent version of SQLite.
• For small repeated transactions this version does not show any significant advantage over cordova-sqlite-storage and may be a little slower.
• Upgrading SQLite from 3.8.10.2 to 3.14.1 is beneficial for large transactions and detrimental for small transactions.
• Upgrading SQLite from 3.8.10.2 to 3.12.2 is beneficial for large transactions and detrimental for small transactions on Android.
• Upgrading SQLite from 3.12.2 to 3.14.1 has a negative performance impact on both small and large transactions on Android.

TODOs and other ideas:

• Integrate flat JSON fix for iOS from https://github.com/litehelpers/Cordova-sqlite-evplus-legacy-free and test again (iOS memory fix #3)
• Test SQLite 3.12.2 vs 3.14.1 on iOS and Windows.
• Consider testing SQLite versions in between such as 3.11.0/3.13.0
• Similar performance tests with recent versions of SQLite, using the older default page/cache sizes as documented in http://sqlite.org/pgszchng2016.html.
• Also test built-in Web SQL performance on Android and iOS. This may only be possible for the repeated update/read and maybe populate/read test scenarios.
• Consider other scenarios that may better isolate the effects of JSON improvements, JavaScript/native code improvements, SQLite version, etc.

[brody4hire]

Results of some further repeated testing on Android:

Rebooted before the following tests:

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
11084	42350	5324	3912	112452

Custom legacy version based on cordova-sqlite-storage 1.4.7, built with sqlite 3.12.2:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8279	35383	7104	3916	118383

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.12.2):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
3867	13204	4601	1485	108361

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
9796	37821	8447	3983	100250

Custom legacy version based on cordova-sqlite-storage 1.4.7, built with sqlite 3.12.2 repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
7978	33553	6014	3913	116207

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.12.2) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
3826	13124	3314	1512	103679

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2) repeated again:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8141	36698	5226	3933	97044

Custom legacy version based on cordova-sqlite-storage 1.4.7, built with sqlite 3.12.2 repeated again:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
9103	34471	4596	3889	110575

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.12.2) repeated again:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4698	11929	3273	1540	112563

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2) repeated yet again:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
9104	35633	5569	3929	101662

Custom legacy version based on cordova-sqlite-storage 1.4.7, built with sqlite 3.12.2 repeated yet again:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8495	34079	3911	3872	106243

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.12.2) repeated yet again:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
3850	13155	3249	1498	100083

[brody4hire]

NOTE: The previously reported test results for Android and iOS came from debug builds on brody4hire/cordova-sqlite-perftest@d227577. (I cannot tell for sure whether the Windows preliminary results above is from Debug or Release build.) A quick test with release builds (using Cordova-sqlite-perftest 0.0.2 brody4hire/cordova-sqlite-perftest@add72ed with very minor changes) on Android and iOS did not show significant differences.

Upcoming steps:

• Updating https://github.com/brodybits/Cordova-sqlite-perftest
• Test with somewhat newer Android/iOS devices
• Test with newer SQLite release such as 3.15 with both old and new default values of SQLITE_DEFAULT_PAGE_SIZE and SQLITE_DEFAULT_CACHE_SIZE (these were changed in SQLite 3.12.0)

[brody4hire]

Minor update to Cordova-sqlite-perftest needed since a bulk insert of 60/70K records with cordova-sqlite-storage causes the test to log an OutOfMemoryError and then hang on my new Samsung Galaxy J3 (2016) Android 5.1.1 test device (see storesafe/cordova-sqlite-storage#573 for details).

[brody4hire]

Results of testing Cordova-sqlite-perftest 0.0.3 release build on new Android device: Samsung Galaxy J3 (2016) model number SM-J320F (dual-SIM) Android version 5.1.1 (device reserved for testing)

Rebooted (recently) before the following tests:

Web SQL:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1776	4336	909	797	19423

cordova-sqlite-storage 1.4.8 (sqlite 3.8.10.2 with older default page/cache size):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4460	11061	2094	1893	25860

cordova-sqlite-storage 1.4.8 with built-in android.database implementation (androidDatabaseImplementation: 2 setting):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
5561	14272	2425	2077	21257

cordova-sqlite-evcore-extbuild-free 0.8.2 (sqlite 3.12.2 with newer default page/cache size):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2447	5507	1319	945	25110

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0 (newer default page/cache size):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4161	10335	1960	1862	26648

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4317	10808	2034	1894	26259

cordova-sqlite-evcore-extbuild-free (0.8.2) custom with sqlite 3.15.0 (newer default page/cache size):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2395	5375	1283	934	25147

cordova-sqlite-evcore-extbuild-free (0.8.2) custom with sqlite 3.15.0 (newer default page/cache size), FTS5 and JSON1 enabled:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2393	5318	1268	928	25133

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size, FTS5 and JSON1 enabled:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4296	10815	2017	1905	28467

Web SQL repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1759	4345	851	805	19370

Web SQL repeated extra:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1768	4271	864	807	19557

cordova-sqlite-storage 1.4.8 (sqlite 3.8.10.2 with older default page/cache size) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4533	11116	2098	1907	27846

cordova-sqlite-storage 1.4.8 with built-in android.database implementation (androidDatabaseImplementation: 2 setting) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
5573	14322	2438	2082	23515

cordova-sqlite-evcore-extbuild-free 0.8.2 (sqlite 3.12.2 with newer default page/cache size) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2472	5566	1316	937	27528

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0 (newer default page/cache size) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4135	10658	1974	1871	29193

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4860	12243	2291	2000	28372

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size, FTS5 and JSON1 repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
4298	10797	2044	1877	28441

cordova-sqlite-evcore-extbuild-free (0.8.2) custom with sqlite 3.15.0 (newer default page/cache size) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2391	5312	1250	936	27195

cordova-sqlite-evcore-extbuild-free (0.8.2) custom with sqlite 3.15.0 (newer default page/cache size), FTS5 and JSON1 enabled repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2470	5331	1290	933	27180

extra preliminary results:

cordova-plugin-sqlite-2 1.0.4 (built-in android.database implementation):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
3158	7991	1617	2315	16408

cordova-sqlcipher-adapter 0.1.8 (SQLCipher for Android 3.5.4) with no encryption key used:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
5398	13564	2406	2389	29931

[brody4hire]

Results of testing Cordova-sqlite-perftest 0.0.3 release build on new iOS device: iPhone 5S with iOS 10.0.2 (device reserved for testing)

Rebooted before the following tests:

Web SQL:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
937	2114	759	233	6354

cordova-plugin-sqlite-2 1.0.4 (built-in sqlite3 library):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1398	3520	689	346	30058

cordova-sqlite-storage 1.4.8 (sqlite 3.8.10.2 with older default page/cache size):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2188	7239	1313	496	35688

cordova-sqlite-evcore-extbuild-free 0.8.2 (sqlite 3.14.0 with newer default page/cache size, no JSON enhancement for iOS):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2033	6126	804	315	33848

cordova-sqlite-evplus-legacy-attach-detach-free 0.8.2-pre2 (sqlite 3.8.10.2 with older default page/cache size, flat JSON enhancement for iOS):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1263	2911	609	504	33634

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size, FTS5 and JSON1 enabled:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2102	6233	1286	519	35775

cordova-sqlite-storage (1.4.8) custom with sqlite apple-osx 06014163 "3.15", older default page/cache size, FTS5 and JSON1 enabled:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2100	6314	835	331	35053

cordova-sqlite-storage (1.4.8) custom with sqlite apple-osx 06014163 "3.15", newer default page/cache size, FTS5 and JSON1 enabled:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2015	6126	762	306	31021

cordova-sqlite-evplus-legacy-attach-detach-free (0.8.2-pre2) flat JSON enhancement, custom with sqlite 3.15.0 (newer default page/cache size); FTS5, JSON1, and other flags:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1170	2654	575	473	31023

cordova-sqlite-evplus-legacy-attach-detach-free (0.8.2-pre2) flat JSON enhancement, custom with sqlite apple-osx 06014163 "3.15" (newer default page/cache size); FTS5, JSON1, and other flags:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1183	2742	594	469	31315

Web SQL repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
915	2104	744	266	5706

cordova-plugin-sqlite-2 1.0.4 (built-in sqlite3 library) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1383	3398	644	282	30921

cordova-sqlite-storage 1.4.8 (sqlite 3.8.10.2 with older default page/cache size) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2119	6428	1328	541	32065

cordova-sqlite-evcore-extbuild-free 0.8.2 (sqlite 3.14.0 with newer default page/cache size, no JSON enhancement for iOS) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2024	6159	779	306	30814

cordova-sqlite-evplus-legacy-attach-detach-free 0.8.2-pre2 (sqlite 3.8.10.2 with older default page/cache size, flat JSON enhancement for iOS) repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1283	2970	608	516	31677

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size, FTS5 and JSON1 enabled repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2055	6038	1236	577	34439

cordova-sqlite-storage (1.4.8) custom with sqlite apple-osx 06014163 "3.15", older default page/cache size, FTS5 and JSON1 enabled repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2084	6264	1265	566	38139

cordova-sqlite-storage (1.4.8) custom with sqlite apple-osx 06014163 "3.15", newer default page/cache size, FTS5 and JSON1 enabled repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
2032	6131	786	435	31548

cordova-sqlite-evplus-legacy-attach-detach-free (0.8.2-pre2) flat JSON enhancement, custom with sqlite 3.15.0 (newer default page/cache size); FTS5, JSON1, and other flags repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1156	2669	569	484	32300

cordova-sqlite-evplus-legacy-attach-detach-free (0.8.2-pre2) flat JSON enhancement, custom with sqlite apple-osx 06014163 "3.15" (newer default page/cache size); FTS5, JSON1, and other flags repeated:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
1188	2693	585	471	32817

[brody4hire]

Results of testing Cordova-sqlite-perftest 0.0.3 release build on Windows Mobile 10 device

Rebooted before the following tests:

cordova-sqlite-storage 1.4.8 (sqlite 3.8.10.2 with older default page/cache size):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
9633	23087	3471	1370	21781

cordova-sqlite-evcore-extbuild-free 0.8.2 (sqlite 3.14.0 with newer default page/cache size, no performance/stability enhancement for Windows:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8181	20243	3483	1442	20092

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size, FTS5 and JSON1 enabled:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8743	21476	3152	1366	19723

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, newer default page/cache size, FTS5 and JSON1 enabled:

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8330	20555	3419	1446	23962

cordova-sqlite-storage 1.4.8 (sqlite 3.8.10.2 with older default page/cache size) (repeated):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
9254	22427	3269	1351	19648

cordova-sqlite-evcore-extbuild-free 0.8.2 (sqlite 3.14.0 with newer default page/cache size, no performance/stability enhancement for Windows (repeated):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8594	20499	3361	1464	21530

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size, FTS5 and JSON1 enabled (repeated):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
6149	15656	2464	1116	18400

NOTE: In this case Visual Studio detached prematurely and the test was manually restarted.

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, older default page/cache size, FTS5 and JSON1 enabled (repeated again):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8767	21896	3127	1340	20254

cordova-sqlite-storage (1.4.8) custom with sqlite 3.15.0, newer default page/cache size, FTS5 and JSON1 enabled (repeated):

extra bulk insert (ms)	bulk insert test (ms)	populate for read (ms)	read test (ms)	repeated update / read (ms)
8403	20322	3433	1439	21544

Additional observation: when I ran the perftest on a busy Windows host (x86 build) before today I noticed that the "repeated read/update test" took much longer. I may run the comparison cases on the Windows host at some point. I think this is consistent with the observation that the "repeated read/update test" was also especially slow on my Acer mobile (which has a number of other apps running). Moving forward I think better conclusions can be drawn from the results on my dedicated test devices.

[brody4hire]

Some general observations and conclusions so far:

• In most cases (though not all) Web SQL is faster than the plugins tested.
• SQLite plugin 2 beats cordova-sqlite-storage
• This version beats the other tested plugins on Android in case of bulk inserts
• cordova-sqlite-evplus-legacy-attach-detach-free (with the flat JSON enhancement on iOS) beats the other plugins on iOS in case of bulk inserts (I plan to port the flat JSON enhancement to this version at some point)
• SQLite plugin 2 beats the other plugins in the "repeated update/read" tests.
• Using a newer version of SQLite and/or using the newer page/cache sizes sometimes results in a minor performance improvement, never any significant performance loss.
• Use of built-in android.database implementation gives slower bulk insert and faster "repeated update/read" test results.

Testing TODO: The existing "repeated update/read" test covers a situation that may not be typical. I would like to find a better repeated read/write performance test case.

Next steps:

• Continue testing this version with larger batches (at least 200K INSERTS) to verify this continues to work without crashing
• Update this and other plugin versions to use sqlite 3.15
• Port the flat JSON improvements for iOS to this version