SQLite performance

[Jeff Dinsmore]

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

Topic

[Jim Kosloskey]

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.

[Jeff Dinsmore]

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

[Max Drown (Infor)]

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)

[Jeff Dinsmore]

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