Not for my application, but for a particular data element within my app. I'm implementing a custom DAL. Everything can be known by querying the database, but there will be many such queries.

The client has a system with 4 SQL Server databases taking up about 1,200 GB on disc. So, clearly, I can't cache the whole thing. But if a query comes in to know about item #42, and then another query comes in for item #42, there's no reason that should be 2 round trips to the DB. I know how to choose which items to hold in cache, but a different question is how many of them?

Each element takes X bytes of storage, except we have some strings (variable length) so I can only say what X is on average. And I sure can't walk the array and sum the length of each property on each item! On the other hand, I could watch Environment.WorkingSet like a hawk, and start dumping when it goes over a threshold. I'm not doing much else, but there are some things like DB connections, that will also gobble up some RAM and contribute to the working set.

How do other people approach this?

Set the threshold at the Application domain level, then just let the framework handle the rest.

Several ways to go about this.. through code and through sys admin type functions (App pools and such).. depending on your deployment situation.

In code though if you specifiy Application domain settings, it may give you greater flexibility to scale or extend in the future.

I'm not 100 % clear on how this works? How does it decide which items to unload to disc when the app comes under memory pressure?

What I'm trying to do is cache items in a list. If I don't have the item in cache, then I send a query for it to the database. Nothing lost, it just takes longer this way, and adds pressure to the database. If I do have the item in the cache, I return it from there instead of going to the database. The items are static, so I don't have to worry about falling out of sync. But on the other hand, I can only use so much memory - I can't fit everything in RAM, so I have to decide what to store, to better my chances of being useful. I have the prioritization down, but I need to figure out how to measure RAM usage so I know if I'm holding too much or too little data.

I don't know of a way to make the AppDomain's max working set honor my prioritization?

Well first off, Im not sure about the total situation.. but a wise old man once told me.. Dont optimize what doesnt need optimization.. so you may assume that lots of requests to the database takes a lot of overhead, but then again so does storing things in cache, and serialization and deserialization and hashtable lookup..

Generally, ASP.NET Cache objects are great, but Ive found that you must use them sparingly.. what seems like a great idea at first, may not end up buying you anything in terms of performance. My Cache strategy is usually to only cast LISTS of items.. not specific details records.. that way I never have to deal with optimistic caching issues, or listener events.. also because most of the time im using Oracle, which doesnt support those neat things in .NET (..yet)

So large lists of data.. like say over 500 or more rows.. I try to cache.. individual records, like RECID #42 or whatever.. I rarellllly cache.. unless its part of some funky architecture that forces me to be disconnected or limit the time I spend on a database for monetary reasons (thats happened).

That being said.. use the cache helper idea I posted on another thread..

if( HttpContext.Current.Cahce["SOME LABEL"] != null)
{

}

Well first off, Im not sure about the total situation.. but a wise old man once told me.. Dont optimize what doesnt need optimization.. so you may assume that lots of requests to the database takes a lot of overhead, but then again so does storing things in cache, and serialization and deserialization and hashtable lookup..

Generally, ASP.NET Cache objects are great, but Ive found that you must use them sparingly.. what seems like a great idea at first, may not end up buying you anything in terms of performance. My Cache strategy is usually to only cast LISTS of items.. not specific details records.. that way I never have to deal with optimistic caching issues, or listener events.. also because most of the time im using Oracle, which doesnt support those neat things in .NET (..yet)

So large lists of data.. like say over 500 or more rows.. I try to cache.. individual records, like RECID #42 or whatever.. I rarellllly cache.. unless its part of some funky architecture that forces me to be disconnected or limit the time I spend on a database for monetary reasons (thats happened).

That being said.. use the cache helper idea I posted on another thread..

if( HttpContext.Current.Cahce["SOME LABEL"] != null)
{
SomeObject obj = HttpContext.Current.Cache["SOME LABEL"] as SomeObject;
}
else
{
'Goto to the database to get the object instead..
}


You can use ADO.NET dataset strategies to consolidate the items you are caching, (like using mutilple DataTables and Relation objects within a single DataSet) and just cache the DataSet objects to use later. (if thats what you are using)

As far as memory goes.. Its interesting to note that ASP.NET uses 2GB memory limit for its Worker Process, including Cache... even if you dont have That much memory available.. so its worthwhile to change the Total Maximum RAM that the worker process can use.. But this is done several ways, I mean Im assuming that this is a Web Application running on IIS? If so.. which Version of IIS.. and what version of the Frameworkdo you have available.. there are several ways to configure such a thing.. and its usually wise to go in and set the Maximum memory cap to something UNDER the physical memory on your server.. (to avoid paging of course)

Originally Posted by RabidSniper

Well first off, Im not sure about the total situation.. but a wise old man once told me.. Dont optimize what doesnt need optimization.. so you may assume that lots of requests to the database takes a lot of overhead, but then again so does storing things in cache, and serialization and deserialization and hashtable lookup..

Interesting - this isn't at all the answer I expected!

To clarify, this isn't the typical shared host situation I'm asking about. The database and the web server and the .NET caching code won't be on the same machines. I agree with you there's a cost to all the .NET logic of storing things in lists, hash mapping, and the like. But that cost is only the price of the dedicated hardware that will use this.

On the other hand, the database this will take pressure off, is doing all kinds of other work as well. The DB has many hundred connections at any given time. It also has 24 pros (6 quads), either 16 or 32 GB of RAM, and I don't even want to guess at how many drives. But there's extreme resource contention in a particular area.

A lot of what hits the database could be rewritten. Virtually all of that stuff is written by people who still work here. The business feels the opportunity cost of having them stop what they're doing to rewrite services (which supply the UI app) is far more than a new caching service (which we'll ultimately reuse) and a dedicated server for it. We could do log shipping and federate the database, but this seems better in the end.

Hrmm..

Well perhaps the virtues of the new Sync framework could help. If you were to say, run sync processes against the prod database, to say local SQL express instances, then maybe you could avoid caching the whole database. Just a thought, I really do not know much about it but I saw it in action at TechEd..