Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts

Monday, March 19, 2012

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

Sunday, March 11, 2012

CLR test script SELECT returns no row data

Hi,

The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"

I get the result as:
Name Address
- -
No rows affected.
(1 row(s) returned)

But not the actual row is displayed whereas I would expect to see something like:

Name Address
- -
John Doe
No rows affected.
(1 row(s) returned)

I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.

Why no results in first case?

Thanks,
Bahadir
You maybe still have the transaction open and uncommitted, thats why you don′t see the actual row.

HTH; Jens SUessmeyer.

http://www.sqlserver2005.de

CLR Stored Procedures & VS2005 ASP.NET designer support?

I have successfully created CLR stored procures using c# and SQL Server 2005. Now I want to use the CLR stored procedures with some ASP.NET controls. I can add the CLR stored procedure to the dataset designer and have it add a new table adapter but it does not list the columns in the designer view. This forces me to code the column references manually into the controls (tried: DropDownList, GridView).

I can deal with adding the columns manually for controls using a CLR stored procedure, but I want to make sure I was not missing something, am I?

BTW: TSQL stored procedures work as expected (show columns in the dataset designer)

thanks!

-pieter

Hi,

as metadata can be defined on the fly in a stored procedure by building your rowset one by one and then sending it through the pipe, I don′t really know if there is a way to give the designer a hint via some properties or anything else to sniff into the CLR resultset.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks. Your reply does help.

I just wanted to be sure it was not something I was doing incorrectly. The CLR stored procedures do indeed have a great deal of power available to them.

Let us hope the VS team can find some way of bringing the two closer together in the future.

-pieter

CLR Stored Procedure in Reporting Services

Hi

I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.

After Creating the dll I have registered the dll like that

CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE


After registering I have deployed the dll :


CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............

AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO



The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.



All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?

Please please ASAP.

Thanks
Tareqe

Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.

="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value

|||

I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.

wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

ALTERPROCEDURE [dbo].[tsql_my_sp_name]

-- Add the parameters for the stored procedure here

@.dbname nvarchar(4000),

@.varTable nvarcar(4000),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

EXEC my_sp_name @.dbname, @.varTable

END

CLR Stored Procedure in Reporting Services

Hi

I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.

After Creating the dll I have registered the dll like that

CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE


After registering I have deployed the dll :


CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............

AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO



The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.



All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?

Please please ASAP.

Thanks
Tareqe

Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.

="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value

|||

I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.

wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

ALTERPROCEDURE [dbo].[tsql_my_sp_name]

-- Add the parameters for the stored procedure here

@.dbname nvarchar(4000),

@.varTable nvarcar(4000),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

EXEC my_sp_name @.dbname, @.varTable

END

CLR SP Execution

Hello,
I am currently working on an application which calls some CLR stored
procedures in SQL Server 2005. The first time one of the CLR stored
procedures (it doesn't matter which one) is called it takes a lot longer to
execute. After the first CLR stored procedure has been executed the CLR
stored procedures all execute in a reasonable amount of time.
I have been unable to find any articles explaining the process that occurs
when a CLR stored procedure is called. I am currently assuming the initial
delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
Server or only when needed?
If anyone can point me in the direction of an article to aid my
understanding or can explain the process I would be grateful.
Thanks in advance.
Alistair HarrisonHow long of a delay do you experience? Do you get the delay with a trivial
proc like the one below? I get a sub-second response when I execute this
after a fresh SQL Server restart.
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyProc()
{
Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
}
};
I'm no expert on SQL CLR internals but I can't think of any overhead beyond
the usual object-specific overhead.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
> Hello,
> I am currently working on an application which calls some CLR stored
> procedures in SQL Server 2005. The first time one of the CLR stored
> procedures (it doesn't matter which one) is called it takes a lot longer
> to
> execute. After the first CLR stored procedure has been executed the CLR
> stored procedures all execute in a reasonable amount of time.
> I have been unable to find any articles explaining the process that occurs
> when a CLR stored procedure is called. I am currently assuming the initial
> delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
> Server or only when needed?
> If anyone can point me in the direction of an article to aid my
> understanding or can explain the process I would be grateful.
> Thanks in advance.
> Alistair Harrison|||Dan,
Thanks for your response.
The following are examples of execution times recorded by the Client
Statistics when the query is executed:
Initial execution of my original sp: 1600 to 1700 ms
Subsequent execution: <100 ms
Initial execution of your test sp: 700 to 800 ms
Subsequent execution: <50 ms
Another point I noticed was that if the test sp is executed first followed
by my original sp following a restart the original sp takes around 1200 ms to
execute. This is less than the 1600 to 1700 ms it seems to take when
executing the sp first but is still much greater than the subsequent
execution time.
It might be worth mentioning that my original clr sp executes a couple of
tsql sps using a context connection and then returns some xml as an output
parameter.
Thanks,
Alistair
"Dan Guzman" wrote:
> How long of a delay do you experience? Do you get the delay with a trivial
> proc like the one below? I get a sub-second response when I execute this
> after a fresh SQL Server restart.
> public partial class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void MyProc()
> {
> Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
> }
> };
> I'm no expert on SQL CLR internals but I can't think of any overhead beyond
> the usual object-specific overhead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
> > Hello,
> >
> > I am currently working on an application which calls some CLR stored
> > procedures in SQL Server 2005. The first time one of the CLR stored
> > procedures (it doesn't matter which one) is called it takes a lot longer
> > to
> > execute. After the first CLR stored procedure has been executed the CLR
> > stored procedures all execute in a reasonable amount of time.
> >
> > I have been unable to find any articles explaining the process that occurs
> > when a CLR stored procedure is called. I am currently assuming the initial
> > delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
> > Server or only when needed?
> >
> > If anyone can point me in the direction of an article to aid my
> > understanding or can explain the process I would be grateful.
> >
> > Thanks in advance.
> >
> > Alistair Harrison
>
>|||It's likely that subsequent executions are faster simply a result of data
caching. I'd expect execution times to average about 1200ms if you run DBCC
DROPCLEANBUFFERS before each execution. For performance testing, I usually
execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test.
I don't think the sub-second delay the first time the proc is run is
anything to be concerned about.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
message news:6A2A987D-E008-440F-867E-0DE4D6A3AA85@.microsoft.com...
> Dan,
> Thanks for your response.
> The following are examples of execution times recorded by the Client
> Statistics when the query is executed:
> Initial execution of my original sp: 1600 to 1700 ms
> Subsequent execution: <100 ms
> Initial execution of your test sp: 700 to 800 ms
> Subsequent execution: <50 ms
> Another point I noticed was that if the test sp is executed first followed
> by my original sp following a restart the original sp takes around 1200 ms
> to
> execute. This is less than the 1600 to 1700 ms it seems to take when
> executing the sp first but is still much greater than the subsequent
> execution time.
> It might be worth mentioning that my original clr sp executes a couple of
> tsql sps using a context connection and then returns some xml as an output
> parameter.
> Thanks,
> Alistair
>
> "Dan Guzman" wrote:
>> How long of a delay do you experience? Do you get the delay with a
>> trivial
>> proc like the one below? I get a sub-second response when I execute this
>> after a fresh SQL Server restart.
>> public partial class StoredProcedures
>> {
>> [Microsoft.SqlServer.Server.SqlProcedure]
>> public static void MyProc()
>> {
>> Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
>> }
>> };
>> I'm no expert on SQL CLR internals but I can't think of any overhead
>> beyond
>> the usual object-specific overhead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
>> message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
>> > Hello,
>> >
>> > I am currently working on an application which calls some CLR stored
>> > procedures in SQL Server 2005. The first time one of the CLR stored
>> > procedures (it doesn't matter which one) is called it takes a lot
>> > longer
>> > to
>> > execute. After the first CLR stored procedure has been executed the CLR
>> > stored procedures all execute in a reasonable amount of time.
>> >
>> > I have been unable to find any articles explaining the process that
>> > occurs
>> > when a CLR stored procedure is called. I am currently assuming the
>> > initial
>> > delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
>> > Server or only when needed?
>> >
>> > If anyone can point me in the direction of an article to aid my
>> > understanding or can explain the process I would be grateful.
>> >
>> > Thanks in advance.
>> >
>> > Alistair Harrison
>>|||Dan
Thanks again for your response.
I have retested the two stored procedures running DROPCLEANBUFFERS and
FREEPROCCACHE before each execution. The execution times increase slightly
but are still consistently below 300ms.
I suppose as the longer execution only seems to occur following a restart it
is not too much of a worry just intriguing as to why it seems to occur.
Alistair
"Dan Guzman" wrote:
> It's likely that subsequent executions are faster simply a result of data
> caching. I'd expect execution times to average about 1200ms if you run DBCC
> DROPCLEANBUFFERS before each execution. For performance testing, I usually
> execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test.
> I don't think the sub-second delay the first time the proc is run is
> anything to be concerned about.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> message news:6A2A987D-E008-440F-867E-0DE4D6A3AA85@.microsoft.com...
> > Dan,
> >
> > Thanks for your response.
> >
> > The following are examples of execution times recorded by the Client
> > Statistics when the query is executed:
> >
> > Initial execution of my original sp: 1600 to 1700 ms
> > Subsequent execution: <100 ms
> >
> > Initial execution of your test sp: 700 to 800 ms
> > Subsequent execution: <50 ms
> >
> > Another point I noticed was that if the test sp is executed first followed
> > by my original sp following a restart the original sp takes around 1200 ms
> > to
> > execute. This is less than the 1600 to 1700 ms it seems to take when
> > executing the sp first but is still much greater than the subsequent
> > execution time.
> >
> > It might be worth mentioning that my original clr sp executes a couple of
> > tsql sps using a context connection and then returns some xml as an output
> > parameter.
> >
> > Thanks,
> >
> > Alistair
> >
> >
> > "Dan Guzman" wrote:
> >
> >> How long of a delay do you experience? Do you get the delay with a
> >> trivial
> >> proc like the one below? I get a sub-second response when I execute this
> >> after a fresh SQL Server restart.
> >>
> >> public partial class StoredProcedures
> >> {
> >> [Microsoft.SqlServer.Server.SqlProcedure]
> >> public static void MyProc()
> >> {
> >> Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
> >> }
> >> };
> >>
> >> I'm no expert on SQL CLR internals but I can't think of any overhead
> >> beyond
> >> the usual object-specific overhead.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> >> message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
> >> > Hello,
> >> >
> >> > I am currently working on an application which calls some CLR stored
> >> > procedures in SQL Server 2005. The first time one of the CLR stored
> >> > procedures (it doesn't matter which one) is called it takes a lot
> >> > longer
> >> > to
> >> > execute. After the first CLR stored procedure has been executed the CLR
> >> > stored procedures all execute in a reasonable amount of time.
> >> >
> >> > I have been unable to find any articles explaining the process that
> >> > occurs
> >> > when a CLR stored procedure is called. I am currently assuming the
> >> > initial
> >> > delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
> >> > Server or only when needed?
> >> >
> >> > If anyone can point me in the direction of an article to aid my
> >> > understanding or can explain the process I would be grateful.
> >> >
> >> > Thanks in advance.
> >> >
> >> > Alistair Harrison
> >>
> >>
> >>
>
>

CLR procedures for SQL Express with VB Express

Hello,

Does anyone know if is it possible to create CLR procedures for SQL Express with VB Express?

Regards,

hi,

AFAIK, yes, it is, you only have to deploy them "by hand"..

regards

|||

Hi,

Do you have details how to do that? I'm new to SQL 2005 and I can only find information about creating CLR procedures pointing to Visual Studio Database Project, which is not available on the Express version.

Thanks

|||

hi,

Gustavo Bilinski wrote:

Do you have details how to do that? I'm new to SQL 2005 and I can only find information about creating CLR procedures pointing to Visual Studio Database Project, which is not available on the Express version.

Thanks

yes, only VS professional or higher provides the SQL CLR template you can start from, but even Express editions should be able to write them... of course you should implement the skeleton your self as, again, only Professional or higher editions provide it.. you have to imports specific Namespaces, and declare specific attributes for class members and methods.. but when you're finished, the reuslt is actually the same..

regards

CLR procedures for SQL Express with VB Express

Hello,

Does anyone know if is it possible to create CLR procedures for SQL Express with VB Express?

Regards,

hi,

AFAIK, yes, it is, you only have to deploy them "by hand"..

regards

|||

Hi,

Do you have details how to do that? I'm new to SQL 2005 and I can only find information about creating CLR procedures pointing to Visual Studio Database Project, which is not available on the Express version.

Thanks

|||

hi,

Gustavo Bilinski wrote:

Do you have details how to do that? I'm new to SQL 2005 and I can only find information about creating CLR procedures pointing to Visual Studio Database Project, which is not available on the Express version.

Thanks

yes, only VS professional or higher provides the SQL CLR template you can start from, but even Express editions should be able to write them... of course you should implement the skeleton your self as, again, only Professional or higher editions provide it.. you have to imports specific Namespaces, and declare specific attributes for class members and methods.. but when you're finished, the reuslt is actually the same..

regards

Thursday, March 8, 2012

CLR performance

Does CLR suffer from the same serialization performance overhead that
extended stored procedures do? Which is likely to execute faster? Thanks."Neil W" <neilw@.netlib.com> wrote in message
news:O3OeWyhmGHA.3880@.TK2MSFTNGP02.phx.gbl...
> Does CLR suffer from the same serialization performance overhead that
> extended stored procedures do? Which is likely to execute faster?
> Thanks.
What do you mean by "serialization performance overhead"?
David

CLR Deployment

I'm attempting to deploy CLR Stored Procedures from Visual Studio 2005.

I have a VS Database project with stored procedures for all of the tables in our database. (I would really like to keep everything together in 1 DLL), everything seems fine except...

I keep receiving the following error when attempting to deploy to SQL Server:

Error 1 Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.

After some experimentation I have determined that its because the DLL is too
large: 1.8 mb. (I also have a larger one).

I have looked through various documentation sources but have found none
concerning this problem other than specifing something aboug a 100ms
execution timeout (not sure if this is the same).

Deployment works if I trim down the size of the DLL (i.e., remove procedures from the solution)

Is there anyway to deploy large DLLs to SQL Server without having to break
them up? If so, where is the setting to allow a longer timeout period for
deployment?

You could create script manualy. Just use CREATE ASSEMBLY/CREATE PROCEDURE and other statements.|||

Thanks for the reply.

Yes I know I could do that, but it is so much easier to use VS.

Also the major time consuming problem is the number of tables and procedures.

Heres the scenario.

We have 2 databases, 1 flat, 1 relational. (Same problem is occuring on both).

The flat database has been imported from FoxPro 2.6 DBF's for conversion into the relational to supply historic data to an application upgrade. Needless to say there are a large number of tables. I have utilized ApexSQL to generate CLR Stored Procedues for each table. (i.e., INSERT,UPDATE,DELETE,SELECT, and so on...)

VS Compiles the source with no errors, it is just during deployment to SQL Server. As a work around for now I have split the .CS files into somewhat logical groups and created a seperate VS solution for each, all of these deploy with no problems.

For documentation and maintenance I would rather keep everything together in one VS Solution for each Database.

Again thanks for your reply.

Glenn

CLR Deployment

I'm attempting to deploy CLR Stored Procedures from Visual Studio 2005.

I have a VS Database project with stored procedures for all of the tables in our database. (I would really like to keep everything together in 1 DLL), everything seems fine except...

I keep receiving the following error when attempting to deploy to SQL Server:

Error 1 Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.

After some experimentation I have determined that its because the DLL is too
large: 1.8 mb. (I also have a larger one).

I have looked through various documentation sources but have found none
concerning this problem other than specifing something aboug a 100ms
execution timeout (not sure if this is the same).

Deployment works if I trim down the size of the DLL (i.e., remove procedures from the solution)

Is there anyway to deploy large DLLs to SQL Server without having to break
them up? If so, where is the setting to allow a longer timeout period for
deployment?

You could create script manualy. Just use CREATE ASSEMBLY/CREATE PROCEDURE and other statements.|||

Thanks for the reply.

Yes I know I could do that, but it is so much easier to use VS.

Also the major time consuming problem is the number of tables and procedures.

Heres the scenario.

We have 2 databases, 1 flat, 1 relational. (Same problem is occuring on both).

The flat database has been imported from FoxPro 2.6 DBF's for conversion into the relational to supply historic data to an application upgrade. Needless to say there are a large number of tables. I have utilized ApexSQL to generate CLR Stored Procedues for each table. (i.e., INSERT,UPDATE,DELETE,SELECT, and so on...)

VS Compiles the source with no errors, it is just during deployment to SQL Server. As a work around for now I have split the .CS files into somewhat logical groups and created a seperate VS solution for each, all of these deploy with no problems.

For documentation and maintenance I would rather keep everything together in one VS Solution for each Database.

Again thanks for your reply.

Glenn

Wednesday, March 7, 2012

CLR Assembly redeploy problem.

I have created a C# library containing CLR stored procedures and user-
define functions using Visual Studio 2005, and I have used Visual
Studio to deploy the assembly to a SQL Server 2005 instance
successfully, of course these sps and udfs are used by other T-SQL sps
and udfs. Now the assembly has a new version, and I want to use it to
replace the original one, but when I use Visual Studio 2005 to deploy
the new assembly, the following error occurs:

Error1Cannot drop the function 'TranslationStringLike', because it
does not exist or you do not have permission.
Cannot drop the function 'TranslateEngString', because it does not
exist or you do not have permission.
Cannot drop the function 'TranslateEngStringReverse', because it does
not exist or you do not have permission.
DROP ASSEMBLY failed because 'FirmBankCLR' is referenced by object
'TranslationStringLike'.FirmBankCLR

That is the assembly is dependent by other objects, it can't be
dropped until it is not dependent by other objects. How can I resolve
this problem using Visual Studio 2005 or something else?Amber (guxiaobo1982@.gmail.com) writes:

Quote:

Originally Posted by

I have created a C# library containing CLR stored procedures and user-
define functions using Visual Studio 2005, and I have used Visual
Studio to deploy the assembly to a SQL Server 2005 instance
successfully, of course these sps and udfs are used by other T-SQL sps
and udfs. Now the assembly has a new version, and I want to use it to
replace the original one, but when I use Visual Studio 2005 to deploy
the new assembly, the following error occurs:
>
Error 1 Cannot drop the function 'TranslationStringLike', because
it does not exist or you do not have permission. Cannot drop the
function 'TranslateEngString', because it does not exist or you do not
have permission. Cannot drop the function 'TranslateEngStringReverse',
because it does not exist or you do not have permission. DROP ASSEMBLY
failed because 'FirmBankCLR' is referenced by object
'TranslationStringLike'. FirmBankCLR
>
That is the assembly is dependent by other objects, it can't be
dropped until it is not dependent by other objects. How can I resolve
this problem using Visual Studio 2005 or something else?


As long as the interface of the assembly does not change, you can just
do ALTER ASSEMBLY. Either specify directly, which is simple if the DLL
is accessible from SQL Server. Or specify the DLL as a hexstring directly
in the ALTER ASSEMBLY statement.

If the interface has changed, you will need to drop all functions
created from it, as well as dependent assemblies.

I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
of Visual Studio. I don't use Visual Studio to work with assemblies
but stick to the command line. And all impression I get is that
using Visual Studio just makes things harder.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
of Visual Studio. I don't use Visual Studio to work with assemblies
but stick to the command line. And all impression I get is that
using Visual Studio just makes things harder.


It seems Microsoft hasn't done the job well, but if we have a lot of
objects in the assembly, the manual process of dropping and creating
them is horrible.|||Amber (guxiaobo1982@.gmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
>of Visual Studio. I don't use Visual Studio to work with assemblies
>but stick to the command line. And all impression I get is that
>using Visual Studio just makes things harder.


>
It seems Microsoft hasn't done the job well, but if we have a lot of
objects in the assembly, the manual process of dropping and creating
them is horrible.


Yes, it's horrible if you have to do it each time you change the
implementation of some single method, and it's amazing that VS cannot
handle this situation.

If you change the interface, it's still a lot of work of course of dropping
and recreating objects. But it is or more less inevitable.

I don't know what's in your assemblies, but I woudl suggest that if you
have a suite of functions and procedures that are independent of each other,
that it's best to have a separate assembly for each function/procedure. Of
course, if they use a lot of common code, you still need to put that common
code in a single assembly in which case you are back to the same situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx