Clovertech

SQLite performance

› Clovertech Forums › Read Only Archives › Cloverleaf › Cloverleaf › SQLite performance

  • Creator
    Topic
  • February 15, 2011 at 10:16 pm #52275
    Jeff Dinsmore
    Participant

      I’m looking at building a cross-reference with SQLite that is updated daily.

      This will replace a solution that currently uses two CL tables that I update with a script, but have not yet automated the copy and purge caches stuff to make it fully automatic.

      I like the SQLite solution because it can be dynamically updated and it will store what I need in a single SQL table rather than two CL tables.

      I’ve done some preliminary time trials and CL table lookup is WAY faster than SQLite – something like 20 microseconds for the table lookup vs 6000 microseconds for SQLite. Now that’s still only about .006 seconds by my calculation, but it’s still something to think about.

      The real question is whether I can open the SQL database once and store the handle as a global variable (one per process? per thread?) that will be available across multiple iterations of a given XLATE or Tcl script. If SQLite has to open its database file for every message processed, I don’t think I’d want to do it that way.

      Do any of you have any experience/opinions you could share?

      Jeff Dinsmore
      Chesapeake Regional Healthcare

    • Creator
      Topic
    Viewing 3 reply threads
    • Author
      Replies
      • March 1, 2011 at 2:37 pm #73646
        Jim Kosloskey
        Participant

          Jeff,

          Did you ever come to any conclusion regarding this concern?

          email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 61 years IT – old fart.

        • March 1, 2011 at 9:47 pm #73647
          Jeff Dinsmore
          Participant

            I just went ahead and built a test vehicle.

            I didn’t attempt to keep the db handle open – I’m just opening, reading, closing for each message – and it seems to run acceptably fast for our purposes.

            I have an auto-updater that reads new values from a file daily and writes the master DB. There are about 17,000 rows and it deletes and rewrites all rows in about a second.

            In the DB open function, I built a check for locks that waits for a couple secnods if the DB is locked, then tries again.

            To load test, I ran ten separate processes each looking up each of the 17000 table values one-by-one – all from the same database – as fast as they could.

            I ran this load test through several times and was only able to cause one of the processes to wait on a lock one time.

            So, I’ve satisfied myself that:

            A) it’s fast enough

            B) locking shouldn’t be a problem (although, it needs to be handled)

            Jeff Dinsmore
            Chesapeake Regional Healthcare

          • March 4, 2011 at 2:21 pm #73648
            Max Drown (Infor)
            Keymaster

              If you were someday to consider keeping the sqlite session open or opening and closing the same db across multiple databases, take a minute to review the timeout command (ex. DBCMD timeout 2000).

              http://www.sqlite.org/tclsqlite.html#timeout

              -- Max Drown (Infor)

            • March 4, 2011 at 2:32 pm #73649
              Jeff Dinsmore
              Participant

                I looked at timeout, but opted for the busy method instead.

                Busy allows for custom handling of the timeout. I use it to send myself an email notification that a timeout has occurred.

                I used it for verification/load testing, but it will also let me know if I start to run into DB locking issues in real interfaces.

                Jeff Dinsmore
                Chesapeake Regional Healthcare

            • Author
              Replies
            Viewing 3 reply threads
            • The forum ‘Cloverleaf’ is closed to new topics and replies.