On Fri, 17 Jun 2005, Sean Heber wrote:
>>> SQLite write (insert) performance doesn't seem very good to me so I'm
>>> trying to find ways to improve it in my application. I had this idea
>>> so I figured I'd run it past you folks who likely better know how the
>>> db works under the hood.
>>>
>>
>> did you wrap your inserts in a transaction?
>
>I don't think I can do that because the inserts come in at random
>intervals as they are generated by outside influences. I'm not just
>doing an import of existing data as the data is created on the fly by
>factors somewhat outside of my control. I tried to make an in-memory
>temporary table and insert into that and periodically start a
>transaction with an "insert into real_table select from memory_table"
>thing in bulk, but that didn't seem to make a significant
>difference. Perhaps I've got something else wrong.
If data is coming in slowly, then doing a transaction per data object is
no problem.
If data is coming in fast, then handling multiple data objects per
transaction is a must for efficiency.
Therefore, why not do both? Poll data objects to be processed, and insert
into the current transaction until no more data is available for some
threshold is reached:
process() {
while(1) {
int datacount = 0;
wait for data;
while(data available && datacount < threshold) {
get data from source;
if (0==datacount) {
begin transaction
}
insert data to db;
datacount++;
}
commit transaction;
}
}
In this case, the consumer will batch up to threshold data inserts,
increasing efficiency. If the producer stops producing data, the consumer
will find no more data and exit it's inner loop. Threshold prevents the
transaction and latency growing indefinitely.
For tuning, you could even make threshold dynamic. Start small, and
increase it's value to some maximum if the current threshold is exceeded.
>
>Thanks for the suggestion.
>
>l8r
>Sean
>
>
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
