CLR Table-valued function

Question

CLR Table-valued function

preetha.sathyamoorthy

Ten Centuries

Points: 1180
More actions
May 15, 2008 at 4:16 am

#186639

Can any one of you give me a sample to convert the SQL table-valued function to CLR table-valued function using C#.Net

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 1

[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRow", TableDefinition = "number int, pricedecimal(18,6)")]

public static IEnumerable methodname(int CustomerPID)

{

ArrayList rowsArray = new ArrayList();

GetData(CustomerPID, rowsArray);

return rowsArray;

}

private static void FillRow(Object obj, out int number, out decimal price)

{

object[] row = (object[])obj;

number= (int)row[0];

price= (decimal)row[1];

}

private static void GetData(int CustomerPID, ArrayList rowsArray)

{

using (SqlConnection conn = new SqlConnection("context connection = true"))

{

conn.Open();

using (SqlCommand cmd = conn.CreateCommand())

{

string SQL = " select number, price from sales where CustomerPID = @CustPID";

cmd.CommandText = SQL;

cmd.CommandType = CommandType.Text;

cmd.Parameters.Add(new SqlParameter("@CustPID", CustomerPID));

//Execute the command

SqlDataReader sqlReader = cmd.ExecuteReader();

while (sqlReader.Read())

{

object[] column = new object[2];

column[0] = sqlReader.GetInt32(0);

column[1] = sqlReader.GetDecimal(1);

rowsArray.Add(column);

}

sqlReader.Close();

}

Actually, I got the solution for my problem. I am posting this for others reference. I hope it will be useful for those who are all struggled like me.

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 2

I'm pretty sure that you just shot yourself in the head performance wise... the myth that all CLR's are faster than T-SQL is just that... a myth. Most of us can smoke almost every CLR... ReGex replacement is the only exception I've seen, so far, and many of us have been able to tie some of that functionality performance wise.

Why do you need a CLR that is so easily and quickly solved in 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)

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 3

Hi,

Actually, I just wants to test the performance of T-SQL and CLR and i have to report that. I already read that T-SQL is faster than CLR. But i have to show the proof by testing with the same data(millions of records) which is helpful for our project. Tat's why I asked about that.

Best Regards,

Preetha SG

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 4

Outstanding! Good luck with your tests! It's a worthy cause! I'd be interested in the results of your tests. I don't know how to write CLR's, but I think I can probably help in just about anything having to do with T-SQL... lemme know if you need any help on this!

--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)

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 5

Hi,

Thanks for your interest.

We completed the test and we sent the report that T-SQL is better than CLR for both scalar-valued and table-valued functions.

Best regards,

Preetha S

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

We would love to see the results...?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

preetha.sathyamoorthy (5/27/2008)
Hi,
Thanks for your interest.
We completed the test and we sent the report that T-SQL is better than CLR for both scalar-valued and table-valued functions.
Best regards,
Preetha S

Like Barry mentioned - it would be interesting to see what you tested and what results you got.

----------------------------------------------------------------------------------
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?

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 8

Hi,

First, we have created stored procedure and function in T-SQL for our project. Then we do the same in CLR. Then, based on different datasets (that means, 1000, 10000, millions of records like that) we checked the performance(timings).

Finally we concluded that T-SQL is better than CLR with proof.

Best Regards,

Preetha SG

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 9

What kinds of functions?

--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)

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 10

We used only Table-valued and Scalar-valued functions for our testing. I can't able to tell more than this, I think. SORRY.

KeiserSoze SSC Rookie Points: 35 More actions · Answer 11

To involve myself in this discussion a bit late maybe but...

Yeah there are differences between T-QSL and CLR, but CLR doesn't exist for writing stuff like "Select.. from" clauses.

To use T-SQL or CLR you have to be familiar with the usage and the goal that wants to be achieved.

I can give you few examples where CLR will be much faster then T-SQL and not only that difference in speed will increase with the size of DS that needs to be done.

1. example: in T-SQL i would like to preform complex mathematical calculation for eg. - can you do it in T-SQL? (if you can I assure you CLR will be better solution for this)

2. example: complexity of writing some T-SQL's sometimes goes beyond human imagination, and that can be achieved with .NET native language in a few lines of code.

Implementation of basic T-SQL stuff is a big NO if you are implementing in CLR. Better result you will gain with T-SQL. Implementing for eg. some kind of state machine stuff (i'm not saying it's impossible to do with T-SQL, don't want to argue with question "why would you do that in the 1st place?", it just something that came up my mind just watching from DB point of view and not searching for the best solution) or something else that's not that strait forward in T-SQL will give benifits if implemented in CLR.

Think it's great idea to have this in addition to extended SP's as before that were written in C/C++.

Working with usual DB concept, of very simple DB design, don't know if you will ever use CLR, but in some cases you will for sure.

The reason of opinion that CLR is slower then T-SQl is just because people didn't get familiar with CLR and doing ridicules performance test to show CLR is slow.. yeap doing "select..from" will show T-SQL is better... not only that most of them don't know to write proper T-SQL likewise.

Check this performance test and articele: http://www.codeproject.com/KB/database/TableValuedFnsAsArrays.aspx

😉

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 12

nenad.prekupec (6/11/2008)
The reason of opinion that CLR is slower then T-SQl is just because people didn't get familiar with CLR and doing ridicules performance test to show CLR is slow.. yeap doing "select..from" will show T-SQL is better... not only that most of them don't know to write proper T-SQL likewise.
Check this performance test and articele: http://www.codeproject.com/KB/database/TableValuedFnsAsArrays.aspx
😉

You ridicule the performance tests that people do that show CLR performance is slow and you offer up that very poorly written performance test that uses a While loop instead of a good set based solution as an argument and then call it from embedded SQL? Heh... you better check again. Check out what a "Numbers" or "Tally" table solution will do for that type of solution... it handily beats the CLR solution. Matt Miller and I have run many tests on this very subject and about the only thing that T-SQL can't beat a CLR on is a reasonably complex RegEx replace and then T-SQL still comes close. And he's NOT anti-CLR! How about a million row running total? Wanna have a race there? 😉

So far as complex math goes, yeah, it takes a bit of thought on how to do such things as calculate the mode for 20,000 individual "somethings" in a million row sample... but it has been done quite nicely (takes about 13 seconds if I recall correctly) and really doesn't take that much thought if you're skilled in SQL. CLR's simply allow people who aren't that skilled in T-SQL to still get a job done, albeit, a bit slower that what can be done in T-SQL.

Hey Matt... if you see this and remember what the URL is for all that testing we did, would you mind posting it here? I've lost track of it...

--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)

KeiserSoze SSC Rookie Points: 35 More actions · Answer 13

You ridicule the performance tests that people do that show CLR performance is slow and you offer up that very poorly written performance test that uses a While loop instead of a good set based solution as an argument and then call it from embedded SQL? Heh... you better check again. Check out what a "Numbers" or "Tally" table solution will do for that type of solution... it handily beats the CLR solution. Matt Miller and I have run many tests on this very subject and about the only thing that T-SQL can't beat a CLR on is a reasonably complex RegEx replace and then T-SQL still comes close. And he's NOT anti-CLR! How about a million row running total? Wanna have a race there? 😉

Well ok.. I might not have given proper link... my point was not arguing with T-SQL gurus, and as said writing proper T-SQL in most of cases will be better...

But in my defense I can say, tests that I have done before (sorry if I can't put results here as I don't have them any more) proved me that CLR was quicker then T-SQL... but as I have said I used it for math functions..

As I have said usage of T-SQL vs. CLR depends on the usage....

Anyway we could do some testing when i'll have more time... just to say when to use CLR and when to use T-SQL... nothing more

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 14

Thanks for the feedback. Just curious, what type of "math functions" are you talking about?

--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)