|
|
Posted by billmiami2 on 05/26/06 04:07
I was playing around with the new SQL 2005 CLR functionality and
remembered this discussion that I had with Erland Sommarskog concerning
performance of scalar UDFs some time ago (See "Calling sp_oa* in
function" in this newsgroup). In that discussion, Erland made the
following comment about UDFs in SQL 2005:
>>The good news is that in SQL 2005, Microsoft has addressed several of
these issues, and the cost of a UDF is not as severe there. In fact for
a complex expression, a UDF in written a CLR language may be faster
than
the corresponding expression using built-in T-SQL functions.<<
I thought the I would put this to the test using some of the same SQL
as before, but adding a simple scalar CLR UDF into the mix. The test
involved querying a simple table with about 300,000 rows. The
scenarios are as follows:
(A) Use a simple CASE function to calculate a column
(B) Use a simple CASE function to calculate a column and as a criterion
in the WHERE clause
(C) Use a scalar UDF to calculate a column
(D) Use a scalar UDF to calculate a column and as a criterion in the
WHERE clause
(E) Use a scalar CLR UDF to calculate a column
(F) Use a scalar CLR UDF to calculate a column and as a criterion in
the WHERE clause
A sample of the results is as follows (time in milliseconds):
(295310 row(s) affected)
A: 1563
(150003 row(s) affected)
B: 906
(295310 row(s) affected)
C: 2703
(150003 row(s) affected)
D: 2533
(295310 row(s) affected)
E: 2060
(150003 row(s) affected)
F: 2190
The scalar CLR UDF function was significantly faster than the classic
scalar UDF, even for this very simple function. Perhaps a more complex
function would have shown even a greater difference. Based on this, I
must conclude that Erland was right. Of course, it's still faster to
stick with basic built-in functions like CASE.
In another test, I decided to run some queries to compare built-in
aggregates vs. a couple of simple CLR aggregates as follows:
(G) Calculate averages by group using the built-in AVG aggregate
(H) Calculate averages by group using a CLR aggregate that similates
the built-in AVG aggregate
(I) Calculate a "trimmed" average by group (average excluding highest
and lowest values) using built-in aggregates
(J) Calculate a "trimmed" average by group using a CLR aggregate
specially designed for this purpose
A sample of the results is as follows (time in milliseconds):
(59 row(s) affected)
G: 313
(59 row(s) affected)
H: 890
(59 row(s) affected)
I: 216
(59 row(s) affected)
J: 846
It seems that the CLR aggregates came with a significant performance
penalty over the built-in aggregates. Perhaps they would pay off if I
were attempting a very complex type of aggregation. However, at this
point I'm going to shy away from using these unless I can't find a way
to do the calculation with standard SQL.
In a way, I'm happy that basic SQL still seems to be the fastest way to
get things done. With the addition of the new CLR functionality, I
suspect that MS may be giving us developers enough rope to comfortably
hang ourselves if we're not careful.
Bill E.
Hollywood, FL
-----------------------------------------------------------------------
-- table TestAssignment, about 300,000 rows
CREATE TABLE [dbo].[TestAssignment](
[TestAssignmentID] [int] NOT NULL,
[ProductID] [int] NULL,
[PercentPassed] [int] NULL,
CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED
(
[TestAssignmentID] ASC
)
--Scalar UDF in SQL
CREATE FUNCTION [dbo].[fnIsEven]
(
@intValue int
)
RETURNS bit
AS
BEGIN
Declare @bitReturnValue bit
If @intValue % 2 = 0
Set @bitReturnValue=1
Else
Set @bitReturnValue=0
RETURN @bitReturnValue
END
--Scalar CLR UDF
/*
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true,IsPrecise=true)]
public static SqlBoolean IsEven(SqlInt32 value)
{
if(value % 2 == 0)
{
return true;
}
else
{
return false;
}
}
};
*/
--Test #1
--Scenario A - Query with calculated column--
SELECT TestAssignmentID,
CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END AS
CalcColumn
FROM TestAssignment
--Scenario B - Query with calculated column as criterion--
SELECT TestAssignmentID,
CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END AS
CalcColumn
FROM TestAssignment
WHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1
--Scenario C - Query using scalar UDF--
SELECT TestAssignmentID,
dbo.fnIsEven(TestAssignmentID) AS CalcColumn
FROM TestAssignment
--Scenario D - Query using scalar UDF as crierion--
SELECT TestAssignmentID,
dbo.fnIsEven(TestAssignmentID) AS CalcColumn
FROM TestAssignment
WHERE dbo.fnIsEven(TestAssignmentID)=1
--Scenario E - Query using CLR scalar UDF--
SELECT TestAssignmentID,
dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumn
FROM TestAssignment
--Scenario F - Query using CLR scalar UDF as crierion--
SELECT TestAssignmentID,
dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumn
FROM TestAssignment
WHERE dbo.fnIsEven(TestAssignmentID)=1
--CLR Aggregate functions
/*
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct Avg
{
public void Init()
{
this.numValues = 0;
this.totalValue = 0;
}
public void Accumulate(SqlDouble Value)
{
if (!Value.IsNull)
{
this.numValues++;
this.totalValue += Value;
}
}
public void Merge(Avg Group)
{
if (Group.numValues > 0)
{
this.numValues += Group.numValues;
this.totalValue += Group.totalValue;
}
}
public SqlDouble Terminate()
{
if (numValues == 0)
{
return SqlDouble.Null;
}
else
{
return (this.totalValue / this.numValues);
}
}
// private accumulators
private int numValues;
private SqlDouble totalValue;
}
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct TrimmedAvg
{
public void Init()
{
this.numValues = 0;
this.totalValue = 0;
this.minValue = SqlDouble.MaxValue;
this.maxValue = SqlDouble.MinValue;
}
public void Accumulate(SqlDouble Value)
{
if (!Value.IsNull)
{
this.numValues++;
this.totalValue += Value;
if (Value < this.minValue)
this.minValue = Value;
if (Value > this.maxValue)
this.maxValue = Value;
}
}
public void Merge(TrimmedAvg Group)
{
if (Group.numValues > 0)
{
this.numValues += Group.numValues;
this.totalValue += Group.totalValue;
if (Group.minValue < this.minValue)
this.minValue = Group.minValue;
if (Group.maxValue > this.maxValue)
this.maxValue = Group.maxValue;
}
}
public SqlDouble Terminate()
{
if (this.numValues < 3)
return SqlDouble.Null;
else
{
this.numValues -= 2;
this.totalValue -= this.minValue;
this.totalValue -= this.maxValue;
return (this.totalValue / this.numValues);
}
}
// private accumulators
private int numValues;
private SqlDouble totalValue;
private SqlDouble minValue;
private SqlDouble maxValue;
}
*/
--Test #2
--Scenario G - Average Query using built-in aggregate--
SELECT ProductID, Avg(Cast(PercentPassed AS float))
FROM TestAssignment
GROUP BY ProductID
ORDER BY ProductID
--Scenario H - Average Query using CLR aggregate--
SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS Average
FROM TestAssignment
GROUP BY ProductID
ORDER BY ProductID
--Scenario I - Trimmed Average Query using built in aggregates/set
operations--
SELECT A.ProductID,
Case
When B.CountValues<3 Then Null
Else Cast(A.Total-B.MaxValue-B.MinValue AS
float)/Cast(B.CountValues-2 As float)
End AS Average
FROM
(SELECT ProductID, Sum(PercentPassed) AS Total
FROM TestAssignment
GROUP BY ProductID) A
LEFT JOIN
(SELECT ProductID,
Max(PercentPassed) AS MaxValue,
Min(PercentPassed) AS MinValue,
Count(*) AS CountValues
FROM TestAssignment
WHERE PercentPassed Is Not Null
GROUP BY ProductID) B
ON A.ProductID=B.ProductID
ORDER BY A.ProductID
--Scenario J - Trimmed Average Query using CLR aggregate--
SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) AS
Average
FROM TestAssignment
GROUP BY ProductID
ORDER BY ProductID
Navigation:
[Reply to this message]
|