May 18, 2007 at 9:15 am
/* This SQL script is extremely inefficient. This is one (of 15) occurrences of the script. It
is repeated 15 times for "Value = 51" thru "Value = 65"
I verified that the appropriate indexes are in place (eg. MyTABLE.Value is indexed).
I'm looking for help to rewrite this small script WITHOUT the CURSOR !! It uses a UDF
which in turn calls a 2nd UDF.
The script and both UDFs are listed below. Any help is greatly appreciated.
*/
------------------------------------- SCRIPT NEXT
DECLARE @val FLOAT
DECLARE MyCURSOR CURSOR FOR SELECT Perf_YTD FROM MyTABLE WHERE Value = 56
OPEN MyCURSOR
FETCH NEXT FROM MyCURSOR INTO @val
WHILE @@fetch_status = 0
BEGIN
UPDATE MyTABLE SET Perf_YTD_Percentile =
dbo.UDF_LinearInterpolation(@val, Perf_YTDLT, Perf_YTDGE,
dbo.UDF_FloatingPointDivision(countLT-1,countMinus1),
dbo.UDF_FloatingPointDivision(countLT,countMinus1)) * 100 --as percentrank
FROM ( SELECT
SUM(CASE WHEN Perf_YTD < @val THEN 1 ELSE 0 END) as countLT,
COUNT(*)-1 as countMinus1,
MAX(CASE WHEN Perf_YTD < @val THEN Perf_YTD END) as Perf_YTDLT,
MIN(CASE WHEN Perf_YTD >= @val THEN Perf_YTD END) as Perf_YTDGE
FROM MyTABLE
WHERE Value = 56
) AS x1
WHERE CURRENT OF MyCURSOR
-- get next row
FETCH NEXT FROM MyCURSOR INTO @val
END
-- Clean up cursor
CLOSE MyCURSOR
DEALLOCATE MyCURSOR
GO
------------------------------------- 1st UDF
CREATE FUNCTION [dbo].[UDF_LinearInterpolation] (@value float, @low float, @high float, @newlow float, @newhigh float)
RETURNS float AS
BEGIN
RETURN CASE WHEN @value between @low and @high and @newlow <= @newhigh
THEN @newlow+dbo.UDF_FloatingPointDivision ((@value-@low), ( @high-@low))*(@newhigh-@newlow)
WHEN @value = @low and @newlow is not NULL THEN @newlow
WHEN @value = @high and @newhigh is not NULL THEN @newhigh
ELSE NULL
END
END
------------------------------------- 2nd UDF
CREATE FUNCTION [dbo].[UDF_FloatingPointDivision] (@numerator float, @denominator float)
RETURNS float AS
BEGIN
RETURN CASE WHEN @denominator = 0.0 THEN 0.0 ELSE @numerator / @denominator END
END
May 18, 2007 at 9:34 am
Gonna try this:
UPDATE RGPerfExtract SET Perf_YTD_Percentile =
dbo.UDF_LinearInterpolation(Value, Perf_YTDLT, Perf_YTDGE,
dbo.UDF_FloatingPointDivision(countLT-1,countMinus1),
dbo.UDF_FloatingPointDivision(countLT,countMinus1)) * 100 --as percentrank
FROM (
SELECT SUM(CASE WHEN Perf_YTD < Value THEN 1 ELSE 0 END) as countLT,
COUNT(*)-1 as countMinus1,
MAX(CASE WHEN Perf_YTD < Value THEN Perf_YTD END) as Perf_YTDLT,
MIN(CASE WHEN Perf_YTD >= Value THEN Perf_YTD END) as Perf_YTDGE
FROM RGPerfExtract
WHERE Value between 51 AND 65 ) AS x1
May 18, 2007 at 10:03 am
Is Value, Perf_YTD unique?
What is the PRIMARY KEY of the table?
May 18, 2007 at 10:44 am
In the absence of more information, here is an outline of something that may work.
If it does work, getting rid of the functions should speed it up.
CREATE TABLE #Value
(
Value int NOT NULL PRIMARY KEY
,countMinus1 int NOT NULL
)
CREATE TABLE #Temp
(
TID int IDENTITY NOT NULL PRIMARY KEY
,PK int NOT NULL UNIQUE
,Value int NOT NULL
,Perf_YTD float NOT NULL
,Perf_YTDLT float NULL
,Perf_YTDGE float NULL
)
INSERT INTO #Temp(PK, Value, Perf_YTD)
SELECT PK, Value, Perf_YTD
FROM RGPerfExtract
WHERE Value BETWEEN 51 AND 65
ORDER BY Value, Perf_YTD, PK
OPTION (MAXDOP 1)
INSERT INTO #Value
SELECT Value, COUNT(*) - 1
FROM #Temp
GROUP BY Value
UPDATE T
SET Perf_YTDLT = T1.Perf_YTDLT
FROM #Temp T
JOIN #Temp T1
ON T.TID = T1.TID + 1
AND T.Value = T1.Value
UPDATE T
SET Perf_YTDGE = T1.Perf_YTDGE
FROM #Temp T
JOIN #Temp T1
ON T.TID = T1.TID - 1
AND T.Value = T1.Value
UPDATE E
SET Perf_YTD_Percentile =
dbo.UDF_LinearInterpolation(E.Perf_YTD, T.Perf_YTDLT, T.Perf_YTDGE,
dbo.UDF_FloatingPointDivision(T.TID - 2, V.CountMinus1),
dbo.UDF_FloatingPointDivision(T.TID - 1, V.CountMinus1)) * 100 --as percentrank
FROM RGPerfExtract E
JOIN #Temp T
ON E.PK = T.PK
JOIN #Value V
ON T.Value = V.Value
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply