S h o r t S t o r i e s

// Tales from software development

Using SQLite as an ‘in memory’ database engine

leave a comment »

I recently needed a lightweight database engine to process data collected from logfiles. Ideally, I wanted a small database engine with minimal deployment requirements and that stored and processed data in memory.

After considering a number of possibilities I went with SQLite. No deployment configuration is required and creating a database is as simple as specifying a filename in the connection string.

I was pleasantly surprised with the capabilities and performance of SQLite but then I noticed that it implements an option to store temporary tables in memory rather than on disk. So, I wondered if it could provide the ‘in memory’ requirement too ?

After a connection is opened, my application executes the following Pragma statement:

PRAGMA temp_store = MEMORY;

Then all tables are created as temporary tables, e.g.:

CREATE TEMPORARY TABLE [log_data](…

I did a few quick tests to check the performance improvement. Previously, writing 150,000 rows of data took around 5.5 seconds. With the changes the same processing dropped to around 0.25 seconds.

Advertisements

Written by Sea Monkey

August 11, 2010 at 8:00 am

Posted in Development

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: