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 ms

    The same for release mode:

    rawUpdate::exec : 734.678 ms
    rawInsert::exe : 730.842 ms

    I repeated that a couple of time - the behavior is robust.
    Does anyone noticed a similar behavior or has a suggestion?


    ps: 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!

  • You may also want to make sure you're managing your transactions efficiently, placing a BEGIN...COMMIT around the bulk insert.

Log in to reply

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.