Multi-threaded apartment saves the day

You know the obligatory [STAThread] attribute that appears before every Main() method you've seen in your C# life? No, it doesn't stand for "start thread." It stands for single-threaded apartment. Recently, this caused a major problem for me.

We have several programs that read enormous amounts of information from a SQL Server database, transform the data, and export it to Excel. These programs were horribly, horribly slow. Not horribly slow in an "I'm doing a ton of work" way - enormously slow in an exponentially slower, scary kind of way. In one case, a 10% growth in database size caused the export time to rise from 1 hour to 3.5 hours.

I immediately began to suspect our code, and watching Task Manager revealed that the memory footprint of our application grew over time, and so did its CPU utilization (by a more dramatic margin). I went off in search of a memory leak. Since the program was dealing with too much data to hold in memory at one time, it was reading data in small chunks, doing its work, and throwing the chunk out (or so we hoped) before moving onto the next chunk. Similarly, the StreamWriter we were using did not hold the entire output file in memory before writing it. It flushed itself after every chunk and then appended future chunks to the same file.

The code that was reading the data was well-tested, often-used, trusted code that we've never had problems with before. I began to suspect the StreamWriter was to blame. Maybe there was some underlying buffer that wasn't getting flushed. I tried destroying and recreating a new StreamWriter for every chunk instead of having one for the entire program, hoping to achieve more linear performance (if slower). This did not work.

I began to suspect some underlying problem with our data layer - more specifically, a DBConnection object that wraps a SqlConnection object. At Red Stapler, our data layer is our most robust, most used, and more important core asset. To have a memory leak in it would be embarrassing. Still, I checked it out. I couldn't find anything.

After a bit of googling, I discovered people with a similar problem. It turns out that SqlConnection objects leak memory (more specifically, they leak handles) in certain circumstances. One of these circumstances is using them in a single-threaded apartment. The message board discussions I found offered several solutions to the problem, one of which was changing from a single-threaded apartment to a multi-threaded apartment.

Fortunately, this was as easy as changing [STAThread] to [MTAThread]. Magically, changing one character in one file, with no other changes whatsoever, fixed the whole problem and suddenly the operation that took 3.5 hours to perform now took 20 minutes. The memory footprint was very stable and the CPU utilization did not increase throughout the program.

I was relieved to find there was nothing wrong with our logic, and even more relieved to find there was nothing wrong with our libraries. The only problem was the lack of understanding regarding STAThread and MTAThread (the latter of which I didn't know existed until solving this problem). Documentation in Visual Studio was poor, only mentioning that these attributes only matter when using COM. This reveals two things: SqlConnection must use COM under the hood, and the only thing I need to know about this is that MTAThread magically solves this horrible problem.

Later, I did do some more research on multi-threaded versus single-threaded apartments, and from what I gather, single-threaded apartments have some sort of built-in thread serialization
mechanism that protects the developer from his or herself. In the future, I will probably use MTAThread unconditionally - certainly when using SqlConnections.