To CLR or Not to CLR (Design and Performance Considerations)

It is important to exercise caution when you are designing CLR database objects— do not overuse them. As always, it is important to use the right tool for the job. I will go through some general rules, as well as some specific ones, for implementing code in CLR:

• As a rule of thumb, Transact-SQL code is better for relational operations and CLR code is better for operations that include lots of computations or that require access to classes in the .NET Framework. Transact-SQL set operations are the optimal way for implementing data access, and even if you need to perform them in managed code, you do so by issuing Transact-SQL commands through ADO.NET.

• Complex computations (or business rules) should not be implemented in CLR procedures. Implement them as middleware components instead. If you need to transfer large quantities of data to middleware, you may decide to break this rule and implement processing on a database server.

• Monitor sys.dm_clr_appdomains.cost to differentiate CPU usage of CLR components from the rest of the server in order to prevent the database server from becoming a bottleneck in the system. If CPU usage is too high, move more of the business logic or processing from the data to the middle tier.

• Scalar functions are typically good candidates for implementation in managed code. You should test to find out whether the overhead of switching between Transact-SQL and CLR code outweighs the disadvantages to processing in Transact-SQL alone.

• CLR table-valued functions are typically faster than their Transact-SQL counterparts. The reason is that a CLR TVF is implemented using a streaming model that does not require the materializing of results in an intermediate table as a Transact-SQL TVF does.

• Aggregations are easier to implement in CLR and they are typically an order of magnitude faster than the Transact-SQL code needed to perform the same operation. Unfortunately, they have to be implemented for a specific data type in CLR, which might be an issue.

• CLR code is a more reliable, more secure, and often faster alternative to documented and undocumented extended stored procedures.

• Queries can be parallelized (split into parts to be executed in parallel) if they are executed in Transact-SQL.

• If the procedure contains many DML statements, it is probably faster to implement it in Transact-SQL because it will be parsed, checked syntactically, and compiled during creation instead of at runtime.

• I recommend that you implement data rules (a subset of business rules that is closely tied to data) as close to the data as possible. Managed user-defined types and managed triggers should be considered (along with standard constructs such as constraints or Transact-SQL triggers) as a way to implement them.

• Managed UDTs are a long-term risk because they cannot be changed without invalidating the content of tables. Something will probably have to be changed in the future—data rules or the version of CLR delivered with a version of SQL Server, or some future service pack.

• ADO.NET has some interesting transaction-related features. If you want to create a nested stored procedure to work outside of the caller's transaction, implement it in a CLR stored procedure.