Performance issue in a T-SQL function

Question

I've written the following T-SQL function, but on implementation it seems to be very slow (it basically functions to return a phone number from a string such as "01530 999111 - don't phone in the evening!".

Do any of you marvellously insightful people have any tips for me in order to improve the performance of this function or have any more efficient alternatives to suggest?

ALTER FUNCTION [dbo].[xfnJustNumbers](@inStr varchar(255))
RETURNS [varchar](255)
AS
BEGIN

  DECLARE @outStr varchar(255)
  SELECT @outStr = ''
  DECLARE @charNo int
  SELECT @charNo = 0
  WHILE @CharNo < len(@inStr) begin
    SELECT @CharNo = @CharNo +1
    IF isnumeric(substring(@inStr,@CharNo,1))=1 SELECT @outStr = @outStr + substring(@inStr,@CharNo,1)
  END
  RETURN @outStr

END

Thanks :)

Replacing it with a CLR regex based function will likely speed things up. Scalar UDfs are pretty slow in SQL Server. It is important to look at the query and be sure that it is operating on the minimum number of rows. I've seen WITH SCHEMABINDING used on scalar UDFs - Not sure if that has any performance benefit at all. — Martin Smith
– Martin Smith, Commented Mar 14, 2011 at 11:49
not sure about the situation but it would definitely help such cases if you had another column for phone number in your table — Whimsical
– Whimsical, Commented Mar 14, 2011 at 11:51
BTW: Don't use IsNumeric as that matches things like +,-,$ you would use LIKE [0-9] or ASCII(@CHR) BETWEEN 48 AND 57 — Martin Smith
– Martin Smith, Commented Mar 14, 2011 at 12:20
I have opted for Martin's suggestion and on running tests, I've reduced the average execution time by half. Thank you! — user484002
– user484002, Commented Mar 14, 2011 at 15:04

Martin Smith · Accepted Answer · 2011-03-14 15:44:20Z

1

You could probably eke out a bit of additional performance from the TSQL version by trying some of the following.

Only calling it if you establish the string does contain at least one number CASE WHEN COl NOT LIKE '%[0-9]%' THEN '' ELSE [dbo].[xfnJustNumbers](col) END
Storing substring(@inStr,@CharNo,1) and len(@inStr) in variables rather than evaluating them more than once.
Checking ASCII(@chr) rather than calling isnumeric this is likely to be more what you need anyway.
Including the WITH SCHEMABINDING option in the function declaration to ensure that plans using these UDFs do not do unnecessary spools.

Having said all that though I'd use a CLR function using RegularExpressions for this.

answered Mar 14, 2011 at 15:44

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Performance issue in a T-SQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related