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?
Often have questions like this?
Learn more efficiently, for free:
