[dba-VB] Performance of .Net Code in SQL Server 2005

Hi Shamil

Thanks. Looks like a very good reference.

/gustav

>>> shamil at users.mns.ru 25-01-2008 22:09 >>>
Hello Gustav,

CLR stored procedures can have output parameters (but not return values) of
many different types...

There are also CLR Scalar-Valued Functions (SVFs), which return a single
value. SVFs can return any scalar type, excluding varchar,
char, rowversion, text, ntext, image, timestamp, table, or cursor.

This information is from the book:

"Professional SQL ServerT 2005
CLR Programming
with Stored Procedures,
Functions, Triggers,
Aggregates, and Types"

By Derek Comingore and Douglas Hinson

which is very good and covers all(?) the aspects on developing and deploying
managed SPs, SVFs, triggers, ... for MS SQL 2005...

I haven't yet used SQL 2005 CLR programming - I'm waiting for a customer to
request for such programming :) - as far as I see from the book I mentioned
above there is no any "black magic" there...

BTW, this book has very detailed chapter (Chapter 5) comparing CLR and T-SQL
performance - I'd dare to give here an short summary of this chapter:

<<
As it relates to a performance comparison between SQL CLR and T-SQL, we have
shown in this chapter that:

-  String parsing and processing perform better with less CPU resources
using CLR base classes.
- Computation of even simple proportions performs better in SQL CLR.
-  SQL CLR table-valued functions can stream results faster to client than
T-SQL
-  T-SQL is still the king of data manipulation, extraction, and updating.
>>

I haven't seen yet/tried MS SQL 2008 - I will probably do that end of this
year if not later...

--
Shamil
 
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com 
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, January 25, 2008 10:38 PM
To: dba-vb at databaseadvisors.com 
Subject: Re: [dba-VB] Performance of .Net Code in SQL Server 2005

Hi Shamil

Oh, and some severe limitations too. This method returns an SqlBoolean:

CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR
methods that return either SqlInt32, System.Int32, void.

So ...

/gustav

>>> shamil at users.mns.ru 25-01-2008 17:23:10 >>>
Hello Gustav,

I'd use T-SQL CASE and built-in T-SQL functions: REVERSE and CHARINDEX and
SUBSTRING and/or I'd write UDFs using the listed above and other string
functions, which are many in T-SQL...

...I'd use .NET code for something more like business functionality or when
advanced parsing is needed etc. ...

Just my opinion...

--
Shamil
 
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com 
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, January 25, 2008 2:02 PM
To: dba-sqlserver at databaseadvisors.com; dba-vb at databaseadvisors.com 
Subject: [dba-VB] Performance of .Net Code in SQL Server 2005

Hi all

I came across this interesting article:

http://www.novicksoftware.com/coding-in-sql/Vol3/cis-v3-N14-performannce-of- 

dot-net-code-sql-server-2005.htm 

But is anyone here running .Net code in SQL Server?

A client has an mdb file with poorly designed tables and sloppy data. The
task is to clean these and transfer them to SQL Server tables with a better
structure.
I'm not so familiar in T-SQL, so I created queries in Access to test and
view the possible output. A typical query contains code like this:

SELECT .. IIf(InStrRev([dessNr],"-")>0 And
IsNumeric(Right([dessNr],1)),Left([dessNr],InStrRev([dessNr],"-")-1),[dessNr
]) AS dessinNoBase

T-SQL knows nothing about InStrRev so I looked for options on how to
translate such functions.

One option is to use .Net code. Speed is not important (small tables).
How do I approach this? Well, the top links at that page link to
presentations on:

  SQLCLR Programming with SQL Server 2005

Still, has anyone been working with this?

/gustav