Improving XML User Defined Function Performance
Doing some code review, I came across a piece of code which giving
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
The likely cause was someone entering a ‘n/a’ in the field (the curse of XML coming from slack systems).
Create FUNCTION [dbo].[GetSellingPrice]
(
@Data xml
)
RETURNS decimal(20,2)
WITH SCHEMABINDING
AS
BEGIN
declare @Return decimal(20,2)
select @Return =
CASE
WHEN @Data.value('(//Property/@SellingPrice)[1]', 'nvarchar(20)') IS NULL THEN 0
ELSE @Data.value('(//Property/@SellingPrice)[1]', 'decimal(20,2)')
END
RETURN @Return
END
Even with good data, this code is not ideal for several reasons:
- Multiple calls to the .value function to get the same data
- Using nvarchar when varchar is sufficient (after all, it’s being cast to a number!)
- Searching the entire XML every call
My proposed alternative TSQL is below which addresses these issues.
CREATE FUNCTION [dbo].[GetSellingPrice2]
(
@Data xml
)
RETURNS decimal(20,2)
WITH SCHEMABINDING
AS
BEGIN
declare @Return decimal(20,2)
declare @text varchar(20)
Set @text=@Data.value('(Property/@SellingPrice)[1]', 'varchar(20)')
if @Text is null OR Len(@Text) < 4
SET @Return=0
ELSE
Set @Return=Cast(@text as Decimal(20,2) )
RETURN @Return
END
One of the problem exposed is an interesting one – there is no IsNaN() function in TSQL which would make this kludge unneeded. So my additional suggestion would be to create a CLR function that provides IsNaN() functionality.
Comments
Post a Comment