0

We recently had a need to validate lot of address (strings) in SQL Server, t-sql code was showing latency we tried SQL CLR and wow! 700% performance improvement. We are looking at what other places we can use it??? I guess not all logic should be done in SQL CLR as we will not be able to do SET based operation.

Pritesh
  • 1,938
  • 7
  • 32
  • 46
  • 3
    So what's the question here? Not sure what we can do to assist/guide. Without knowing what your application does use of CLR implies understanding of it thus you'd know where you have opportunities to improve... – xQbert Apr 20 '12 at 17:56
  • @xQbert I am looking what are other scenarios were i could consider SQL CLR, actually it's quiet tempting to put most stuff in SQLCLR as .net programming has got better intelisense, richer library,debugging support.... I do not want to use it at wrong place, only where it will benefit performance. – Pritesh Apr 20 '12 at 18:04
  • Well one [suggestion](http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/20/428014.aspx) says never yet [others](http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/5/Determining-When-to-Use-CLR.aspx) suggest sometimes... Still [Others](http://beyondrelational.com/modules/17/interview-questions/238/interview-questions/9950/sql-server-what-is-the-advantage-of-clr-integration.aspx) say only when built in functionality doesn't exist. So far it seems like there is no clear cut idea on what is "Best" My favorite said, "CLR is disabled by default *nuff said*" – xQbert Apr 20 '12 at 18:13
  • 1
    This makes me wonder: 700% gain, are you sure the execution plan was optimized. Was it bad SQL to begin with? CLR is (according to several articles found in the above) is often used as a crutch when someone doesn't know T-SQL. talk to a DBA before going CLR on everything. – xQbert Apr 20 '12 at 18:18
  • 1
    I use one only when necessary - a good example of this is custom aggregate functions: ie concatenation / string join. – YS. Apr 20 '12 at 18:21

1 Answers1

0

After doing lot of hit and run through weeknd, we come to conclusion that for string and math functions CLR (C#/VB) has very much bigger libarary (optimized/tested) then T-SQL so taking advantage of bigger library we can get advantage over string and Math operation done in T-sql.

Pritesh
  • 1,938
  • 7
  • 32
  • 46