I have a scalar UDF doing some string manipulations. When I use it in select clause it causes performance issues. I read that if we do not query tables or views in scalar functions it won't affect your performance much. Below is the part of my UDF code which I am trying to replace based on that.
WHILE @i <= @Maxid BEGIN SET @i = @i + 1 SELECT @addrname = name FROM [dbo].[temp] WHERE id = @i IF CHARINDEX (@addrname, 'TEST STRING FOR TEST') > 0 BEGIN SET @addrstart = CHARINDEX (@addrname, 'TEST STRING FOR TEST') SET @addrlen = LEN (@addrname) SET @i = 0 BREAK END END
My dbo.temp table contains some sample char strings which I need to search. Temp table contains two columns id and name. It has following sample data in it:
I am looking to out to replace use of temp table in above code to see if it helps in improving performance. Please help me with that.
UDF basically creates some type of code from addressline and PO code fields and it needs to run for 5 million rows.