Experience with CLR Stored Procedures in MS SQL Server 2005
-
Hello all. I am interested in hearing about other's experiences with CLR Stored Procedures (C#, VB.NET, whatever) in SQL 2005. Positive, negative, no difference. Better with single-tables than with large joins. Techniques and optimizations. What worked and did not work and why. What things they are better for than others (i.e. heavy mathematical or string operations). Etc. I am also posting a small message in the C# forum to see if any developers over there have experience and would like to come over here to share. I am looking to see if any of my operations can be optimized by changing them to CLR-based SPs. But rather than go the shotgun approach and rewrite everything, I want to get the best bang for my buck, and figured starting here would be a good idea. Thanks! Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
Hello all. I am interested in hearing about other's experiences with CLR Stored Procedures (C#, VB.NET, whatever) in SQL 2005. Positive, negative, no difference. Better with single-tables than with large joins. Techniques and optimizations. What worked and did not work and why. What things they are better for than others (i.e. heavy mathematical or string operations). Etc. I am also posting a small message in the C# forum to see if any developers over there have experience and would like to come over here to share. I am looking to see if any of my operations can be optimized by changing them to CLR-based SPs. But rather than go the shotgun approach and rewrite everything, I want to get the best bang for my buck, and figured starting here would be a good idea. Thanks! Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFilesJames R. Twine wrote:
I am looking to see if any of my operations can be optimized by changing them to CLR-based SPs.
Not sure I understand your question. SQL Server (and others) have supported developing stored procedures using external languages like C/C++ for many years now. What differentiation are you making between that and using a CLR based language for developing stored procedures?
led mike
-
James R. Twine wrote:
I am looking to see if any of my operations can be optimized by changing them to CLR-based SPs.
Not sure I understand your question. SQL Server (and others) have supported developing stored procedures using external languages like C/C++ for many years now. What differentiation are you making between that and using a CLR based language for developing stored procedures?
led mike
I know about Extended Stored Procedures and have even written/used them in the past. CLR-based SPs run in the .NET environment, and even though it is hosted by SQL Server, they can be considered more stable because errors/exceptions cannot cause instabilities in the XP server's executable (stepping on its memory, for example). This is one reason why they may be more attractive, especially if deploying them to a server that is used for other purposes at a client site. They also may work faster (other than the initial compilation hit) on systems that would normally run a XSP in a separate process because the cross-process overhead is eliminated (somewhat). CLR-based objects can be used as UDTs. Anyway, the part of my post that you quoted was miswritten - of course I can optimize some of my operations. The question is more along the lines of what KINDS of operations are likely to be sped up by moving to a CLR-based SP. I am not trying to compare the performance of XSPs vs. CLR-SPs. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
Hello all. I am interested in hearing about other's experiences with CLR Stored Procedures (C#, VB.NET, whatever) in SQL 2005. Positive, negative, no difference. Better with single-tables than with large joins. Techniques and optimizations. What worked and did not work and why. What things they are better for than others (i.e. heavy mathematical or string operations). Etc. I am also posting a small message in the C# forum to see if any developers over there have experience and would like to come over here to share. I am looking to see if any of my operations can be optimized by changing them to CLR-based SPs. But rather than go the shotgun approach and rewrite everything, I want to get the best bang for my buck, and figured starting here would be a good idea. Thanks! Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFilesI've used CLR integration for several cases and I have to say that I have totally discarded using plain C so my answer may be a bit biased :). I like to use normal T-SQL procedures and plain SQL especially when transactional processing is needed and/or when working with result sets. T-SQL performs better in these areas. What I typically do, is that I only 'extend' T-SQL with C# based functionality, not replacing it. Some of the usages have been: - complex mathematical functions not implemented in T-SQL. Using these for columns in result sets - integration to the database server or even to the client calling the db - file handling capability for stored procs (and also logging) - extended string handling (T-SQL is actually very lousy in this) etc. The negative aspects I see are: - integration to the actual connection calling CLR based functionality. It's working, but for some reason not very intuitive (at least not for me) - installation is a bit difficult if it's meant to be done by client. Creating a full silent install script for the whole database server requires more work than I originally expected - cannot use fibers so in some installations it may cause performance problems on other areas of database usage Mika
The need to optimize rises from a bad design. My articles[^]
-
I know about Extended Stored Procedures and have even written/used them in the past. CLR-based SPs run in the .NET environment, and even though it is hosted by SQL Server, they can be considered more stable because errors/exceptions cannot cause instabilities in the XP server's executable (stepping on its memory, for example). This is one reason why they may be more attractive, especially if deploying them to a server that is used for other purposes at a client site. They also may work faster (other than the initial compilation hit) on systems that would normally run a XSP in a separate process because the cross-process overhead is eliminated (somewhat). CLR-based objects can be used as UDTs. Anyway, the part of my post that you quoted was miswritten - of course I can optimize some of my operations. The question is more along the lines of what KINDS of operations are likely to be sped up by moving to a CLR-based SP. I am not trying to compare the performance of XSPs vs. CLR-SPs. Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFilesCLR based code may/will be useful for the areas where TSQL is not optimised; that is procedural code, string handling and complex calculations. You also have access to .NET Framework classes for registry, files, XML etc. Comparing the performance of XSPs and CLR is probably moot anyway since XSPs are deprecated in SQL 2005 onwards.