2

Similar questions:

Is there a free, high-performance, SQL queriable, .NET in memory RDBMS? I've done some research in the past 10 hours, but didn't find one.

I've just tried SQLite in memory mode, but it's performance is not good. The following test took 4130 ms, only 2x faster than SQL Server.

As Kibbee suggested, I used a profiler to investigate the performance, and found SQLite's in memory mode is actually pretty performant, the bottleneck was NHibernate. Here is a benchmark of SQLite 3.6.3 (posted on 20-Oct-2008).

Community
  • 1
  • 1
  • 2
    If you give SQL Server enough memory, it will almost be all in-memory.... – marc_s Jul 25 '11 at 04:55
  • Why do you want an always In-Memory RDBMS? Is it the need for simplicity (no installation, simple deployment, etc.) or something else? – Carlos G. Jul 25 '11 at 05:08
  • @Kiranu, I need to do a lot of complex logic to the entities, which requires the DB be able to run a few thousands of queries per 0.1 second. –  Jul 25 '11 at 05:17
  • @Kiranu, it's for a WPF desktop program. –  Jul 25 '11 at 05:25
  • @marc_s, I've just tried to increase `Minimum server memory` to 1GB, and `Minimum memory per query` to 10MB. But unfortunately didn't see performance difference in my tests (I used the same test code above, just changed SQLite to SQL Server). –  Jul 25 '11 at 05:40
  • 1
    @caveman: oh, I was talking about 64 GB of RAM or more ..... **enough** memory.... – marc_s Jul 25 '11 at 06:07
  • 1
    Did you use a profiler to see how much of that is actually attributable to the database? You are currently around 0.4 ms per iteration. That's not at all terrible performance. Not only that, but you're comparing SQL Server which is usually used to access data remotely vs. SQL Lite, which is used a local data store on your own machine. What are the requirements for your project? If you just need to store data locally on your machine, you might be better off just writing and reading to a flat file, instead of incurring all the overhead of a database. – Kibbee Jul 25 '11 at 20:28
  • @Kibbee, thanks for your advices. As you suggested, I used a profiler to investigate the performance, and found the bottleneck was NHibernate (see my updated question). All of my data is stored locally on my machine, but since I need transaction control and complex queries, I have to use a RDBMS. –  Jul 26 '11 at 03:22
  • 1
    Go figure, nhibernate was a bottleneck. – Stephanie Page Jul 26 '11 at 20:47
  • 1
    marc_s, caching database tables in memory, as your comment suggests is far from an in-memory database. CRUD against those tables still goes through a fair bit of effort in order to maintain the ACID properties. Traditional RDBMS's have not tuned those process to be as fully performant as in-memory databases. – Stephanie Page Jul 26 '11 at 20:50
  • @Stephanie, what does "go figure" in your comment mean? (sorry if it's an obvious question. I tried google but got very different answers). –  Jul 27 '11 at 02:04
  • 2
    Idiomatic expressions are not always easy to google. Here's a link: http://en.wiktionary.org/wiki/go_figure – Stephanie Page Jul 27 '11 at 15:39
  • I posted an answer saying SQLite was the answer to my question, but the post was removed by moderator. Well I found SQLite's in memory mode difficult to use, so hope someone can come up with a better choice. –  Aug 01 '11 at 20:12

2 Answers2

2

Your problem seems to be not one of architecture but one of capacity. Altough RDBMS are highly optimized applications, they still require significant resources and most RDBMS that see heavy or complex usage will generally need to hit the disks. This brings us to cache, which is what many large applications use to increase response time by caching certain query results and serving those, instead of re-running the query every time. Software like Memcached (on linux) can be used to reduce the need to run queries.

Finally, if caching is not an option, you can simply provide more resources to the application. Like @marc_s said, significantly increasing memory (and cpu capacity, since by increasing memory you move the bottleneck to the CPU) is a good strategy to use.

Stephanie Page
  • 3,875
  • 1
  • 18
  • 22
Carlos G.
  • 4,564
  • 4
  • 34
  • 57
2

If you want performance, skip the SQL and RDBMS parts. A simple datastructure in ram with a change log will easily outperform a database. You might need some better collections than the standard provided ones though, as they don't scale well.

Stephan Eggermont
  • 15,847
  • 1
  • 38
  • 65