Posted by Hugo Kornelis on 05/27/06 00:47

On 25 May 2006 18:07:28 -0700, billmiami2@netscape.net wrote:

>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.

Hi Bill,

Thanks for testing this, and for sharing the results. Very enlightening.

Just a quick note -

>--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

I think that this can be simplified to

SELECT ProductID,
CASE WHEN COUNT(*) >= 3
THEN CAST(SUM(PercentPassed) - MAX(PercentPassed) -
MIN(PercentPassed) AS float) / CAST(COUNT(*) - 2 AS float)
END AS Average
FROM TestAssignment
WHERE PercentPassed IS NOT NULL
GROUP BY ProductID
ORDER BY ProductID

A few quick tests show no differences with your version, and approx. 50%
less execution time.

--
Hugo Kornelis, SQL Server MVP

• Next in forum: Re: Trying To find a Match in computed columns
• Prev in forum: Re: error: Cursor not returned from query
• Thread view: Re: SQL Server 2005: CLR functions vs SQL functions

[Reply to this message]