Performance CLR function

I think that the simple answer is that code written as managed code will never be as fast as the
built-in functions that are shipped with the product...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"TimXox" wrote in message

> Hi,
> I created CLR aggregate function MULT which works like Sum, but instead of +
> it does *. I compare the performance of my function with:
> SELECT exp(sum(log(AttributionValue+1)))
> the result is the same , but for the same number of rows my function takes
> almost twice time more.
> Please advice,
> Tim.
 >> Stay informed about: Performance CLR function 

TimXox wrote:
>
> Hi,
> I created CLR aggregate function MULT which works like Sum, but instead of +
> it does *. I compare the performance of my function with:
> SELECT exp(sum(log(AttributionValue+1)))
> the result is the same , but for the same number of rows my function takes
> almost twice time more.
> Please advice,
> Tim.

On what would you like to be advised? You cannot expect the same
performance from a CLR function when compared to a simple native T-SQL
equivalent. If there is a simple T-SQL equivalent, it will always
outperform a similar CLR function because of the overhead that is
involved in calling a CLR function.

--
Gert-Jan
SQL Server MVP
 >> Stay informed about: Performance CLR function 

I cannot agree that SQL - 92 has equvalent to this aggregation function.
The standart SQL has SUM, AVG,MAX,MIN, ect , but not MULTIPLY.
All I do is just M = a1 * a2 * a3 * a4 ..............
Of couse we may work around using native functon, but they are much more
complicated and have some limitations:

M = EXP( LOG(a1) + LOG(a2) + LOG(3) + LOG(a3) +......

Notice that amount of calculations which native functions have to do is
enormous compare to my func. So it looks like for me that CLR is very slow
for this ( PURE CALCULATION) case.
So, what is the point to have CLR. Or let me put another question: If I
have to do some regretional analisys with millions of row should I use pure
T-SQL ? I hoped CLR is good candidate here.
Thanks,
Tim.



"Gert-Jan Strik" wrote:

> TimXox wrote:
> >
> > Hi,
> > I created CLR aggregate function MULT which works like Sum, but instead of +
> > it does *. I compare the performance of my function with:
> > SELECT exp(sum(log(AttributionValue+1)))
> > the result is the same , but for the same number of rows my function takes
> > almost twice time more.
> > Please advice,
> > Tim.
>
> On what would you like to be advised? You cannot expect the same
> performance from a CLR function when compared to a simple native T-SQL
> equivalent. If there is a simple T-SQL equivalent, it will always
> outperform a similar CLR function because of the overhead that is
> involved in calling a CLR function.
>
> --
> Gert-Jan
> SQL Server MVP
>
 >> Stay informed about: Performance CLR function 

>> Or let me put another question: If I have to do some regression Analysis with millions of row should I use pure T-SQL ? I hoped CLR is good candidate here. <<

The right answer is get SAS or another stat package for this kind of
work. They will handle floating point errors, have good functions,
etc. SQL is not a computational language.

CLR will stink and make maintaining the code a nightmare. Gert-Jan is
right on the money with his remark about CLR being a way for non-SQL
programmers to screw up databases in their native language.

As an aside, your PRD() function is wrong. Here is a version of the
aggregate product function in SQL. You will need to have the
logarithm and exponential functions. They are not standards, but they
are very common.

The idea is that there are three special cases - all positive numbers,
one or more zeros, and some negative numbers in the set.

You can find out what your situation is with a quick test on the
sign() of the minimum value in the set.

Within the case where you have negative numbers, there are two sub-
cases: (1) an even number of negatives or (2) an odd number of
negatives. You then need to apply some High School algebra to
determine the sign of the final result.

SELECT CASE MIN (SIGN(nbr))
WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers
WHEN 0 THEN 0.00 -- some zeroes
WHEN -1 -- some negative numbers
THEN (EXP(SUM(LN(ABS(nbr))))
* (CASE WHEN
MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
THEN -1.00 ELSE 1.00 END)
ELSE NULL END AS big_pi
FROM Foobar;

The book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z. A. Melzak
(Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short mathematical
book on the general principle of conjugacy. This is the method of
using a transform and its inverse to reduce the complexity of a
calculation.
 >> Stay informed about: Performance CLR function 

Gentlemen,
Thank you for your input. Here what I read in SQL 2005 docs:

...”Because managed code compiles to native code prior to execution, you can
achieve significant performance increases in some scenarios….. Managed code
is better suited than Transact-SQL for calculations and complicated execution
logic…..Use managed code for CPU-intensive functions and procedures that
feature complex logic…”

Couple months ago I finished a big project (in SQL 2000) with financial
calculations prototyped by a C++ client-server application. I changed
architecture and moved everything (including calculations) to SQL Server. The
performance gain is huge, but code looks ugly, very difficult to read even
simple formulas. I agree T-SQL is not a language for computations.
My intension was to move code to SQL 2005 and re-write calculation functions
in CLR. ( I used formula exp(sum(log(AttributionValue+1))) just to illustrate
the issue – in realty it is a function with logic very close to the one in
Mr.Celko post).
I am kind of disappointed, but still hope I am doing something wrong.
Below is the code I use.
Please advice.
Tim.



[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined, //use clr
serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]

public class MULT : Microsoft.SqlServer.Server.IBinarySerialize
{
private SqlDouble intermediateResult ;

public void Init() { intermediateResult = 1; }

public void Accumulate(SqlDouble value)
{ if (value.IsNull) {return;} intermediateResult *= value; }

public void Merge(MULT other)
{ intermediateResult *= other.intermediateResult;}

public SqlDouble Terminate()
{ SqlDouble output = intermediateResult;
return (output);
}
public void Read(BinaryReader r)
{
if (r == null) throw new ArgumentNullException("r");
intermediateResult = r.ReadDouble();
}
public void Write(BinaryWriter w)
{
if (w == null) throw new ArgumentNullException("w");
w.Write((double)intermediateResult);
}
}


"--CELKO--" wrote:

> >> Or let me put another question: If I have to do some regression Analysis with millions of row should I use pure T-SQL ? I hoped CLR is good candidate here. <<
>
> The right answer is get SAS or another stat package for this kind of
> work. They will handle floating point errors, have good functions,
> etc. SQL is not a computational language.
>
> CLR will stink and make maintaining the code a nightmare. Gert-Jan is
> right on the money with his remark about CLR being a way for non-SQL
> programmers to screw up databases in their native language.
>
> As an aside, your PRD() function is wrong. Here is a version of the
> aggregate product function in SQL. You will need to have the
> logarithm and exponential functions. They are not standards, but they
> are very common.
>
> The idea is that there are three special cases - all positive numbers,
> one or more zeros, and some negative numbers in the set.
>
> You can find out what your situation is with a quick test on the
> sign() of the minimum value in the set.
>
> Within the case where you have negative numbers, there are two sub-
> cases: (1) an even number of negatives or (2) an odd number of
> negatives. You then need to apply some High School algebra to
> determine the sign of the final result.
>
> SELECT CASE MIN (SIGN(nbr))
> WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers
> WHEN 0 THEN 0.00 -- some zeroes
> WHEN -1 -- some negative numbers
> THEN (EXP(SUM(LN(ABS(nbr))))
> * (CASE WHEN
> MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
> THEN -1.00 ELSE 1.00 END)
> ELSE NULL END AS big_pi
> FROM Foobar;
>
> The book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z. A. Melzak
> (Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short mathematical
> book on the general principle of conjugacy. This is the method of
> using a transform and its inverse to reduce the complexity of a
> calculation.
>
>
>
>
 >> Stay informed about: Performance CLR function 

Tim,

IMO, Microsoft added CLR support primarily to make it easier for
application developers with inadequate SQL skills to leverage their
knowledge about their CLR language.

The mere fact that SQL Server supports CLR functions does not mean that
you should use them. The CLR is good for procedural handling. If you
have to do complex manipulation, it can be much easier to write the
algorithm in C# or whatever, because those languages are very rich in
their syntax and supporting functions.

If you have to process large data sets, then the row by row processing
of scalar UDFs might bite you when it comes to performance, and you will
also notice the overhead when using CLR for the function(s).

So I guess it depends. If you want to do "regretional analysis with
millions of rows", you either write it in a language that is easiest for
you (in CLR) and accept that the performance will be accordingly, or you
take the (probably more complex) set oriented approach.

--
Gert-Jan
SQL Server MVP


TimXox wrote:
>
> I cannot agree that SQL - 92 has equvalent to this aggregation function.
> The standart SQL has SUM, AVG,MAX,MIN, ect , but not MULTIPLY.
> All I do is just M = a1 * a2 * a3 * a4 ..............
> Of couse we may work around using native functon, but they are much more
> complicated and have some limitations:
>
> M = EXP( LOG(a1) + LOG(a2) + LOG(3) + LOG(a3) +......
>
> Notice that amount of calculations which native functions have to do is
> enormous compare to my func. So it looks like for me that CLR is very slow
> for this ( PURE CALCULATION) case.
> So, what is the point to have CLR. Or let me put another question: If I
> have to do some regretional analisys with millions of row should I use pure
> T-SQL ? I hoped CLR is good candidate here.
> Thanks,
> Tim.
>
> "Gert-Jan Strik" wrote:
>
> > TimXox wrote:
> > >
> > > Hi,
> > > I created CLR aggregate function MULT which works like Sum, but instead of +
> > > it does *. I compare the performance of my function with:
> > > SELECT exp(sum(log(AttributionValue+1)))
> > > the result is the same , but for the same number of rows my function takes
> > > almost twice time more.
> > > Please advice,
> > > Tim.
> >
> > On what would you like to be advised? You cannot expect the same
> > performance from a CLR function when compared to a simple native T-SQL
> > equivalent. If there is a simple T-SQL equivalent, it will always
> > outperform a similar CLR function because of the overhead that is
> > involved in calling a CLR function.
> >
> > --
> > Gert-Jan
> > SQL Server MVP
> >
 >> Stay informed about: Performance CLR function