-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 secondscalls 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.3199103 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.3098903 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.2498903 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.1698904 0.00 0.01
_sqliteOsOpenExclusive [31]
Without Precompiled Queries
- ---
% cumulative self self total
time seconds secondscalls 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.5399103 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.2498901 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.2199103 0.00 0.04 _sqliteRunParser
[6]
0.8 17.75 0.20 1657589 0.00
