Hybrid in Memory database SQLite

Question

I have an app that use collections of objects an is really fast. Now, I am adding a database for persitancy, so I started to save things in SQLite database.

But now i found is much much slowly. I think it is because the disk is slower than ram. it possible to have the DB in memory? I found an Inmemory DB in the documentation of SQLite, but it is in memory and I need persitancy.

So, is it possible to have the DB in memory for perfoamnce and save to the disk after some time?

Thank you and regards.

Update:

In the answers they say that it is because I am doing lots of inserts, and this is true. They say I should make a bulk insert.

My code looks like this in a memory collection called Computers:

foreach (line in lines)
{
Computers.Add(new Computer { ComputerName = line});
}

Now in the DB:

    foreach (line in lines)
    {
string sql = "INSERT into computers VALUES ('"+computerName+"')";
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);                            command.ExecuteNonQuery();
    }

How can I do this in a single bulk insert?

Unless you are doing a LOT of calculations every second, I believe your problem is likely more about how you use SQLite more than the fact that you are using it. — Jeremy Holovacs, Jan 28 '13 at 21:16
Regarding your edit, just BEGIN a TRANSACTION before doing the inserts, and COMMIT the TRANSACTION when you are done inserting. — Robert Harvey, Jan 28 '13 at 22:09

score 3 · Accepted Answer · edited May 23 '17 at 12:12

3

You can use the Backup API to accomplish this. See http://www.sqlite.org/backup.html A complete code sample in C is provided.

If you are doing lots of inserts at once, make sure you combine them in a single transaction. Otherwise SQLite waits one disk rotation for each insert, which can greatly slow down the insertion process. Doing this may allow you to use a disk-based SQLite database, without the slowdowns.

See Also
SQLite Insert very slow?

edited May 23 '17 at 12:12

Community

1
1

answered Jan 28 '13 at 21:16

Robert Harvey

178,213
47
333
501

Thank you @Robert. I think this is my problem and I should combine the INSERTS. I updated my question. – Ricardo Polo Jaramillo Jan 28 '13 at 21:34
Why not mark this as the answer? The official sqlite FAQ confirms this too: http://www.sqlite.org/faq.html#q19 – Dan Ling Jan 28 '13 at 22:00

score 0 · Answer 2 · answered Jan 28 '13 at 21:17

If you have a database in memory and only occasionally flush it to disk, you lose one of the most important features of a database, which is ACID. In other words, you app can write data thinking it is persisted, then a subsequent problem can cause you to lose the data after all.

Many databases will perform quite well (though still much slower than in-RAM solutions) if you have enough RAM available for the database to cache frequently accessed items.

Start by examining what exactly is slow in your interactions with the database. Are you fetching data that doesn't change over and over? Perhaps cache that in the database? Are you making many separate updates to the database that could be combined into a single update?

Hybrid in Memory database SQLite

2 Answers2