-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
I'm running into a problem using the experimental precompiled query API. Yes, I know it's experimental, but this is pretty serious, and no one on this list, or google has posted anything similar.
In short, when I switch to precompiled queries, according to gprof, it looks like the DB is being synched after every call, even though I've issued PRAGMA synchronous=OFF. As a result, using precompiled queries is an order of magnitude slower.
I need to squeeze every last bit of performance I can, as I've only got two simple queries that I'm using (an insert or an update) and I'm going to be issuing billions of them. I only have one table with 6 columns numerical columns. The primary key is composed of two columns. However, the PRIMARY KEY() in the CREATE TABLE declaration doesn't have an effect, and neither does the presence or absence of indices.
I'm running under OS X 10.3 (compiled with --disable-shared). Below are the top consumers in my gprof output. Anyone have any ideas? Especially the _moncount, mcount functions (which aren't part of SQLite, I realize)? The program runs below issue a total of 100k inserts.
Thanks in advance, Dan
With Precompiled Headers - -------------------------
% cumulative self self total
time seconds seconds calls ms/call ms/call name
18.7 4.98 4.98 _moncount (5175)
12.8 8.39 3.41 mcount (265)
4.9 9.70 1.31 99103 0.01 0.12 _sqliteVdbeExec [6]
3.4 10.60 0.90 _szone_malloc [19]
3.2 11.44 0.84 1107279 0.00 0.00 _sqliteOsRead [22]
3.2 12.28 0.84 _szone_free [23]
3.0 13.08 0.80 1013033 0.00 0.00 _sqliteOsWrite [24]
2.8 13.83 0.75 1422484 0.00 0.00 _sqliteOsSeek [25]
1.7 14.29 0.46 14461558 0.00 0.00 _cellSize [32]
1.7 14.75 0.46 1981980 0.00 0.00 _initPage [21]
1.5 15.16 0.41 4714055 0.00 0.00 _fileBtreeKeyCompare [34]
1.5 15.55 0.39 2291775 0.00 0.00 _sqlitepager_get [9]
1.2 15.87 0.32 _szone_size [38]
1.2 16.18 0.31 _free [40]
1.1 16.48 0.30 98903 0.00 0.01 _sqliteOsClose [28]
1.1 16.77 0.29 _malloc_zone_malloc [42]
1.1 17.05 0.28 395610 0.00 0.00 _sqliteOsReadLock [39]
1.0 17.32 0.27 _bcmp [46]
1.0 17.58 0.26 2756851 0.00 0.00 _sqlitepager_unref [29]
0.9 17.82 0.24 98903 0.00 0.00 _sqliteOsDelete [49]
0.9 18.06 0.24 _small_free_list_add_ptr [50]
0.9 18.29 0.23 _access [52]
0.8 18.51 0.22 1637526 0.00 0.00 _allocateSpace [45]
0.7 18.70 0.19 296709 0.00 0.00 _sqliteOsUnlock [53]
0.7 18.88 0.18 _malloc_zone_free [55]
0.6 19.05 0.17 _malloc [58]
0.6 19.21 0.16 1284142 0.00 0.00 _moveToChild [10]
0.6 19.37 0.16 98904 0.00 0.01 _sqliteOsOpenExclusive [31]
Without Precompiled Queries - ---------------------------
% cumulative self self total
time seconds seconds calls ms/call ms/call name
24.0 6.12 6.12 _moncount (5171)
20.4 11.31 5.19 mcount (270)
3.6 12.22 0.91 _szone_malloc [15]
3.5 13.11 0.89 _szone_free [16]
2.1 13.64 0.53 99103 0.01 0.04 _sqliteVdbeExec [8]
1.6 14.04 0.40 4712506 0.00 0.00 _fileBtreeKeyCompare [20]
1.5 14.42 0.38 7419421 0.00 0.00 _yy_find_shift_action [21]
1.5 14.79 0.37 7419418 0.00 0.00 _yy_shift [22]
1.4 15.15 0.36 __ZNSs9_M_mutateEmmm [23]
1.3 15.48 0.33 _szone_size [25]
1.2 15.79 0.31 3660038 0.00 0.00 _sqliteParser [9]
1.2 16.09 0.30 4154793 0.00 0.00 _sqliteGetToken [18]
1.1 16.37 0.28 _free [31]
1.1 16.64 0.27 3759386 0.00 0.00 _yy_reduce [10]
0.9 16.88 0.24 98901 0.00 0.02 _sqliteInsert [12]
0.9 17.11 0.23 2777662 0.00 0.00 _sqlitepager_unref [32]
0.9 17.34 0.23 _malloc_zone_malloc [36]
0.8 17.55 0.21 99103 0.00 0.04 _sqliteRunParser [6]
0.8 17.75 0.20 1657589 0.00 0.00 _allocateSpace [35]
0.8 17.95 0.20 _pthread_mutex_lock [40]
0.7 18.14 0.19 __ZNSs7replaceEN9__gnu_cxx17__normal_iteratorIPcSsEES2_mc [42]
0.7 18.32 0.18 296706 0.00 0.00 _fileBtreeMoveto [14]
0.7 18.49 0.17 8425798 0.00 0.00 _sqliteFree [45]
0.6 18.65 0.16 3759386 0.00 0.00 _yy_find_reduce_action [50]
0.6 18.81 0.16 3412585 0.00 0.00 _cellSize [48]
0.6 18.97 0.16 2769307 0.00 0.00 _sqliteStrICmp [49]
0.6 19.13 0.16 98903 0.00 0.00 _vxprintf [30]
0.6 19.29 0.16 _malloc [51]
0.5 19.43 0.14 1780278 0.00 0.00 _sqliteSetNString [37]
0.5 19.57 0.14 __ZNSs4_Rep9_S_createEmRKSaIcE [56]
0.5 19.71 0.14 __ZNSs6appendEmc [57]
0.5 19.85 0.14 _malloc_zone_free [58]
0.5 19.99 0.14 _pthread_mutex_unlock [59]
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin)
iD8DBQFAXxVvU/zGB5xpbyoRAqPhAJ9VgzKdJeVPer25ls3oYIQtVD2pvgCdGVlV niO9hnJonZeMSuhhvSOIPk8= =xrN7 -----END PGP SIGNATURE-----
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
