SQLite Forum

Feature Request: support bulk value fetches
Thanks to all who shared corrections, alternatives and/or interest to this point.  I'd like to share some details of usage, as that might clarify things a bit and limit which options might be feasible for me (or others) going forward.

* In my code, there is only one writeable connection in use at a time, and only one thread can be making use of that connection at a time.  When a thread is interested in performing a write operation (potentially in concert with accompanying read operations), it must await its turn for the writeable connection.

* In my code, all read-only operations employ the same read-only connection.  When a transaction is committed on the writeable connection, that 'invalidates' the future use of the current read-only connection (as it is now stale), another read-only connection is made available, and any new read-only operation will employ the 'fresh' read-only connection.

* We don't want to offer a separate connection for each thread, as we could potentially have a hundred parallel reader threads.  Each connection requires its own chunk of memory for state and cache, and that doesn't scale well into the hundreds.

* Given that 'writer' threads have exclusive rights to the sole writeable connection, it doesn't seem possible that field data would change other than through that thread's actions.  That doesn't suggest that we can be careless (Larry's comment about my scheme doling out unprotected objects is certainly worth heeding, so that is much appreciated).

* Given that 'reader' threads are using a read-only connection, it doesn't seem possible that field data would change under their feet.  But ditto the previous bullet about taking care regardless.  However, I'd be curious to know what actions can invalidate the unprotected values returned from the read-only connection in my design.

Regarding the valid question from ddevienne about performance, the results are varied based on the particular workload, so it's hard to give concrete numbers for all situations.  I believe the benefit has much to do with the comparative number of values being fetched from each row by parallel threads.  Perhaps an extreme example will illustrate how certain threads might be unduly hampered.  This specific example is a mental exercise, and not one that I have actually executed (yet).

> Let's assume we have one thread that is iterating over 10,000 rows in some table, and fetching one value from each row.  Its pattern of operation would be to alternate between a 'step' call ('s') and a 'fetch' call ('f'):

> > <code>sfsf...</code>

> Let's assume we have another thread that is iterating over 10,000 rows in some other table, and fetching 19 values from each row.  Its pattern of operation would be 'step' ('S') and 19 'fetch' calls (each fetch being 'F'):


> Each of those 'step' and 'fetch' calls requires a lock on the same connection mutex.  The calls of those parallel threads would likely be interleaved as:

> > <code>SsFfFsFfFsFfFsFfFsFfFsFfFsFfFsFfFsFfFsFfSs...</code>

> So we only get 1 'step' in the 19-field table for every 10 'steps' of the 1-field table.  If we could instead group the 19 field fetches under a single mutex lock, the 19-field fetcher wouldn't be at a disadvantage, and each of the threads could 'step' at roughly the same rate:

> > <code>Ss(F19)fSs(F19)f...</code>

> So with the existing locking scheme in the library, the 1-field fetcher gets about 91% of the 'step' calls, and the 19-field fetcher gets about 9% of the 'step' calls (he's walking one-tenth as fast as the 1-field fetcher).  With the proposed locking scheme, both fetchers would get about 50% of the 'step' calls.  It eliminates the gross inequity in per-thread performance based simply on the number of values being fetched.

In my actual tests, some rows are fetching 30+ values, and those 'wider' fetches are far more likely to bump into contention from the other fetchers.  Either reducing the number of values fetched (where possible) or changing the 'fetch' locking scheme are effective at improving the performance of those particular iterations.

Regarding the suggestion from ddevienne about using my own locking, that could work.  But I think I'd first need to identify every sqlite call that would be doing locking if I allowed it (such as prepare, bind, step, reset, clear-bindings, finalize, checkpoint, etc.), and apply my own locking around each one of those calls.  I think I'd prefer to rely on sqlite for that as much as possible, to avoid overlooking something.

Answers to the latest questions from Larry:

* The lifetime of the objects being returned is very brief.  Shortly after being returned from sqlite, the integer/double/text values are copied into my own objects.

* Each of the dozens of reader threads could be reading from different tables, or reading different rows from the same table.  There is a very slight possibility of overlap in what they're scanning at a given time, but it's not very likely.

* I take advantage of sqlite's per-connection read cache, but can't really afford to maintain my own cache of data due to the huge amount of data in the database.  I have potentially tens of thousands of rows in hundreds of tables.

I think I'll heed Larry's initial guidance and make copies of the values at the lowest level (perhaps using 'dup' on the sqlite3_value objects) such that they are safe to use.  I'll then repost for more feedback.

Again, even if this doesn't turn out to be a suitable extension to the library, I appreciate the feedback and suggestions.