Here are my most wanted features and they don't even require locking or
threading fu ;)
* I really would like some SQL syntax sugar that will
automatically include rows referenced by foreign keys in a table. I
haven't seen any other SQL database do this as all expect you to
explicitly include the conditions and joins which can become quite the
hassle.
* My second awesome feature request would be support for storing
prepared statements into the database file to be used at later times.
It's been documented that the creation of statements is a performance
hit so it would be nice if my application doesn't have to recreate these
statements every time it's executed. This would also cut down on the
amount of hard-coded SQL I have scattered throughout my C++ application
as I could instead use some sort of preprocessing tool to generate the
statements and merely reference those statements by name or some other
identifier in my code.
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Brad House
Sent: Monday, September 21, 2009 1:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Most wanted features of SQLite ?
>> It could probably benefit a large number of integrations to
>> have finer grained locking even if it could not be implemented for
all
>> integration types.
>
> It makes the system a great deal slower, since you need to keep
> checking all the levels of lock you have implemented. For instance,
>
> UPDATE props SET colour='black',condition='poor' WHERE
> description='fake sword'
>
> needs to check for locks on the file, the record, and three fields.
> That's five operations before you can even start to modify the data.
> Could easily double the amount of time it takes to perform the
> update. And if you implement column locks there are even more. And
> implementing fine-grain locks leads to lock-contention: if someone
> locks a record and you try to lock a field in that record, what should
> happen ? Now before trying to modify data and having locks interfere,
> you're trying to modify locks and having lock-interaction interfere.
I definitely don't agree here as we're talking about these additional
locks existing _only_ in memory, not on disk. There'd be no reason to
implement on-disk locking or even notifying the OS of sections of the
file
which are locked since we're only talking about multiple threads in the
same process. Any other process would hit the OS file lock and be
forced
to wait. The overhead of in-memory locking going to be extremely
minimal,
and only affect those who specifically enable this fine-grained locking.
That said, I do think the on-disk journal file format might need to
change to accomplish even this, and I think that is probably the
biggest show stopper.
I'm not suggesting that this would be easy to implement either, and yes,
you'd need to figure out if SQLite will block on a lock, or return BUSY,
but by limiting the implementation scope to multithreaded applications,
it at least makes the implementation feasible, and would provide great
benefit
to many users of SQLite. In our own synthetic benchmark of our
application,
which is extremely write-heavy, we see roughly 15 txns/sec with SQLite,
but
1000 txns/sec with multiple connections to MySQL. Biggest difference
here
is MySQL allows multiple writers. (That said, I need to actually try to
benchmark MySQL with only 1 connection to be able to normalize those
numbers a bit). Typically though, those with large transaction volumes
are going to go to some other database besides SQLite for other features
of a server-based engine, like replication.
Not complaining here though, SQLite definitely fits the bill for the
default database of our application. What it does, it does well!
-Brad
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
