We've all heard of salesforce and sugarcrm and the likes of systems like this. Unfortunately at my workplace we have been asked to write a similiar system (rather then license or purchase). Basically the database is fairly large. Think of modules such as: Corporate groups, customers, programs, projects, sub projects, and issue management. In simple terms a corporate group has one to many customers. A program has one or more projects. A project has one or more sub projects. And an issue can be created on many sub projects.

Of course the system is a bit more complex but instead of listing every single module I think its best to keep it simple. In any event, the system in its current state has only two resources to be working on it (basically we have to do it all: CSS, database, jquery, asp.net and C#). We've started off well by defining the UI master and footer pages that way we can reuse those across all of our pages.

Now comes the hard part. The system will have about 4k end users with say 5-10% being concurrent users. We are wondering if it makes sense to cache our database data (For say 5-10 minutes) rather then continously hit our database. The reason being is some of these pages may have 5-10 search filters associated with the page. Imagine every time a selection is made from a search box how many database hits. Also some of these search fields cascade so selecting for instance an initial drop down may cascade several drop down boxes under them.

Is it wrong to cache because I am not finding too many articles on whether it is a good idea or not. Remember the system is similiar to say a CRM system where we manage our various customers, projects, sub projects, issues, etc.

  • 1
    I work with Jon and we both are to come up with a design and implement this webbased application. I appreaciate all the input that has been said and discussed and find it very helpfull. Just wanted to point out one issue why the cache implementation was brought into the picture for this app. our web app is going to consist of many main manager pages; each manager page will pull as many records as posiible based on the logged in user profile setting and publish it to the requesting page. after this data is loaded; the user will have many filters on that page to filter the results untill they fi – user108434 Nov 11 '13 at 13:53

Caching of some types can make sense, but 500 concurrent users is generally vastly below the point where you'd need to even consider it. 1-2 enterprise quality servers can handle thousands of concurrent users, even with pretty heavy usage, if sanely implemented.

It's good to perform capacity planning (here's a Link Server example), but database caching is likely to be optimization step 5 (at the earliest), with application-level caching being something more like step 10 - taken if you are still unable to keep up with demand, which given <1000 concurrent numbers should never happen. Unless you are handed some really, really under-powered server equipment, it shouldn't be anything you need to worry about.

Further, there are many kinds of caching, and with all the wonderful tools available manually caching data in the application - especially on a data-driven application like CRM - is probably the least helpful kind. Go with the ones you can just "turn on" and tweak first, when you find a need.

When and if a performance problem appears, profile to determine the source of the troubles, and adjust in the way that least impacts overall development.

As to the basic issue of caching, when its needed it can be the bees knees. In the simplest way, most modern database systems have easily implemented (or automatic) query caching, which can be a great way to lighten server load - especially if you have some fancy, complex queries and hundreds of thousands of records (or more).

More aggressive caching can be...challenging and time consuming, if you aren't already a DBWizard.

  • 2
    "Enterprise quality" always makes me laugh :-) – Florian Margaine Nov 11 '13 at 14:05
  • @FlorianMargaine "Borg quality" would also be acceptable in a pinch, of course. You just have to make more adjustments. – BrianH Nov 12 '13 at 3:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.