Advantages of Memory based sqLite databases

[doc hawk]

The difference between :memory: and the local hard disk is, in itself, stunning.

I initially set up with mysql, and (iirc) disk sqlite.

Whether it’s mysql or livecode’s fault, it isn’t (wans’t?) possible to to do a compound transaction to a mysql server.

So when I opened a “file” (a couple of tables per debtor for my software), it took *minutes* on a remote server to do what took a couple of seconds for a local sqlite file.

And an in-memory database was instantaneous.

If you need remote, there is a very many to one difference between Postgres (which can handle a transaction of several hundred queries at once) and mysql (which requires several hundred queries in sequence, with full latency applied to each one).

And another huge boost for going to in-memory sqlite.  (Don’t believe the bit about simultaneous access to sqlite files, it doesn’t quite work!).

So I settled on a remote Postgres, in which I stored the commands to update the in-memory sqlite.

The catch, of course, is that livecode never implemented the encrypted connection that Postgres supports out off the box.

If I was going to continue, I’d probably go to a local Postgres server on each machine with REPLICATE, and stay with in-memory sqlite—but barring any surprises, I’ll just retire instead.