SQLite insert/updates slower in release mode?
-
I ran across a strange thing: my project, compiled in release mode (Qt 4.7.4, Windows) seems to be slower for database inserts. I measure the time it takes for the QSqlQuery.exec()-call. The SQL is prepare() d and within a transaction.
My test case includes inserting/updating about 1000 records. Compiled in debug mode, I get:rawUpdate::exec: 717.546 ms
rawInsert::exec: 99.0827 msThe same for release mode:
rawUpdate::exec : 734.678 ms
rawInsert::exe : 730.842 msI repeated that a couple of time - the behavior is robust.
Does anyone noticed a similar behavior or has a suggestion?thanks,
wernerps: I already use "pragma synchronous=OFF", "pragma cache_size=500000"
-
I have not a hint for your performance problem, but definitively I will deprecate the use of pragma synchronous = OFF for performance gain. Assuming that you are running the application within the same context (machine, operating system, cold database), I have only a trivial question: are you sure the number of inserted records are exactly the same? May be an insert depending on some run-time parameter that limits the number of tuples? Or could it be that the release is running with some indexes that the development release does not have? I know this is trivial, it's just an idea.
-
Can you provide a small example so we can verify your results?
As to cache_size. Keep in mind that it holds the nuber of pages, not bytes. Given a default page size of a minimum of 1024 bytes SQLite will use at least roughly 500 MB of cache. If you access your database from more than one thread it will use a mutliple of it and you might run out of memory fast.
If you are striving for performance you might take a look at journal_mode and locking_mode, probably temp_store as well. (Temporary) disabling the journal and locking will gain a huge performance boost. But be sure to understand what your are doing!