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

Popular posts from this blog

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

Yet once more into the breech (of altered programming logic)

Error : /ScriptResource.axd : Invalid viewstate.