Actual Reason to Use CLR
-
Our need is to be able to convert an abitrary UTC time to local time (and vice versa) for an arbitrary US county or country in Europe. what I mean by arbitrary time: any date back to a cutoff of 2001/01/01 -- not just "today" or "right now". that's what made it messy: the rules for when DST turns on and off have changed through the years, and they don't change uniformly in space. Indiana is a case of this, and worse: some counties there even changed timezones in Apr 2006! To implement this logic twice, and maintain two codebases for it, was a cost I was not willing to pay.
We haven't experienced any problems with our solution. we didn't download it off the web though - built it in house. maybe that's where your problem was. you get what you pay for.
i'm curious to see what you did in t-sql though.
---------------------------------------
elsasoft.org -
Code reuse in this circumstance sounds really useful. I think I might take a look around. I'm sure we have something along these lines that might, possibly benefit.
I'm curious to see the TSQL solution as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I am considering using CLR for something that does involve data access and would value your opinions on that. We have a lot of stored procedures to analyze financial data. The result is displayed in SSRS on our intranet. But we also need to feed several other applications with exactly the same data but formatted in XML. So I am basically left with the choice to duplicate all stored procedures (and maintain them 2 times) once FOR XML and once as a recordset; or I need to convert everything to dynamic sql and conditionally paste “FOR XML” or not at the end. I like neither of these options for several reasons. So I was thinking that maybe I could write a wrapper in CLR that builds a resultset by executing the name of the stored procedure passed as a parameter; and then selects FOR XML out of that resultset.
What do you think of that? Or are there better suggestions?
Thanks,
Jan
-
Most of our procs are converted to XML out on the client (app server actually). No real need to output to XML, in most cases, in the procedure. From your database, I don't think I'd change a thing. Just put an application layer between your db & the other apps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Of course Grant, you are right, this would be the better way.
Suppose I was too much thinking database.
thanks,
Jan
-
As suggested, i wouldn't do anything using the clr that couldn't be done easily and efficiently using TSQL. However, I've found that clr functions can be very useful - One of the most useful abilities is its excellent string manipulation.
-
I haven't found the implementation of clr too difficult, but then i'm as much a .Net programmer as a Sql Developer.
-
And therein is the reason that many of us are slow to adapt CLR. We're not .Net developers. I stopped doing web development about the time that the company I worked for then was starting to switch to .Net.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Where separation in 'n' tier applications CLR functions have little use because each component is a specialised resource.
Where the SQL Server is more of a one-stop-shop then the CLR functions are useful but there is usually some way of getting around them such as the use of SSIS.
I don't find CLR functions difficult but I do worry about the appropriateness of their use in the SQL layer.
-
...I don't find CLR functions difficult but I do worry about the appropriateness of their use in the SQL layer....
Indeed, this is the biggest concern
and of course the performance impact in case of abuse
For the moment our implemented ddl-triggers prevent unauthorized implementation of clr-modules. That's a big help !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
any technology can be misused.
that's not a reason not to use it. just be careful not to misuse it.
---------------------------------------
elsasoft.org -
Although i've not found many real cases to implement CLR one that i thought about the other day was that accessing data in a CLR vb.NET sp would be quicker than using CURSORS. I know cursors should be used as sparingly as possible, but in a case where this kind of processing can't be avoided wouldn't it be better writing a CLR sp in VB.NET/C#.NET than using cursors?
I was wondering if anyone out there has seen any efficiency savings from using CLR sp's over TSQL sp's that use cursors.
I only mentioned this as no one else seems to have thought about using it in this way.
-
When built right in the right circumstances, CLR functions perform very well (better than UDF's for that matter). Just about anything string-related (rising to the level of requiring a separate process) seems to be more easily done in CLR;certainly just about anything dealing with the "world" outside of SQLServer (other than "straight" imports/exports to/from well-supported formats) would be solid candidates for at least review (after all - CLR has access to native hooks on a lot more things than T-SQL might "out there").
As to replacing Cursors - streams do tend to be a LOT more efficient than cursors if it's a big one. It seems to be a matter of optimization: framework and CLR teams spent a lot of time working with streams, so there's a lot optimized there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
After carefully reading each post it seems that there is a very, very small niche for CLR. The IS/IT world has become a victim of the MS marketing machine in this case definitely. Just look at all of the collective resources that have been possibly wasted that could have been used for solutions to more common problems !
On a more personal note, my deepest sympathies to those converting time in Indiana. Whilst being a life long Hoosier, I have been luck to live in one of the more 'normal' areas (the Northwest corner just outside of Chicago).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
well you can't blame me M$ have made me rely too much on point and click)Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply