QSql SQLite poor performance?
-
Hi,
I am currently developing a windows application that communicates to a linux system. The linux system is using single SQLite database file for its data storage...which is also shipped to the windows side occasionally for processing...that is pretty much the reason why I chose SQLite for a database solution.
The linux side performs very well, since I am writing single items (insert) to the database every 5 mS, the write time does well when I set the following parameters:
PRAGMA synchronous = off
PRAGMA journal_mode = offThe windows side also writes data to a database file every 5 mS but performs terribly compared to the windows side. The database open alone takes quite a bit of time.
For example: I have one routine that opens the database file, writes to two tables 1 datapoint each, and that operation alone takes well over a second.
Has anyone else run into performance issues with SQLite on windows? Or could anyone shed some light on the subject in case there is anything I am missing here? (please keep in mind I am unable to do bulk write routines, they all need to be single data point writes, and I am mostly working with integer data).
I have some experience with windows .mdb files and they absolutely FLY with reads and writes.
Thanks in advance!
-
I haven't seen such performance issues yet for SQLite on Windows and we shovel quite some data in and out of the database. For performance-critical sections we use foreign_keys = OFF, journal_mode = OFF, synchornous = OFF, locking_mode = EXCLUSIVE and temp_store = MEMORY (be aware that you will have to access the database at least once when changing the locking mode, for instance SELECT * FROM sqlite_master LIMIT 0).
As this only occours on Windows I suspect a caching resp. I/O problem. You might also try to increase the page / cache size (cache_size and page_size).
-
Hi,
I'm not sure i get your right. Do you have that database on something like a network share with a linux and a windows process, each writing to it at a 5ms rate ?
-
Hmmm, we seem to have lost posts after this morning's maintenance... I am sure answered this yesterday
Make sure that the inserts are occurring inside a transaction:
"INSERT is really slow - I can only do few dozen INSERTs per second":http://www.sqlite.org/faq.html#q19
