Scalar Function Performance issue.
-
I have a UDF that gets called for many rows, and columns when outputing data to text files.
When the code of the UDF is executed "inline", I get "good" performance.
Calling the UDF that does the same work is much slower....
The UDF:
Create Function fn_LPadZeroInt
(@Num Int,
@Length int)
Returns VarChar(20)
-- Returns passed integer value left-padded with zeros for length of passed @Length
--ie passed value of 123,6 becomes "000123"
-- Print dbo.fn_LPadZeroInt(-123, 6)
Begin
return Case When @Num < 0
Then right('-00000000000000000000' + convert(varchar(20), -@Num), @Length)
Else right('00000000000000000000' + convert(varchar(20), @Num), @Length)
end
End
Sample Test Code (Offending line commented):
Declare @Start DateTime
Select @Start = GetDate()
Select Count(*), Min(Number), Max(Number)
,Max(Case When Number < 0
Then right('-00000000000000000000' + convert(varchar(20), -Number), 5)
Else right('000000000000000000000' + convert(varchar(20), Number), 6)
end) AS LocalFast
--,Max(Dbo.fn_LPadZeroInt(Number, 6)) as FromFunc
from (
Select HB.Number * 256 + LB.Number as Number
From Master.dbo.spt_Values HB
Cross Join Master.dbo.spt_Values LB
Where HB.Type = 'P' and LB.Type = 'P'
and HB.Number Between 0 and 256
) NumX65536
Print Convert(Varchar(10), DateDiff(ms, @Start, GetDate())) + 'ms'
Any ideas to speed things up?
I'd like to keep using a UDF, to keep the other code clean.
Tnx.
Once you understand the BITs, all the pieces come together
-
Or a built-in SQL function that does the same kind of "padding".
+1 = "00001"
-1 = "-0001"
Once you understand the BITs, all the pieces come together -
I have had exactly the same performance problems with Scalar functions.
I suspect that if you run Profiler when running the function, you will see Profiler flooded with what appears to be Stored Procedure calls (one for each row).
-
Thanks for the simpathy....
I use the function upto 100 times / row. 🙁
Output is fixed length text data.
The actual TSQL gets generated via SPs, but having the UDF call makes debugging alot easier, and if I had to "inline" the code would get really long....
Once you understand the BITs, all the pieces come together
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply