Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSql SQLite poor performance?
QtWS25 Last Chance

QSql SQLite poor performance?

Scheduled Pinned Locked Moved General and Desktop
4 Posts 4 Posters 4.0k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • V Offline
    V Offline
    vezprog
    wrote on last edited by
    #1

    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 = off

    The 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!

    1 Reply Last reply
    0
    • L Offline
      L Offline
      lgeyer
      wrote on last edited by
      #2

      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).

      1 Reply Last reply
      0
      • SGaistS Offline
        SGaistS Offline
        SGaist
        Lifetime Qt Champion
        wrote on last edited by
        #3

        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 ?

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        1 Reply Last reply
        0
        • C Offline
          C Offline
          ChrisW67
          wrote on last edited by
          #4

          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

          1 Reply Last reply
          0

          • Login

          • Login or register to search.
          • First post
            Last post
          0
          • Categories
          • Recent
          • Tags
          • Popular
          • Users
          • Groups
          • Search
          • Get Qt Extensions
          • Unsolved