I think I'm gonna like this new database server

N2BJ is one of my worst case scenarios. Barry burns up the airwaves and has uploaded nearly 300,000 QSOs into his OutBox. When he checks his eAward status, it always takes a very, very long time. In fact, it usually times out his browser trying to check for things like ePFX300 and eZ40 standings. Until recently, he could barely check his InBox, and usually only late at night when the system wasn't heavily loaded.

So I use N2BJ whenever I'm testing a new performance improvement. He represents the worst case scenario, and I mean that in the nicest possible way! If I can make the system fast enough for people like N2BJ, K9QVB, and others with huge logs, it will work just fine for everyone else.

So, I was chomping at the bit to find out how much faster the new database machine is going to be than the old one. The old server had about 4GB of memory for the SQL Server engine to use. The new one has 24GB. Most database engines nowadays try to load as much of the actual data into memory as they can, so they can service queries directly from memory instead of having to do disk accesses. So obviously the more memory you can give a database engine, the faster things will be. I also have 2 CPUs with 4 cores each now, and I have 4 disks in a RAID 10 array that spin at 15,000 RPM on a SCSI bus instead of the slower, smaller disks on the old database server.

So, I ran a query this morning. It's a simple display of N2BJ's InBox. On the current server, it took 14 seconds to return 90 records. Then I ran it on the new server. Now mind you, I have not made any changes to the indexes or any other tweaks to enhance the performance. It only took 1 second to return the 90 records. That's 14 times faster speed. Then I ran the ePFX300 Standings query. It took 7 minutes 17 seconds on the current server. On the new server, it only took 38 seconds. Again about a 14-fold improvement in speed. And just by playing around with this query, I've found a few ways to speed it up even further.

I think I'm gonna like this new database server. 14 times faster already! And I haven't even implemented the recommendations that should give me another 40% improvement. I think it will be fast enough that I can remove the current time delays (cache) from the InBox and OutBox.