Strategies for Performance

Like many growing technology companies, we frequently wrestle with performance and scalability of our offerings. In our case, we're tied pretty tightly to Microsoft; a hold over from a time when the products were built using Access. We've since moved on to Visual Basic, c#, .Net, and SQL Server, but we are not platform agnostic.

We're also taking steps to move off a pure client-server architecture, and to an n-tier architecture delivered through a browser. Typically our applications have a small number of users who submit long running queries. There are two primary stress points for performance, loading the data repository, and running queries (reports).

We are attacking the problem across several fronts. First, we're throwing hardware at it. Second, we are upgrading the OS and database platform. And finally, we are optimizing the applications. Note that we are not considering using a server farm for the application servers. We believe the low number of hits to the web apps make scaling the application server a lower priority.

Throwing hardware at the problem is the easiest and quickest way to scale. In our case, that means moving the application server to a separate box, and purchasing more power. More memory, more speed, and more processors. We all know, however, that this type of solution simply covers up bottlenecks in the application.

We are also stepping up to SQL Server 2005. In the standard edition, which most of our customers deploy, SQL Server 2005 will use 4 CPUs and as much memory as the OS can give it. Some of our customers are CPU and memory bound when using SQL Server 2000. Stepping up to 2005 is a significant boost. SQL Server 2005 also runs on Windows 2003 64 bit.

The 64 bit OS appears, in our sample testing, to give a huge performance boost. Unfortunately, it also gives us problems with some of our applications. Most significantly, we have not successfully deployed .Net 1.1 on the OS. Therefore, all our web applications must be migrated to Visual Studio 2005. We found that moving our web applications from VS 2003 to VS 2005 required some work. We are still trying to work through problems with deployment projects for these applications. Our Visual Basic legacy clients flat out do not run in the Terminal Services environment.

Finally, we are confronting the code in the applications themselves. The products have evolved from a client-server architecture. The software requires an active user that performs key functions synchronously. We will move file operations and reporting to asynchronous classes. This frees up the UI and gives the user a responsive experience. But asynchronous does not make queries run faster. Improving query performance requires reviews of the execution plan, indexes, and index views.

It is tedious work, but it will payoff in greater revenue.