Oh, CRUD! SQLite is not performant by default.

Today, I submitted a job application, in which I was required to list certain skills that I have. One of the advantages of interacting with people whom specialise in recuiting IT/development personel is that the person with whom I was dealing was sharp enough to spot an error in my application and gave me the opportunity to clarify/correct my response before passing it on to the potential employer. I really appreciate working with people who know what they're doing and do their jobs properly. (it's one of the reasons why I prefer to work with/for women.) I was also particularly pleased to find that a recruitment agency with which I've previously dealt is actually still in existence and I was misled to believe otherwise.

Anyway, on to the topic at hand: Compared to concurrent/server-based relational database management Systems (RDBMS) like MySQL and Postgre, SQLite is pretty lightweight (as the name implies) and easy to use. Libraries for many languages (including C#, C++, Java, PHP and Python) are either inbuilt or easily available. However, it may not be the best option for certain domains (particularly Web-based solutions).

As far as I understand, SQLite is designed and implemented to optimise file/storage size. However, the trade-off is that it isn't as performant as other relational database (RDB) systems in terms of its operations, particularly those involving create/[read/]update/delete (CRUD) statements/tasks. It generally doesn't have to be, given that it's intended to be utilised in single-use ad single-access applications (as opposed to a server-based RDB like MySQL or Postgre). There are, however, a number of practices/techniques that can be leveraged to minimise that hit to some degree. Here's a quick 101 on some of them:

• Wrap all CRUD statement blocks in transaction batches (using statements). This is generally good practice, anyway, regardless of which implementation/variant of SQL you use (MySQL, Postgre or SQLite).
• statements (either in your SQL or the software you use to interact with DBs, such as DB Browser or Menial) to optimise performance. SQLite is not optimised for it by default. The downside is that by altering the configuration/setup, you increase the risk of data/DB corruption (which isn't a rare occurrence; many a time I've had to attempt to fix a DB or replace it with a recent backup, so much so that I have a habit of making a backup of the entire DB before making any non-trivial change).
  • Setting PRAGMA TEMP_STORE) instead of ) Marginally increases performance when working with temporary objects.
  • Setting PRAGMA JOURNAL_MODETRUNCATE, rather than (the default), makes logging and performance significantly faster. Setting it to is faster still, but there's high risk of corrupting the data in the DB with this option.
  • PRAGMA SYNCHRONOUS to squeeze the last bit of significant performance (3-4x) out of SQLite. Anything other than FULL (the default, ensuring maximum safety/recovery on system crash/failure) will likely corrupt your data. While this can offer quite a performance boost, changing this setting should only be done on *NIX-based machines with high stability and uptime (typically servers, where you should be using a server-based RDBMS anyway), since system and power failures can potentially corrupt the DB.
  • PRAGMA LOCKING_MODEEXCLUSIVE only if one application will ever use the DB at a time. (This restricts the maximum number of concurrent connections to one, which is how you use an SQLite DB, IMO. Otherwise, use a server-based DBMS or build some sort of task queue daemon/service to handle interactions with the DB, which is essentially an RDBMS anyway and probably not worth the effort.)
  • While you increase the value of/for PRAGMA CACHE_SIZEPRAGMA PAGE_SIZE, they seem to have minimal practical impact (and only on Windows machines). personally, I wouldn't bother.
• Create indexes on the frequently-accessed/used columns/fields in tables. This helps with the time it takes to perform queries. However, overuse of indexes can be just as detrimental as having none whatsoever. (Anyone whom has heavily relied on Drupal's Views module can attest to this, which is why I avoid it and write custom queries for modules as much as possible when doing Drupal development.) The general rule for creating indexes is "as few as possible, but only as many as necessary".
• One of the optimisations that I suggest for other RDBs is to limit the number of text-based fields (and maximum permitted length) in tables. However, this doesn't apply to SQLite due to the way it stores data and is already optimised to do so.
• Use the query planner (EXPLAIN QUERY PLAN on a query) to look for bottlenecks. "SEARCH table" in the output is good, but "SCAN table" is bad. (See the Augmented Mind link in "Resources" for details.)
• If possible, rewrite queries that use (particularly ) to use other comparisons (possibly combined with ), since initially selecting all data from a table and filtering is an expensive operation. (I've personally experienced being criticised for this in code reviews, particularly on MySQL code that offers more options than SQLite does.)
• If possible, rewrite all UPDATE ... WHERE ... statements to use REPLACE INTO ... syntax. This has the advantage of being shorthand for "INSERT OR UPDATE [INTO] ..." personally, i've never got the hang of statements (although I probably should). They seem convoluted and esoteric to me and I'm a big fan of the principles "simple is better than complex" and "if the implementation is hard to explain[/understand], it's a bad idea".
• Measure everything (both before and after) when making an optimisation. As the late great Donald Knuth noted, "premature [and unnecessary] optimisation is the root of all evil [in software development]."

Remember that changing the default values of settings can boost performance, but it also increases the risk of data corruption. If performance is really that important to you (particularly for concurrent connections), rather use a server-based RDBMS like MySQL or Postgre.

Thumbnail image: Photo by Cottonbro Studios on Pexels