SQL Server CLR stored procedures in data processing tasks - good or evil?

Question

In short - is it a good design solution to implement most of the business logic in CLR stored procedures?

I have read much about them recently but I can't figure out when they should be used, what are the best practices, are they good enough or not.

For example, my business application needs to

• parse a large fixed-length text file,
• extract some numbers from each line in the file,
• according to these numbers apply some complex business rules (involving regex matching, pattern matching against data from many tables in the database and such),
• and as a result of this calculation update records in the database.

There is also a GUI for the user to select the file, view the results, etc.

This application seems to be a good candidate to implement the classic 3-tier architecture: the Data Layer, the Logic Layer, and the GUI layer.

• The Data Layer would access the database
• The Logic Layer would run as a WCF service and implement the business rules, interacting with the Data Layer
• The GUI Layer would be a means of communication between the Logic Layer and the User.

Now, thinking of this design, I can see that most of the business rules may be implemented in a SQL CLR and stored in SQL Server. I might store all my raw data in the database, run the processing there, and get the results. I see some advantages and disadvantages of this solution:

Pros:

• The business logic runs close to the data, meaning less network traffic.
• Process all data at once, possibly utilizing parallelizm and optimal execution plan.

Cons:

• Scattering of the business logic: some part is here, some part is there.
• Questionable design solution, may encounter unknown problems.
• Difficult to implement a progress indicator for the processing task.

I would like to hear all your opinions about SQL CLR. Does anybody use it in production? Are there any problems with such design? Is it a good thing?

Answer 1

I do not do it - CLR ins SQL Server is great for many things (calculating hashes, do string manipulation that SQL just sucks in, regex to validate field values etc.), but complex logic, IMHO, has no business in the database.

It is a single point of performance problems and also VERY expensive to scale up. Plus, either I put it all in there, or - well - I have a serious problem maintenance wise.

Answer 2

Hey,

Personally I prefer to have business functionality not dependent on the database. I only use CLR stored procedures when I need advanced data querying (to produce a format that is not easy to do in SQL). Depending on what you are doing, I tend to get better performance results with standard stored procs anyway, so I personally only use them for my advanced tasks.

My two cents.

HTH.

Answer 3

Generally, you probably don't want to do this unless you can get a significant performance advantage or there is a compelling technical reason to do it. An example of such a reason might be a custom aggregate function.

Some good reasons to use CLR stored procedures:

• You can benefit from a unique capability of the technology such as a custom aggregate function.

• You can get a performance benefit from a CLR Sproc - perhaps a fast record-by-record processing task where you can read from a fast forward cursor, buffer the output in core and bulk load it to the destination table in batches.

• You want to wrap a bit of .Net code or a .Net library and make it available to SQL code running on the database server. An example of this might be the Regex matcher from the OP's question.

• You want to cheat and wrap something unmanaged and horribly insecure so as to make it accessible from SQL code without using XPs. Technically, Microsoft have stated that XP's are deprecated, and many installations disable them for security reasons.
  
  From time to time you don't have the option of changing the client-side code (perhaps you have an off-the-shelf application), so you may need to initiate external actions from within the database. In this case you may need to have a trigger or stored procedure interact with the outside world, perhaps querying the status of a workflow, writing something out to the file system or (more extremely) posting a transaction to a remote mainframe system through a screen scraper library.

Bad reasons to use CLR stored procs:

• Minor performance improvements on something that would normally be done in the middle tier. Note that disk traffic is likely to be much slower than network traffic unless you are attemtping to stream huge amounts of data across a network connection.

• CLR sprocs are cool and you want to put them on your C.V.

• Can't write set-oriented SQL.