0

I'm new to ASP.Net, MVC and the Entity Framework.

I'd like to understand the best practice for small databases. For example, say at Contoso University we know there are only going to be a few hundred or a few thousand students and courses. So all the data would comfortably fit in memory. So then is it better to use an in-memory collection and avoid potentially high-latency database operations?

I am thinking of small-scale production web sites deployed to Windows Azure.

To be more specific, the particular scenario I am thinking of has a few thousand records that are read-only, although users can create their own items too. Think of a collection of movies, albums or song lyrics that has been assembled offline from a list of a few thousand popular titles. The user can browse the collection (read-only), and most of the time they find what they are looking for there. However the user can also add their own records.

Since the popular titles fit in memory, and these are read-only, is it maybe better not to use a database for the popular titles? How would you organize data and code for this scenario?

Thanks for any thoughts and pointers.

Toby Sharp
  • 2,029
  • 3
  • 19
  • 21

2 Answers2

2

I think a database is good place to store your information.

However, you are concerned about database latency.

You can mitigate that with caching - the data is stored in memory.

In short, it isn't an either or scenario...

Joe Ratzer
  • 18,176
  • 3
  • 37
  • 51
  • Thanks. Do you have any pointers to docs about how to enable caching? E.g. for Sql Azure? – Toby Sharp Nov 12 '13 at 16:44
  • 1
    Hello Toby, this link - http://www.windowsazure.com/en-us/develop/net/best-practices/performance - mentions the Windows Azure Caching Service. You can also ensure the .NET code caches your data. For example, EF has 1st level caching and there are third-party frameworks such as NCache for 2nd level caching which would result in a lot more data in cache. – Joe Ratzer Nov 12 '13 at 16:50
  • 2
    It's worth mentioning that the performance of accessing data from a database should not be a problem for simple requirements. And remember the phrase about premature optimization! – Joe Ratzer Nov 12 '13 at 16:52
  • 1
    @JoeR - 100% agree with last statement (as shown below). Too often, it seems people want to gain 10ms on performance when that time could be spent rocking out a new feature or fixing a bug :) – Tommy Nov 13 '13 at 14:15
  • @toby you will find worth answers in this link [http://stackoverflow.com/questions/17653124/how-to-cache-database-tables-to-prevent-many-database-queries-in-asp-net-c-sharp] – QMaster Dec 26 '13 at 14:35
1

You should definitely store your data in some persistent storage medium (SQL, Azure Tables, XML file, etc). The issues with storing items in memory are:

  1. You have to find a way to store them once for the application and not once per user. Else, you will have potentially several copies of a 2-5 MB dataset floating around your memory space.

  2. Users can add records, are these for everyone to see or just them. How would you handle user specific data.

  3. If your app pool recycles, server gets moved by the Azure engineers, etc, you have to repopulate that data.

  4. As said above, caching can really help to alleviate any SQL Azure latency (which btw, is not that high, we use SQL Azure and web roles and have not had any issues).

  5. Complex queries. Sure, you can use LINQ to process in memory lists, but SQL is literally built to perform relational queries in a fast, efficient, data-safe manner.

  6. Thread safe operations on an in-memory collection could be troublesome.

Edit/Addendum

The key, we have found, to working with SQL Azure is to not issue tons of little tiny queries, but rather, get the data you need in as few queries as possible. This is something all web applications should do, but it becomes much more apparent when using SQL Azure rather than a locally hosted database. Lastly, as far as performance/caching/etc, don't prematurely optimize! Get your application working, then identify bottlenecks. More often than not, it will be a code solution to fix the bottleneck and not necessarily a hardware/infrastructure issue.

Tommy
  • 39,592
  • 10
  • 90
  • 121
  • Thanks for these points. I would like the user to be able to choose whether their new record is publicly visible or not. I guess I should start with sql azure and see whether the latency is a problem first. – Toby Sharp Nov 12 '13 at 16:46
  • 1. Seems easy enough to load in a static constructor for example. – Toby Sharp Nov 13 '13 at 10:29
  • 1
    @TobySharp - sure, didn't say it couldn't be done. But you still have to accomplish that with all the other items that one would need to think about having an in-memory collection. I simply wanted to point out potential pitfalls that I could see by quickly looking at your question. Some may be easy to overcome, some not so much and I am sure there are other problems that I didn't list here. – Tommy Nov 13 '13 at 14:13