Scalar UDF causing performance issues in SQL Server

Posted on

Question :

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
         SET @i = @i + 1
         SELECT @addrname = name
         FROM [dbo].[temp]
         WHERE id = @i

         IF CHARINDEX (@addrname, 'TEST STRING FOR TEST') > 0
               SET @addrstart = CHARINDEX (@addrname, 'TEST STRING FOR TEST')
               SET @addrlen = LEN (@addrname)
               SET @i = 0

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:

1, #

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.

Answer :

Scalar UDFs always affect performance. What you might have read is that you probably don’t want to use them in your WHERE or JOIN ON clauses because that test is happening as part of the filtering stage and thus may force every single row to go through the UDF to determine the output.

This is compared to the less worse option of just using it in the part of the SELECT statement that specifies the columns/data you want to return; in which case you’ve already filtered everything and so there’s presumably less to work on. i.e. Doing a test of the filtered data to return a bit field as to whether there’s a match or not.

Apart from this, about making searches more performance, in your case you are searching for a string anywhere within another string, and that’s bad. To make things faster you would need to make it so that you are only searching for a string at the beginning of the string (x like ‘%…’) or end of the string (reverse(x) like (‘%’ + reverse(‘…’) as a persisted computed column and then stored in an index).

Either way you would then go with dynamic SQL; iterate the temp table and build up a string with the SQL statement you want that codes in the specific search terms you are looking for in the WHERE clause; then run with sp_executesql. But it’s terrible practice and sometimes a security nightmare.

But it would only go significantly faster if it’s on those ‘%…’ searches. Not ‘%…%’.

The other alternative is to investigate full-text indexing to see if your specific search terms are going to meet that criteria, but it’s much more complicated, and you’re going to have to crack open a book to learn it.

Leave a Reply

Your email address will not be published. Required fields are marked *