SQLite3 database insertion speed over SSHFS connection | Sololearn: Learn to code for FREE!
New course! Every coder should learn Generative AI!
Try a free lesson
+ 1

SQLite3 database insertion speed over SSHFS connection

I've created a small util to watch a text file on local PC. When the file changes (outside source updates the contents of the file) the util program reads the file, extracts each line which has a SQL insert query on it and sends it to a SQLite dB. I tested this locally and it's fast. Everything completed instantly. In reality I want to use a SQLite dB that is on a VPS (remote server). I mounted a connection to the VPS storage using sshfs and the program still works, but the real world insertion time is so much slower. What took half a second now takes almost 1.5 mins. I can see the SQLite journal file being written on the VPS when inserts happen. I've played a bit with those using PRAGMA but it still isn't very fast. I tried using a transaction around my insert query exec as well. Would sshfs most likely be the reason for the slow down? I am sending large (2000+) rows of data in each INSERT query. Is that the most efficient way to do this?

19th Jan 2021, 7:52 PM
Nathan Stanley
Nathan Stanley - avatar
0 Answers