.Net 4.0 Memory-Mapped Files verses RDMS Storage

Question

I'm interested in people's thoughts comparing storing data in a traditional SQL based Database or utilising a Memory-Mapped File such as the one in the new .Net 4.0 runtime. The data in question would be arrays of simple structures.

Obvious pros and cons:

SQL Database Pros

1. Adhoc query support
2. SQL Management Tools
3. Schema changes (adding more columns and setting default values)

Memory-Mapped Pros

1. Lighter overhead? (this is an assumption on my part)
2. Shareable between process threads
3. Any others?

Is it worth it for performance gains?

Answer 1

SQLServer can use memory mapped files if you choose "SharedMemory" as the protocol. Otherwise it'll use Pipes, TCP or VIA.

Regarding pros and cons.. to me they are amost not comparable. SQL has the whole query/multiuser/transaction etc infrastructure built in. If you store with MMF's you are on your own regarding all that. On the other hand, MMF are built in the OS.. no seed for a server/service.

Answer 2

You could try out MongoDB, and get a mixture of both worlds (database-like features over a memory mapped store).

MongoDB bridges the gap between key-value stores (which are fast and highly scalable) and traditional RDBMS systems (which provide rich queries and deep functionality).

Here's a good article that can walk you through installing and coding to MongoDB:

Going NoSQL with MongoDB