TSQL->C# SP
-
CREATE PROCEDURE [dbo].[GetItemName]
@cItemID int
AS
BEGIN
DECLARE @cItemName varchar(100)
SET @cItemName = (SELECT cItemName FROM tItems WHERE cItemID = @cItemID)
SELECT @cItemName
END
I'd like to see this converted to CLR code. Yes I know I can select it directly but I am trying to show a point.
-
Here's a C# version
using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetItemName(SqlInt32 cItemID)
{
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("cItemName", SqlDbType.VarChar, 100) });
using (SqlConnection conn = new SqlConnection("Context Connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT cItemName FROM tItems WHERE cItemID = @cItemID", conn);
cmd.Parameters.AddWithValue("@cItemID", cItemID);
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
record.SetSqlString(0, rdr.GetSqlString(0));
}
}
}
SqlContext.Pipe.Send(record);
}
};
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Can you tell me exactly how the CLR SPs are created and stored?
I was under the impression that you could just write them in a .Net language in SSMS
just like you do the standard TSQL SPs.
The sample code you show looks like it has to be compiled as an assembly first, correct?
If this is the case, then what would be the benefits of doing such a thing .vs the TSQL
approach for SPs?
-
Using Visual Studio 2005 (or 2008) will allow you to build and deploy the assembly containing the CLR SP, it will also create the T-SQL wrapper for it. There's little (if any) benefit of CLR SPs over T-SQL, avoid if possible.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
So you must ALSO create a TSQL wrapper for them. Man, what good is having to do that?
Someone must be nuts to use CLR SPs...
-
MrBaseball34 (2/27/2009)
So you must ALSO create a TSQL wrapper for them. Man, what good is having to do that?Someone must be nuts to use CLR SPs...
Heh... finally... someone besides me see's that point!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (2/27/2009)
MrBaseball34 (2/27/2009)
So you must ALSO create a TSQL wrapper for them. Man, what good is having to do that?Someone must be nuts to use CLR SPs...
Heh... finally... someone besides me see's that point!
Someone must be really nuts to use CLR to make DML statements.
There are many areas in which T-SQL sucks: math, string manipulation, date manipulation ... name others.
IMHO, the best use of CLR not SPs, but UDFs.
-
Heh... and IMHO, CLR's have virtually no place including UDF's. RegEx and file handling are the only two places I've not been able to beat a CLR with T-SQL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (3/1/2009)
Heh... and IMHO, CLR's have virtually no place including UDF's. RegEx and file handling are the only two places I've not been able to beat a CLR with T-SQL.Exactly!
Also: string/text/binary manipulations are very slow in T-SQL.
We tried many tricks on MSSQL2K (very similar "Tally table") to parse ASN.1-encoded message in T-SQL, we ended with xp_ procedure. Xp_ are sucks for many reasons, but finally it was faster.
Also: Add to this list math with floats - MSSQL does not handle float datatype correctly.
What about new handwritten aggregate function?
It's just not possible in T-SQL.
(OK, ok - you can try tricks, but in most cases they will stink badly)
CLR makes possible to EXTEND T-SQL, not to replace.
And CLR is MUCH better than xp_ - that's my strong IMO.
-
alexander.yuryshev (3/1/2009)
Jeff Moden (3/1/2009)
Heh... and IMHO, CLR's have virtually no place including UDF's. RegEx and file handling are the only two places I've not been able to beat a CLR with T-SQL.Exactly!
Also: string/text/binary manipulations are very slow in T-SQL.
We tried many tricks on MSSQL2K (very similar "Tally table") to parse ASN.1-encoded message in T-SQL, we ended with xp_ procedure. Xp_ are sucks for many reasons, but finally it was faster.
Also: Add to this list math with floats - MSSQL does not handle float datatype correctly.
What about new handwritten aggregate function?
It's just not possible in T-SQL.
(OK, ok - you can try tricks, but in most cases they will stink badly)
CLR makes possible to EXTEND T-SQL, not to replace.
And CLR is MUCH better than xp_ - that's my strong IMO.
Got a sample of an ASN.1 file I could look at? Also, not sure what you mean by a "new handwritten aggregate function" unless you mean someone writting an ad hoc formula on the fly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Got a sample of an ASN.1 file I could look at? Also, not sure what you mean by a "new handwritten aggregate function" unless you mean someone writting an ad hoc formula on the fly.
Hi, Jeff.
This was not a file, it was CAMEL-message, but anyway:
ASN.1 BER is bytestring, it has predefined structure,
tags:
1. Has unique type id
2. Can be optional
3. Can be variable length
4. Length of tag is in header (like classical Pascal-strings)
(BER is looks like XML designed by sane people)
I suppose you already figure out what it means in T-SQL. Stinking loops all the way.
Let me suggest another VERY simple and VERY painful task for T-SQL:
Write a function of n-root of value with predefined precision.
Newton method is simple and can be written in every language.
But you have loop that ends when you get desired precision.
It's not a scholar task:
Two weeks ago I've wrote function of "effective mortgage rate" - very similar in looping and exit from loop.
In T-SQL it looks horrible.
And I do not have option to rewrite it in CLR UDF (SQL2K).
-
Why would you need to loop to do effective mortgage rates?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Look:
PV - total amount
CFi - is amount in period (in simplest case it's constant)
N - number of periods
We need to find r - effective rate.
There is no fixed formula for calculating r directly (it will be unique for every N)
Such things are calculated by approximations:
make guess for r
loop:
calc resulting PV
find difference with real PV
make better guess for r
While resulting PV and real PV differs greater than desired precision.
It's a classical Newton-Raphson method
T-SQL is not good in such tasks. C/C#/Java/Python/name others - works much faster.
-
Sounds like a fun binary search problem... I'll have to give it a try using pseudo cursors. Thanks, Alexander... I'll dog-ear this post and let you know what I come up with.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (2/27/2009)
MrBaseball34 (2/27/2009)
So you must ALSO create a TSQL wrapper for them. Man, what good is having to do that?Someone must be nuts to use CLR SPs...
Heh... finally... someone besides me sees that point!
:blink:
Hey guys,
At the risk of stating the bleedin' obvious:
If there wasn't a T-SQL wrapper, how would one access the CLR routine from, er, T-SQL? That, man, is what good it is. 😛
The 'wrapper' isn't onerous: the CREATE PROC body is simply [font="Courier New"]EXTERNAL NAME assembly.class.method[/font] - quite easy really.
Often, T-SQL is optimal choice (mostly for the things it is designed to do well, like set-based operations on local data).
Sometimes CLR is optimal (where the task is compute-intensive, or facilities exist within a .NET language which T-SQL cannot do, or does badly).
To say that one must be 'nuts' to use CLR stored procedures (without qualification) is, at best, a sweeping generalization.
CLR is a complement to T-SQL. It is a neat way to add some of the power and reusability of the .NET framework to SQL Server, without reinventing stuff in ever more additions to T-SQL.
A parting example. An application currently does its business-logic validation in middle-tier classes, written in a variety of .NET languages, and there is a desire to move the validation code to the database, would you:
a) Cancel all leave and rewrite the logic in T-SQL; or
b) Reference the existing classes in a SQL CLR routine, finish by lunchtime and take the rest of the week off? 😀
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply