SqlServer2005: Move From TSQL To SQLCLR And Save Your Project
June 24, 2008
Many organisations have lots of business rules in TSQL stored procedures.
You’re right, that’s crazy but that’s what we see all the time, and our current project in Sydney, Australia using the Eagle PACE product is no different. Eagle PACE is an Opertaional Data Store for the financial services industry, in our case the Funds Management sector. At it’s heart is an enterprise database with a relational data model appropriate to the industry, and is surrounded by data feed mechanisms which in basic terms are a series of Stored Procedures.
Whilst a TSQL stored procedure is good for simple logic, eg stringing together a couple of selects and inserts, it is simply not up to the task of handling complex business rules and workflow.
I would say that a TSQL stored proc of more than 200 lines is just to complex to debug, enhance and test.
TSQL falls short in the following areas:
1) Code reuse.
There isn’t any.
No classes, no rowtypes, no inheritance of stored procs or functions.
No way to encapsulate functionality - the only way is, that’s right, you have to write another stored proc and have nested calls - yuk!
Absolutely awesome when you have 133 parameters to pass around.
[Dont laugh - we have this many parameters and more in the stored proc interfaces we have to maintain]
2) Error handling.
SqlServer2005 has try/catch, which is good, but not yet widely used.
Most TSQL code we see has lots of if/goto statements after each real code statment.
This blows out the size of the proc and turns in into spaghetti.
This kind of error handling is quite intrusive, so one loses track of what the storeed proc is actually doing due to all the eror handling code.
3) Testability and code coverage.
It is hard to unit test and do code coverage on a large stored proc.
You cannot break it down and test chunks of it in unit tests.
Here’s a scenario we see all the time.
You’ve been handed a 2000 line stored proc and it has a bug or it needs an enhancement.
There is no documentation of course, so to fix the problem you have to figure out what the hell it does, starting from the top and following the code paths until you find the problem.
Oh dont forget all those goto error statements.
Oops, I’ve just been distracted because the PA just walked past my desk, and I’ve lost my place somewhere in the bowels of this multi-page proc, so I have to start again at the top to figure out where I have to make the code fix.
At buildmasters, we were presented with several dozen very large TSQL stored procedures that were written in other countries [some of the worst TSQL code I have ever seen I must add] that we had to maintain and enhance [no documentaion provided of course]. All part of an Eagle PACE consulting enegagement in Sydney, Australia.
These stored procs were unmaintainable.
It was clear that we would not meet our development deadlines if we did not innovate at the TSQL level.
Not only this, but the vendor’s product (Eagle PACE) could only talk to external components via ODBC, so refactoring into a C# or Java business layer was out of the question.
The innovation we came up with was to write/refactor all TSQL stored procedures and functions into C# SQLCLR.
Not only did we get to write business logic in C#, we were able to abstract much of the code into base classes and helper methods.
This, together with the use of C# generics, nullable types and predictable try/catch error handling, meant that we wrote a lot less code.
We literally reduced the code base by 1000’s of lines by moving from TSQL to SQLCLR.
Less lines of code means less bugs, because, as we all know, coding is the art of inserting bugs into software.
Now, a new developer can approach an unfamiliar SQLCLR stored proc with confidence that he will be able to understand its purpose within a short time.
Because common functionality is encapsulated in base classes and helper methods, a stored procedure’s code consists of only the specific business rules required for its purpose.
It is much easier to find a bug or the place where an enhancement is to be performed.
Error handling is consistemt and non-intrusive - just throw an exception and let the base class do the rest.
Also, developers can unit test SQLCLR C# methods and base classes in isolation.
We also wrote a deployment tool that takes a C# SQLCLR assembly and registers the appropriate stored procedures and functions into the SqlServer database, so that as far as Eagle PACE is concerned, our SQLCLR stored procs appear as normal TSQL stored procs, happily callable from the vendor’s ODBC interface.
We have innovated the Eagle in a way that has never been done before.
This is done automatically as part of our continuous build and deploy process, and all these C# SQLCLR stored procedures are subject to the same unit test code coverage as the rest of our code.
SQLCLR - really cool technology.
Comments
4 Responses to “SqlServer2005: Move From TSQL To SQLCLR And Save Your Project”
Got something to say?
I have been trying to convince my technical lead that we should replace our long and tedious procedures with CLR but they don’t seem to undertsnad the benefit - how do I justify the 3 weeks I need to recode the procedures I am supporting in CLR????? Help!
Peitre
I’m not sure I agree entirely with you! SQLCLR is not a good fit for ALL strored procedures and functions! There are things that TSQL will do better 10 times out of 10. How can you beat TSQL’s performance for set based operations? SQL CLR is not a silver bullet for everything my friends!
I am definately with the last comment on this one. In benchmarks, there hasn’t been a whole lot that TSQL ccan’t beat when coded properly. CLR is definately not for data access type stored procedures, and abstracting TSQL code up into SQLCLR for data access will only hurt performance. Complex Business Logic is a very hotly debated subject, where most will argue to abstract that to a middle tier or application server, rather than having it draw memory from SQL.
M:
You are right about the performance issue.
You can and we do set based operations inside our sqlclr code.
For example, we use datasets and execute updates and insert sql commands where required,
Sqlclr does not stop you from doing things effeciently.
As I said in the blog, the gain for us is the re-usablity of code using base classes and generic, error handling, reduction of code lines, debug-ability and unit test -ability.
We do not lose out on performance.
JW:
I would love to have used a business layer for the complex business layer of our current project.
The reason we did not is that the package to which we are interfacing has ONLY
an odbc connector to sql server.
As I said in the blog, the package does not support referencing business layer
dll’s for example [and we cant modify the package].
So using sqlclr was the only way we could
a) keep the package happy, as it thinks it is calling just a tsql stored proc, and
b) use a much friendlier, high level language ie C#.
Again I say, we have not lost out on performance because we are using
datasets and update quries inside the sqlclr code to do set based operations.