We are getting ready to start using in-
memory database to cache sqlite reads/writes
in effort to improve speed. For background,
the application is an email client.
The way we envision the caching working is
that we have a mirror copy of any table in
use in memory. The tables are small and not
many are in use at any time, so we are ok with
space. Since we read much more than we
write, reading from memory should improve.
Also, this should simplify our transaction
model for transacted objects.
> We are getting ready to start using in-
> memory database to cache sqlite reads/writes
> in effort to improve speed. For background,
> the application is an email client.
> The way we envision the caching working is
> that we have a mirror copy of any table in
> use in memory. The tables are small and not
> many are in use at any time, so we are ok with
> space. Since we read much more than we
> write, reading from memory should improve.
> Also, this should simplify our transaction
> model for transacted objects.
> Has anyone done this? Comments?
It sounds like overkill to me too.
In memory databases are unique per connection. If you have
multiple processes they won't be able to share the same database.
You could share the same database amoung threads or a
database server process.
Using the 90/10 rule this last 10% of performance is going to cost you
90% of the effort.
> On Tue, Feb 14, 2006 at 10:16:14AM -0500, Kervin L. Pierre wrote:
> > We are getting ready to start using in- memory database to cache
> > sqlite reads/writes in effort to improve speed. For background,
> > Since we read much more than we write, reading from memory should
> > improve.
> Shouldn't SQLite's built-in cache for disk-backed databases already
> accomplished that for reads? Have you tested the actual performance?
I'm reading and replying to this message using an SQLite-backed
email client See
[hidden email] wrote:
> I'm reading and replying to this message using an SQLite-backed
> email client See
That's very interesting...
> All incoming and archival emails are stored as BLOBs in a table.
> The full text of messages is indexed. It is all very fast and
> I haven't had to do anything special to make it so.
How did you implement full text indexing?
I though sqlite did not support full text
index, am I wrong?
> Of course, all this assumes a modern workstation. Perhaps your MUA
> is designed to work on a handheld with cheapest (read: slowest)
> flash memory available and a 50 MHz ARM processor or something?
Lol, I wish. It's the direct opposite. The
project is essentially an alternative to PST
in Microsoft Outlook :)
We are stuck with Outlook's internal API.
Our design has an SQLite table for each
message. Message objects are transacted.
Hence the plan is to mirror the message
SQLite table in an in memory database until
the SaveChanges() API is called, which would
copy the changed rows to disk.
Currently message saves and retieval is
very slow because Outlook makes about
250 calls to our 'sqlite-backed' generic
property retrival function when it opens a
single message. So that SQLite-based
function, GetProps(), needs to be very,
very, fast. Any ideas?
"Kervin L. Pierre" <[hidden email]> wrote:
> How did you implement full text indexing?
> I though sqlite did not support full text
> index, am I wrong?
SQLite does not have *automatic* full-text indexing.
But it is easy enough to index the full text of an
email message yourself in application code. I just
counted 32 lines of code in my email reader used to
implement full-text indexing. It is not hard.
> Our design has an SQLite table for each
Is this right? You are doing a separate
CREATE TABLE for each message? That's going
to be the source of your problem. I think
you have much better success if you put
each message in its own row of a single
[hidden email] wrote:
> Is this right? You are doing a separate
> CREATE TABLE for each message? That's going
> to be the source of your problem. I think
Yes we are. I tried a message/row design
early on but I could not count on it being
behaving well. The problem was fitting
Outlook's internal API ( MAPI ) on a SQL
database. Every message has a one-to-many
relationship with properties, which have
a one-to-many with values. Combined with
some of the reporting requirements of the
API, I thought that a simple message/table
would work, at least for version 1.
I was hoping that the CREATE hit would not
be significant since it is only occured
when a message is created.
But there doesn't seem to be much we can do
about the reads.
PS. One approach seems to be to replace the
default 'sqlite3OsMalloc' with a pool based
malloc for speed. It would be nice if this
was a function pointer instead of a define.
that way we could replace the function with-
out having to modify SQLite source.