which is better... in-memory search or database access?

Question

We're working on an online system right now, and I'm confused about when to use in-memory search and when to use database search. Can someone please help me figure out the factors to be considered when it comes to searching records?

Answer 1

One factor is that if you need to go through the same results over and over, be sure to cache them in memory. This becomes an issue when you're using linq-to-sql or Entity Framework—ORMs that support deferred execution.

So if you have an IQueryable<SomeType> that you need to go through multiple times, make sure you materialize it with a ToList() before firing up multiple foreach loops.

Answer 2

It depends on the situation, though I generally prefer in memory search when possible.

However depends on the context, for example if records can get updated between one search and another , and you need the most updated record at the time of the search, obviously you need database search.

If the size of the recordset (data table) that you need to store in memory is huge, maybe is better another search directly on the database.

However keep present that if you can and if performance are important loading data into a datatable and searching, filtering with LINQ for example can increase performance of the search itself.

Another thing to keep in mind is performance of database server and performance of application server : if the database server if fast enough on the search query, maybe you don't need to caching in memory on the application and so you can avoid one step. Keep in mind that caching for in memory search move computational request from database to the application server...

An absolute response is not possible for your question, it is relative on your context ...