Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vgr.raju
Yak Posting Veteran
95 Posts |
 Posted - 2006-10-29 : 05:54:14
|
| Hello all, If possible,can some one point me to the white papers,articles which talks about perfomance difference between T-SQL and C# Stored Procedures.Thanks!RajuThanks!Rajuhttp://www.trickylife-trickylife.blogspot.com/ |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-10-29 : 14:29:01
|
I use CLR procs for things sql isn't that good at, like complex math calculations. also if you have heavy-duty string manipulations, like parsing a log file or something, that's often better done in compiled code. Another reason I use CLR procs/udfs is for code reuse. say you have a library of static util functions that all your managed apps use. You can expose that library to your sql code as well by importing the assembly and wrapping each static method in a CLR udf.Finally, you can use CLR procs to minimize the attack surface on your sql server. Let's say you have some sprocs now that depend on xp_cmdshell. Throw those sprocs away and reimplement them using the CLR (use a dev you trust, as you'll have to import the assemblies as UNSAFE). Then disable xp_cmdshell.  Here are some links for you courtesy of google:what ms has to say about it. start here:http://msdn2.microsoft.com/en-us/library/ms345136.aspx ppt from an MVP with some guidelines on when to use CLR:http://download.microsoft.com/download/9/f/a/9fadc29f-8df1-486f-b200-94f79ee7a7de/DBS401.ppt This one is kind of lame because the sql he's comparing uses a cursor. It's like comparing javascript and C++ at sorting, but using quicksort in js, and bubblesort in C++! anyway fwiw here it is: http://blogs.ittoolbox.com/visualbasic/dotnet/archives/clr-stored-procs-versus-tsql-stored-procs-round-2-10878 (EDIT: the js vs C++ is perhaps not a fair analogy on my part. They are different algorithms after all. It is sort of a strawman argument though, to compare cursors and compiled code. It's well-known that t-sql cursors suck, so it's not that surprising that CLR wins. I guess the moral is, if you HAVE to iterate thru a record set, better to do it in the CLR. but 99% of the time there are good ways to get things done in a set based way and avoid procedural code altogether. enough!)
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
 |
|
|
|
|
|
|
|
C# vs T-SQL Stored Procedures