The Performance was Not Anywhere Near the Numbers I had Sold Management On

"The performance was not anywhere near the numbers I had sold management on."

David Takahashi, Agilist at Group 1 Software (part of Pitney Bowes), recounts his experiences integrating a relational database management system engine into his .NET application. He explains the unexpected performance problems he encountered and how he managed to solve them.

Introduction

In August of 2007, I was asked to analyze and select a third-party database for a .NET project that was well under way. I used a test harness to compare and contrast offerings from many vendors. I was able to justify the selection of a certain RDBMS (relational database management system) engine and got signoff. At that point, the actual integration of the database into the application began in earnest. The integration went well and there was joy in the land, until I realized that the performance was not anywhere near the numbers I had sold management on.

So, being the analytic type of person that I am, I began a search for a code profiler. I took a look at what was available and listed my requirements. My list evolved as follows:

1. I have many tools spanning many languages. The candidate-profiler must be easy to use, with a low learning curve.
2. The tool needs to probe rapidly: waiting hours for results is unacceptable.
3. The tool needs to work seamlessly with the existing code: building profiling targets and running post batch processes is a last resort.
4. The tool needs to be cost effective.
5. The tool needs to "probe" framework 1.1 and framework 2.0 (at the time) applications.
6. The tool needs to be effective in determining the current bottleneck.
7. The tool needs to be effective in determining and illuminating areas ripe for performance improvements. This means the tool needs the ability to "filter" the measurements so that the tree can be singled out from the forest.

My candidate for source code profiling was ANTS Profiler from Red Gate Software. The reviews I had read suggested it was built for ease of use. I took a look at the Red Gate website and was convinced that this was a company that cared about its customers: it was chock full of reviews, user testimonials, downloads, and general information that was easy to get to.

I went ahead and downloaded the free trial and submitted ANTS Profiler to its first real test: would it painlessly install? It is amazing how many products actually fail this first test. If it won’t install, can you expect software "goodness" down the road? It installed flawlessly! It did not require a reboot! I was also pleasantly surprised to hear from Red Gate within a few days, asking how my evaluation was going, with a real person to reply to, no less.

I had my successful install, and then pointed my shiny new profiler at my code: this was the second real test. In five minutes, I had the answer: the code was spending most of its time inadvertently initializing database connections (3rd and 4th slowest methods in the screenshot below).

Armed with this knowledge, it became obvious I needed to enable connection pooling. Voilà! Five minutes!! The first report was around 10:30 a.m. and was a run on 100 records; by 2:00 p.m., I was processing 1,000 records.

The second screenshot was taken after I enabled connection pooling and shows that SQLDisconnect and SQLDriverConnectW are gone.

Then I began looking at the next biggest CPU hogs and knocked off all that I could by selectively filtering what was being profiled. In the end, I was left with system or framework code, and I was able to identify areas I could call less or where I could go for more aggressive performance-enhancing strategies.

So, this is a tool that, out of the box, provides an easy route to results, and allows you to extend and refine the results from there.

In the interim, as part of my release process, I routinely ran the same data through a saved ANTS Profiler project, in order to ensure that performance had not degraded, and to offer proof in cases where performance had increased. I saved the results for each release and now I have a record stretching back over two years of releases.

ANTS Profiler is a tool that stays out of your way, yet provides the kind of meaningful information you need to fine-tune the performance of your application, and ensure that the performance remains at the desired levels. It returns results rapidly, and has naturally worked itself into the software lifecycle of my application.