Can SqlServer in-memory tables be replacement with ASP.NET cache?

Question

Our current system was architectured like;

We have around 5 million records in a DB table. Depending on the need, we get, say a resultset of 1 million records and keep them in cache throughout the application, when we are done, get rid of them.

Now, instead of using .NET application's memory, "Is it possible to use in-memory tables to keep those 1 million records in an in-memory table whilst disk based table still keeps 5 million records?"

Answer 1

That's possible. Performance will still be less than with in-process data. One of the most expensive things to do when executing a cheap SQL statement is all the overhead of executing anything at all (network, serialization, ...).

You will need to measure (or have a good understanding of) whether the now reduced performance is still enough.

If the existing system works without problems there is no need to change anything.