May 15, 2008 at 4:16 am
Can any one of you give me a sample to convert the SQL table-valued function to CLR table-valued function using C#.Net
May 26, 2008 at 1:19 am
[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.
May 26, 2008 at 9:14 am
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
Change is inevitable... Change for the better is not.
May 26, 2008 at 11:48 pm
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
May 27, 2008 at 6:09 am
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
Change is inevitable... Change for the better is not.
May 27, 2008 at 7:11 am
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
May 27, 2008 at 10:35 am
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]
May 27, 2008 at 10:50 am
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?
May 28, 2008 at 3:24 am
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
May 28, 2008 at 5:59 am
What kinds of functions?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2008 at 7:07 am
We used only Table-valued and Scalar-valued functions for our testing. I can't able to tell more than this, I think. SORRY.
June 11, 2008 at 2:46 am
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
😉
June 11, 2008 at 5:14 am
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
Change is inevitable... Change for the better is not.
June 11, 2008 at 5:58 am
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
June 11, 2008 at 7:23 am
Thanks for the feedback. Just curious, what type of "math functions" are you talking about?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply