Load entire database into ado.net tables in memory

Anyone know how to do this? I have a friend who is working on a huge database and wants to do this so that they're not incurring lots of over head duing database transactions.

Anyone got some information on how to do this?

Or why it may or may not be a good idea?
(besides the obvious data failure problems)
LVL 3
jayrodAsked:
Who is Participating?
 
jj819430Commented:
Ok, this is a pretty serious design question.

If you load the whole thing into datasets you are going to need some serious hardware like graye said. (If this is a large database)

What might be better is to build your middle layers out into a framework that accesses the data as neccessary. A sql call should not be taking all that much time. If you commonly need to access the same information over and over again and they do not change frequently then just plop that into XML outside of the database and bind to that. You can even create some services to keep that up to date.

The solution to this would depend completely on the design and usage of the system.

IF YOU REALLY WANT TO BIND ALL OF IT INTO MEMORY
you can use the System.DAta.SqlClient namespace to give you what you need to load the tables. But it will be slow.
0
 
grayeCommented:
Uh, well... for one thing, if it's really a huge database, that would mean that the client PC would have to have enough RAM to hold the entire database... otherwise the client PC would start "paging"... and things would slow to a crawl.

You can persist the database (or parts of it) to files on the client PC...  but somehow I don't think that's what you're after
0
 
jayrodAuthor Commented:
well he does have a few large tables. And this is for a web application so the server is a beast. But I think i'll just tell him that he's better off doing it on a table by table basis. He does have one table that's got several hundred thousand rows that don't ever change. maybe this table would be great to put on an "island"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.