All of those new Sales Guys are starting to pour in now :) Did you notice, that was a smiley? I think we're going to be able to handle the load after all!
Our ongoing transition from ASP.OLD into ASP.NET is helping, along with lots of SQL optimizations. But the single biggest thing we are implementing is... Caching! Of course, like everyone will gladly tell you, caching is an immense performance enhancer - IF you can stand the data being "stale."
I think that what we've done is quite interesting, so of course I'll talk about it here :) Remember the Work Queues I discussed before? With caching I've been able to solve ALL the problems in a very elegant way. Here's the scoop:
1) We set up a WorkQueue Server. Yes, a server all it's own, with lots of RAM.
2) The WorkQueue Server hosts one ASP.NET WebService which knows the definitions of each Work Queue. You call into the WebService asking for a certain Queue, and passing in all sorts of parameters, which will cause the service to remove certain columns, sort the data a certain way, filter certain rows, return only the ID fields, etc.
3) The WebService holds a DataSet in CACHE for each Work Queue. When a request comes in, it takes the appropriate DataSet from cache, filters it accordingly, and sends it out. The DataSets in cache are "total" sets of data. In other words, enough rows and columns to support all the various places that need the data.
4) So then if we have 4 different places that need the same Queue, but sliced and diced differently, we call the same WebService, with different args, and that's it.
5) The WebService auto-refills the cache on expiration, etc. We have introduced a randomization factor so that not all Queues need refreshing at the same time :)
The benefits of this are enormous. We can now have one stored procedure per Queue. And that procedure gets run on average once each 10 minutes (our cache base time). From there, it's all done in RAM on the Queue Web Service. This gives us all the benefits of "dynamic SQL" without paying the price for that. Let's not argue about what that price might be. Suffice it to say we "don't like dynamic SQL." Our database interaction is with stored procedures, with NO PARAMETERS. The furthest that any badly formed dynamic sql can make it is into the definition of the DataView used to work with the DataSet in cache. Never to the database. That's beautiful.
| posted on Thursday, April 01, 2004 3:07 PM