Data Persistence

[Richard Gaskin]

Mike Bonner wrote:

 > On the subject of sqLite/memory databases and preferences/data
 > persistence, rather than using an array, and doing the encode/decode
 > stuff, would it make sense to do the following..
 > 1. open an empty in memory database
 > 2. attach a disk based database
 > 3. copy the required table(s) from disk base to memory base
 >
 > At this point, for data reads you have a super fast sqlite in memory
 > database.  For updates, do a double update, both in memory, and disk.

Whether using LC-native LSON or going through the externals interface to 
manage the SQLite RDBMS, getting any data from disk always has 
tradeoffs, sometimes favoring one method over another.

ArrayEncode is a single function call, so it's not like it's onerous to use.

SQLite's b-tree is a good one, so while the steps needed internally to 
traverse it are vastly more complex than the steps needed to traverse an 
array's hash, both are handled in machine-compiled C so it's not a big 
deal either way.

This reiterates the point I'd made earlier:
 > It seems like this would be more efficient than re-encoding an array
 > every time there is an update and writing the whole thing out to disk
 > each time, rather than updating just whatever small change might need
 > to be made to sqlite.

This is the upside of an RDBMS's complex b-tree: disk paging.  As I'd 
written earlier, if the data size is large enough that LC's machine-code 
serialization is slower than SQLite's SQL parsing + machine-code b-tree 
traversal, then SQLite would be the winner in that instance where raw 
performance may offset the additional requirement mixing SQL scripting 
with LC Script, and of no longer having a completely self-contained EXE 
(only Mac embeds externals and drivers within the bundle; Win and Linux 
at that point require use of an installer to install the multiple parts 
where the standalone can find them and the user won't mess with them).

You'd be surprised how fast LC is for many things, though. I've written 
routines using flat files that were able to do simple queries in LC 
Script about 20% faster than SQLite.

But even there, it's about project requirement.  If you need richer 
querying relying on relationality, nothing scripted in LC will come 
close in raw performance to the well-honed relational engine in SQLite.

But also, remember that the file system itself is a b-tree-based storage 
system, well optimized for general use, automatically ACID (in journaled 
file systems), and super easy to use.  Clustering as with Canela's 
LiveCloud DB, or even as separate file per document/record, can be quite 
efficient for some needs.

SQLite is a wonderful toolkit.  But with so many ways to store, 
retrieve, and work with data, we can pick and choose for the task at hand.

At a certain point, discussions about "SQL vs <your favorite non-SQL 
solution here>" are too similar to those of the NoSQL world last decade 
when those started taking off.  None of the many passionate arguments 
have managed to rid the world of MongoDB, CouchDB, Neo4J, or any other 
non-SQL storage system, nor have they displaced the valuable role of a 
good RDBMS where relationality is needed.  We have more options today 
than ever before, so we can pick whatever gets the job done.

-- 
  Richard Gaskin
  Fourth World Systems
  Software Design and Development for the Desktop, Mobile, and the Web
  ____________________________________________________________________
  Ambassador at FourthWorld.com                http://www.FourthWorld.com