How I tried to cheat the performance monster and lost........

I like things simple.  Simple is good.  When things are simple, bringing someone new into a project is easy.  There are fewer special cases to worry about.  Looking for a problem is not like looking for a needle in a haystack.

A couple of years ago, I had a customer that was going to be doing several database lookups per second.  They wanted to cache the data so that there would be no reason to go out and read from the database on each requested lookup, thereby improving performance.  The data was not going to be updated except once every couple of days, so this made some sense.  Well, I read through some MS KB articles and found a couple that stated that under certain circumstances, you could cache recordset objects in an ASP Application variable.  This app met those conditions.  I remember thinking, “This is a lot of data, so I wonder if this will really work.”  As you can imagine, it fell flat on its face.  I called MS dev support on the issue and they basically said that the object in the Application variable was the problem.  I told the client this and the customer was adament that there would be an enormous performance problem.  I played a little game and said that I had some other tricks up my sleeve that we could try at the same time.  Well, I made the lookup go against the database directly and not only was the stability problem solved, but the performance problem was to.  Why?  Well Sql Server is not that dumb.  It has good tools to setup the indexing of tables and it is smart enough to cache data in memory when needed.  Sql Server tools are your friends.  Index Tuning Wizard and the Sql Profiler are smarter than 90% of developers out there.  I tried to cheat the performance monster and I lost.  In doing so, I re-learned a valuable lesson:  Doing things in a straightforward, simple, and smart manner will solve a lot of problems that “creative coding” can cause.

Wally