CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir
|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well --Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

Closing ADO objects

I know it's "best practice" to dispose ado.net objects, but does it make a big difference if just the connection is closed?

In other words, is the code below good enough or should the DataAdapter & Command be explicitly closed?

using (SqlConneciton cn = new SqlConnection(connstr))

{

SqlDataAdapter da = new SqlDataAdapter(sql,cn);

DataSet ds = new DataSet();

da.Fill(ds);

SqlCommand cmd = new SqlCommand(someOtherSql,cn);

cmd.ExecuteNonQuery();

}

Most will be handled by the garbage collector and its all around no big deal. As far as I know, th eone you need to worrie about a lot, is the SqlDataReader: not closing these make for crazy performance overhead.|||

Hi John,

Actually, you don't need to call dispose. The Dispose method is used to release unmanaged resources. Since Close has already been called, the connection will be put back to pool automatically.

IMO, just call Close. That's enough. If you're using "using" statement, it's better. It will call dispose automatically.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Client Side Cursor and CommitTrans

I am trying to trap an error using VB6, ADO 2.8 and SQL Server 2000. Using
a client side cursor, if I pull the ethernet cable just before issuing a
CommitTrans statement, the CommitTrans succeeds anyway. I cannot get the
Errors collection, or WithEvents to report any problem. What now..'I might consider taking this over to the .vb groups to see what they say.
We mostly talk T-SQL specifically around here.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Scott Meloney" <scottm1100@.yahoo.com> wrote in message
news:%230wkWcQpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>I am trying to trap an error using VB6, ADO 2.8 and SQL Server 2000. Using
>a client side cursor, if I pull the ethernet cable just before issuing a
>CommitTrans statement, the CommitTrans succeeds anyway. I cannot get the
>Errors collection, or WithEvents to report any problem. What now..'
>