Grounded in Reality
Consider that calling a DLL from T-SQL is similar to calling an external (out-of-process) COM object. Established best practices still apply: Call external routines fewer times while using more parameters, and spend as much quality time as possible in the invoked code. By "quality time," I mean that the time your application spends in the external code should be a productive and efficient use of CPU cycles. The first time SQL Server calls an external CLR DLL, SQL Server must load the code into memory, compile it into runtime code, and execute it. After SQL Server takes these steps, calling the external object is less costly than calling it without this preparation—but still more expensive than calling in-process code. The result? Calling a CLR object can be considerably slower than calling a T-SQL stored procedure.
The Microsoft Regional Directors and I came up with several ways that SQL Server-based CLR assemblies can be a viable alternative to T-SQL-based procedures. SQL Server developers have already implemented solutions for virtually all these examples by using extended stored procedures and COM DLLs, but each of the following solutions would likely benefit from a rewrite to CLR-based code:
- Replacing or augmenting the functionality of large, complex T-SQL stored procedures—especially those that perform mathematical calculations such as complex business or scientific formulas. T-SQL's math skills aren't much better than mine, so offloading that complex math to a CLR-based DLL might be beneficial.
- Accessing a Microsoft Project file to update complex scheduling tasks. The logic in the CLR procedure lets an application interact with the Project data structures that T-SQL can't access directly. This approach can apply to any external data structure that doesn't have a traditional data-access interface such as process-control instrumentation or non-mainstream files.
- Performing complex geographic-mapping algorithms. By using Geographic Positioning Satellite (GPS) coordinates, one of the Regional Directors was able to write a query for the request, "Show me all the airports within 5 miles of this city." The CLR procedure that executed the geometry functions simplified the rather complex process of writing intelligent queries against a mapping database.
- Performing complex engineering calculations. For example, my daughter George brought me a 6-inch thick book of chemical-engineering formulas. With the CLR, you could code these formulas and easily access them from T-SQL procedures as functions. Again, be careful that the amount of CPU time needed to compute the formula justifies the trip to the CLR code and back.
In each of these examples, CLR code extends the power and reach of the T-SQL query language. That's what CLR-based procedures are for.
Testing the Concept
To test the effectiveness of a basic CLR-based stored procedure, I wrote a simple .NET assembly in Visual Basic .NET to convert Fahrenheit to Celsius and back. I tried two approaches. First, I called the assembly from a T-SQL stored procedure and discovered that it took roughly eight times longer to execute than the equivalent conversion code in T-SQL.
Next, I modified the .NET assembly and coded it so that the test application called it as a CLR-based T-SQL function. The performance of this version was better than the first approach but still about five times slower than the equivalent T-SQL code. However, I wrote the T-SQL code in one simple expression, so I'm not surprised at the result. The T-SQL code didn't require any higher math to compute—just simple arithmetic.
Of course, simple conversion isn't what CLR-based stored procedures are for—they're intended to call routines that are difficult, expensive, or impossible to execute from T-SQL. CLR-based stored procedures are designed as a replacement for extended stored procedures—not simple expressions.
After consulting with Peter Blackburn, my UK-based technical guru, I decided to try something a bit more ambitious—and more realistic. This time, I chose to implement RSA encryption by using the RSACryptoServiceProvider to take a given string and return an encrypted byte array. Almost 3 weeks later, I was able to get the new application working. Figure 3 shows a diagram of how my application works. Along the way, I discovered several problems (which I describe in a moment) that forced me to create much of the project in T-SQL batches. I also discovered a new key to debugging, which I describe in the sidebar "T-SQL and SQL CLR Debugging," page 14. The benefit of this experience is that I'm more familiar with SQL Server 2005's new SQL Server Management Studio (formerly called Workbench), which replaces Enterprise Manager, Query Analyzer, and a few other utilities. Microsoft is currently integrating the Management Studio tools into Visual Studio 2005 to make managing SQL Server databases even easier.
Prev. page
1
[2]
3
next page 
