4

So I thought I had a perfect use-case for a CLR SQL Procedure. I've search the Net for perhaps a similar example where data is retrieved, records added and updated. I have not looked at a SQL CLR procedure for awhile, but since it was released in 2005 (some 6 years ago!) I would have hoped there were plenty of examples!

I'm considering it because I have to look at some data, run it through a bunch of procedural logic, update, and then get it back to the client. My thinking here is to get as close to the DB metal as possible, and use that hardware to make it happen quickly.

Is anybody using SQL CLR? If you have, what has been your experience with it?

p.s. originally posted on stackoverflow and moved here based on a comment.

Community
  • 1
codeputer
  • 141
  • 4
  • Is there a reason you aren't using Sequel Server Integration Services? That's probably the "official" solution for what it sounds like you are trying to do. – psr Aug 29 '11 at 16:17
  • Are you certain you really need procedural logic. I have almost never seen a case where procudral logic for an update or insert was needed if you know how to use set-based logic. This might give you some ideas on whther this can be done in set-based code: http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them – HLGEM Aug 29 '11 at 20:56
  • I have seen lots of procedural logic in SQL procedures, mainly when it comes to looping. However, there is a vast difference between procedural and set-base logic. To say that your can convert one to another in almost all cases seems far fetched to me. – codeputer Aug 31 '11 at 21:09
  • psr - this logic is part of the application flow, users edit data, I push it through business logic, and send it back. In some cases, I can easily update 2000+ records base on one smal change. I've been doing some reading, so I think I'll alter my question, as I feel the SQL CLR is the right place for this logic, but looking for best practises to move it in and out of the SQL CLR. Thanks for the input! – codeputer Aug 31 '11 at 21:13

4 Answers4

1

SQL CLR integration was developed mainly because, implementing logic through T-SQL was really hard. .NET Framework if filled with thousands of useful libraries, to which you have no access at SQL engine level. Thus, any logic should be implemented from scratch. For example, a simple foreach loop should be implemented with cursors, which are honestly, far less productive.

I have experience of working with SQL CRL integration. I did it for date-time conversion at database engine level. Date-time conversion at the level of database engine is really really hard, while .NET, has System.Globalization which facilitates the work.

The main point of this method, is to follow the step exactly as described (like extension methods in which, methods should be static functions inside static classes inside first-level namespace). If you fail to do something exactly as you're told, things simply fail.

Saeed Neamati
  • 18,142
  • 23
  • 87
  • 125
1

I have used SQL Clr procs and triggers with very good effect to help database performance in some cases. There are situations where your TSQL logic is just going to run slow (full text search without FTS indexes), and there are some things you just can't do in TSQL (lookup a DNS address, map reduce, image processing, OCR, unzip a file, complex algorithms, etc).

I'm considering it because I have to look at some data, run it through a bunch of procedural logic, update, and then get it back to the client. My thinking here is to get as close to the DB metal as possible, and use that hardware to make it happen quickly

But that is not a scenario where I would recommend using them. The other poster is correct that set theory almost always has a way to do what you want.

SQL CLR is not closer to the metal, you are running a full instance of .net clr inside your database. If you have none right now, adding one will add a lot of RAM usage for that one proc. If you have 20 then the cost is not so bad "per proc".

If you are trying to do all these calcs to get the data back to the client, then just do it on the client. The client usually has close to 100% of their CPU available to them. The server will never have anywhere near that for one user.

If you have truly central logic or complex business requirements that you don't want to put on every client, put that on the server. Don't do simple CRUD operations in SQL Clr, it doesn't buy you anything usually.

Jason Short
  • 111
  • 3
0

You're better off using CLR than t-SQL if it is complicated logic. I would only consider this approach over using your application code as long as your consider:

  1. You need/prefer to share this capability with multiple applications that use the database and are unable to share their internal code.
  2. The database has plenty of resources available to make the calculations.

Sharing code is great, but not at the expense of your database performance.

JeffO
  • 36,816
  • 2
  • 57
  • 124
0

Firstly, try very very hard not to use it - whilst I implemented both a CLR stored procedure and a streaming vector table function, both had problems, mainly in performance.

My sproc basically bombed out when presented with a million rows of data, whereas a sql procedure that called a scalar function (in CLR - in my case it provided a regex function) worked, and worked far far quicker than the sproc did. The SVTF had some issues with security such that it would only work on a developer edition DB (we like security at our place) so that was dropped. Performance was similarly bad but not as bad as the sproc.

So stick to simple scalar functions but try not to use them much and keep them really simple.

gbjbaanb
  • 48,354
  • 6
  • 102
  • 172