|
|
Posted by billmiami2 on 05/27/06 16:18
Hugo,
I think that I tried your expression but it didn't give me exactly the
same result because it eliminated the groupings without any counts,
which was not the case for the CLR version. I wanted to make sure that
the two resultsets were exactly the same, so I chose my more
complicated expression. I did try an expression with a single query
that gave me the same results (including the Null groupings) which was
SELECT ProductID,
CASE WHEN COUNT(PercentPassed) >= 3
THEN CAST(SUM(PercentPassed) - MAX(PercentPassed) -
MIN(PercentPassed) AS float) / CAST(COUNT(PercentPassed) - 2 AS float)
END AS Average
FROM TestAssignment
GROUP BY ProductID
ORDER BY ProductID
but this took more time to run than my expression with the subqueries,
so I abandoned it. Even so, choosing one vs. another does not change
the overall conclusions of the test.
Erland,
By how much were the CLR UDFs faster than the expressions using
built-in functions? Was the difference enough to motivate you to start
writing CLR UDFs in the future, even though you would create
dependencies on the DLLs that you otherwise would not have?
Also, did you find any cases where a CLR aggregate gave you any
benefit?
Bill
Navigation:
[Reply to this message]
|