In a Python Bottle server using SQLite, I noticed that doing a DB commit after each INSERT is not efficient: it can use 100ms after each client request. Thus I wanted to improve this in How to commit DB changes in a Flask or Bottle app efficiently? .
I finally came to this solution, which is more or less a "debounce"-like method: if multiple SQL
INSERT happen during a 10-second timeframe, group all of them in a single DB commit.
What do you think of the following code? Is it safe to do like this?
(Note: I know that the use of a
global variable should be avoided, and replaced by a
class / object with attributes, etc. I'll do this, but this part is not really on topic here).
import bottle, sqlite3, random, threading, time @bottle.route('/') def index(): global committhread c = db.cursor() c.execute('INSERT INTO test VALUES (?)', (random.randint(0, 10000),)) c.close() if not committhread: print('Calling commit()...') committhread = threading.Thread(target=commit) committhread.start() else: print('A commit is already planned.') return 'hello' def commit(): global committhread print("We'll commit in 10 seconds.") time.sleep(10) # I hope this doesn't block/waste CPU here? db.commit() print('Committed.') committhread = None db = sqlite3.connect('test.db', check_same_thread=False) db.execute("CREATE TABLE IF NOT EXISTS test (a int)") committhread = None bottle.run(port=80)
If you run this code, opening http://localhost/ once will plan a commit 10 seconds later. If you reopen http://localhost/ multiple times, less than 10 seconds later, you will see that it will be grouped in the same commit, as desired.
Note: this method is not "Do it every 10 seconds" (this would be a classic timer), but rather: "Do it 10 seconds later; if another INSERT comes in the meantime, do all of them together". If there is no
INSERT during 60 minutes, with my method it won't do anything at all. With a timer it would still periodically call a function (and notice there's nothing to do).
Worth reading too: How to improve SQLite insert performance in Python 3.6?