Performance of CLR

I have stored procedure in database. The procedure have near 2000 lines of
code, it implement state machine. No access to data in the procedure, only
lots of "if else" to find next state. The procedure is called for every
change of some objects, from tens of other SPs. From BOL it looks like it is
good candidate for moving it to C#. I restored database from SQL2k in SQL2k5
.
Next I created C# function, which do nothing except accept same parameters
and return value. The value is constant, no any code in the C# function. Nex
t
I comment all code in TSQL procedure, and add call of CLR procedure in the
SP. Next I measure performance of other SPs which call the SP. From my
results - it become worse, duration increased. Execution path in callers are
remain same. Why it is so? Is cost of CLR function call so high?The cost of the function call to a CLR procedure from inside the SQL Server
environment is indeed going to be higher than the cost of calling a TSQL
function/procedure. But once inside the CLR procedure computation will be
much faster.
The reason for the slowdown is that you are switching from one environment
to another with CLR while in TSQL you are staying in the same environment
across the call boundary.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:DC10BABA-32E1-4FA4-BA69-061B49F93F9E@.microsoft.com...
>I have stored procedure in database. The procedure have near 2000 lines of
> code, it implement state machine. No access to data in the procedure, only
> lots of "if else" to find next state. The procedure is called for every
> change of some objects, from tens of other SPs. From BOL it looks like it
> is
> good candidate for moving it to C#. I restored database from SQL2k in
> SQL2k5.
> Next I created C# function, which do nothing except accept same parameters
> and return value. The value is constant, no any code in the C# function.
> Next
> I comment all code in TSQL procedure, and add call of CLR procedure in the
> SP. Next I measure performance of other SPs which call the SP. From my
> results - it become worse, duration increased. Execution path in callers
> are
> remain same. Why it is so? Is cost of CLR function call so high?|||"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The cost of the function call to a CLR procedure from inside the SQL
> Server environment is indeed going to be higher than the cost of calling a
> TSQL function/procedure. But once inside the CLR procedure computation
> will be much faster.
Actually, that does not appear to be true based on my tests. I just ran
a test comparing a T-SQL UDF against an identical CLR UDF. Both do nothing
more than return an input parameter. The CLR version is approximately 27%
faster over the course of 1,000,000 iterations, on my test server... I'd be
interested in seeing some other results. Don't forget to compile the
assembly in release mode so that you get more optimized code.
The two functions are:
CREATE FUNCTION ReturnMe(@.Input INT)
RETURNS INT
AS
BEGIN
RETURN(@.Input)
END
...and...
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
{
return(Input);
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Yeah that is the same I have seen in our tests. Scalar functions blow TSQL
UDF's out of the water, see SQLCLRPERF project which is part of the demo
files of a recent performance evaluation session, it compares some string
manipulation, results when using the UDF
set statistics time on
go
print 'call CLR UDF'
select dbo.GetMemberLength(StringValue, 2) from dbo.MyStringTable
print 'call T-SQL UDF'
select dbo.GetMemberLength_TSQL(StringValue, 2) from dbo.MyStringTable
set statistics time off
Passing in 19614 rows
call CLR UDF
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 108 ms.
call T-SQL UDF
SQL Server Execution Times:
CPU time = 6650 ms, elapsed time = 15671 ms.
See http://sqldev.net/download/conf/SQL...-USA/SAV303.zip
for the demo files and
http://sqldev.net/download/conf/SQL...0Evaluation.zip
for the presentation
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:unUEMeY6FHA.2176@.TK2MSFTNGP14.phx.gbl...
> "Hal Berenson" <hberenson@.predictableit.com> wrote in message
> news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> Actually, that does not appear to be true based on my tests. I just
> ran a test comparing a T-SQL UDF against an identical CLR UDF. Both do
> nothing more than return an input parameter. The CLR version is
> approximately 27% faster over the course of 1,000,000 iterations, on my
> test server... I'd be interested in seeing some other results. Don't
> forget to compile the assembly in release mode so that you get more
> optimized code.
> The two functions are:
> CREATE FUNCTION ReturnMe(@.Input INT)
> RETURNS INT
> AS
> BEGIN
> RETURN(@.Input)
> END
>
> ...and...
>
> [Microsoft.SqlServer.Server.SqlFunction]
> public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
> {
> return(Input);
> }
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Just to add my experience with that. This week I wrote a performace
measurement function which executed some really easy UDAs.
(Based on a million records, summing up integer values)
-A SUM used in TSQL ran about 500 ms
-A SUM within a UDA ran 800 ms
-A Cursor ran 35000 ms !!!
Jens Suessmeyer.|||After more investigation I found the problem was caused by broken state
machine. Execution path in calling SP was changed. After fix of the problem,
I see ~10% increase in performance.