I sometimes sound like a broken record when I talk about speed issues at a customers site.  “It is usually not the hardware, but rather the code running on it.”

 In database systems we work with a lot of data in tables.  Most of the time we are looking for something in particular.  When a system with large amount of data is running slow, people have the tendency to think they just piled too much data in it and their server is not handling it.  It can be true in some cases, but most of the time a system can be optimized to perform better.  Sometimes much better!

 

A simple example illustrates this.  Let’s say we get a phone book from our phone company, but it is not sorted in any order.  Let’s say you happen to live in New York.  You then need to find the phone number for a particular person.  Your only method for this would be what we call “Brute Force”, where you basically go through each and every entry and see if there is a match.  Anyone who has looked for a phone number in NY’s phonebook, even when it’s sorted, will tell you that it takes time.  Unsorted, would be a complete nightmare. 

 

So let’s say there are 1 million entries in the phone book and each entry had the same chance of being the correct one, then on average you would search 500,000 entries before finding the right one.

 

If however it was sorted and you used a binary search method of finding your entry, you would then split the book in half and ask, is the name in the former or latter half.  Then take that half and split again asking the same question, until in the end you would be left with 2 entries and one of them is the right one.  The number of steps required to do that is log2(1,000,000), which is about 20).  So, if a programmer is looking for something that is not sorted, then there is going to be a severe performance drop.  Instead of 20 steps, the computer might be going through half a million entries.

 

This is just one way performance can be enhanced by looking for pitfalls in the code.  Don’t be too quick to blame that aging server.