[sqlite] Performance of newer versions

[Sergej Jurečko]

Something I've recently noticed when using SQLite over multiple threads
(with SQLITE_THREADSAFE=2). You must disable memstatus (with
-DSQLITE_DEFAULT_MEMSTATUS=0) otherwise that mutex will cause a lot of
contention.

Sergej

[sqlite] Insertion into the large DB drastically slows down at some point

[Sergej Jurečko]

> On modern PCs, SQLite's page cache does not have a large effect because
> of the file cache of the OS, but you should do, e.g.,
>  PRAGMA cache_size = -100;
> for a 1 GB cache (default is only a few MB).
Hitting the page cache is much cheaper as it does not involve a system call. 
Try opening a memory only db, versus placing sqlite on a ramdisk. A simple 
benchmark will show a large speed difference.

Sergej

[sqlite] Schema-less JSON SQLite DB?

[Sergej Jurečko]

I will probably get around to doing it in the next few months. Hopefully I
have the time, right now I'm very busy. I will post to this message board
when I'm done.


Sergej

[sqlite] Schema-less JSON SQLite DB?

[Sergej Jurečko]

An idea I?ve had a while ago was to implement functions for json documents
(using sqlite3_create_function_v2)

Json would be stored in a text column. You would need to define 2 functions:
- docget(document,key1,subval1,subval2,?)
- docset(document,key1,subval1,subval2,..,val)

Last parameter of docset is value you wish to set.

So for instance one would write
INSERT INTO mytable VALUES (1,?{key : 10, subdoc : {subkey : ?a"}}?);
SELECT id,doc FROM mytable WHERE docget(doc,?key") > 10;
SELECT id,doc FROM mytable WHERE docget(doc,?key?,?subdoc?,?subkey?) = ?a?;
UPDATE doc FROM mytable WHERE id=1 SET docset(doc,?key?,12);

One could even implement indexes on keys within documents using additional
tables.

What do you guys think? Is it stupid, could it be improved?


Sergej