Common Language Runtime Executables: Another Look

Joe Celko is against their use, based on his editorial comments (www.sswug.org/see/22557), and he’s one of the most respected in the industry. Others whose opinion I respect are more ambivalent. They feel there are places that cry for the functionality and performance promised by sophisticated compiled code and other situations where it makes no sense at all.

Evaluating CLR Executables

At this point I needed to decide for myself whether CLR executables make sense. Since I’ve been working with SQL Server 2005 since the earliest versions of Yukon, I was fortunate to get some advanced bits long before the general public and had already formed a few opinions. All during the alpha/beta cycle, we fought many battles in an attempt to make the CLR executable development process easy and the integration into T-SQL seamless. Unfortunately, most of the CLR routines I wrote during the betas were slow in comparison to the T-SQL equivalent code—sometimes pitifully slow. Sure, I wrote useful functions that could not even be attempted in T-SQL, but for the mainstream stored procedure or function, the CLR code was like bowling with oblong river rocks.

No, it's not fair to judge any product under development, especially for performance. Microsoft kept reminding everyone of this for years. Since Yukon (SQL Server 2005) is a radical departure from the "old" SQL Server 2000, there were bound to be areas that needed to be tuned up and redesigned during the final development process. But now that it's shipped, we can take the gloves off. To this end I wrote a whole new series of tests and examples. I rewrote my temperature converter and currency UDT (user-defined type) and created several other “typical” examples that are often touted as functionality that should be implemented using CLR executables. These include a GPS function that computes the distance between two points using latitude and longitude, a GPS UDT to store a map coordinate in a single column, and a series of “text munging” examples. While I’m not quite done with the chapter, the performance results have been crystallizing. And they’re a bit surprising.

A Few Surprises, A Few Glitches

The temperature conversion function that changes Fahrenheit to Celsius is a bit faster than the same routine written against Beta 2, but it’s about as fast as my Model A on the tollway between Lawrence and Emporia. An operation as simple as temperature conversion is not a good choice for a CLR function. In contrast, the new currency UDT generated a dramatically smaller data footprint and seemed to run at a respectable speed.

The most surprising were the GPS executables. These use several trig functions to calculate the distance between two lat/long points. Most folks would believe T-SQL could hold up under the CPU strain. While T-SQL did okay, the CLR routine was just as fast—within 0.005%. This means if you have even a moderately complex math formula, it makes sense to code it in a CLR language. The text crunching examples also ran as fast or faster than the T-SQL equivalent but were far easier to write.

The other benefit to writing your SQL Server executables with a CLR language is developer performance. The stored procedure and function preambles are simple to understand, and the code body is even simpler to write. User-defined types are a bit daunting at first, but you can build them fairly quickly. Intellisense and Visual Studio help. This means your routines can be coded with every construct the .NET Framework languages expose, and any CLR language is far richer than T-SQL. You get sophisticated branch logic, real exception handling, far better string handling, real collection and array constructs, and more. The only stumbling block might be Visual Studio 2005 CLR executable debugging. Visual Studio 2005 is far better than coding, compiling, and deploying CLR executables by hand as they illustrate in the SQL Server documentation. But Visual Studio 2005 can be frustrating when the IDE or “Debug Manager” repeatedly fall over.

Microsoft didn’t figure out how to build, deploy, and test a UDT without jumping through hoops. Despite these issues, the step-through-debugging process is better than ever and seamlessly step-debugs into T-SQL or other CLR executables. Microsoft knows of these issues and I expect them to be fixed sometime this summer.