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.

  • 2
    Your problems are more fundamental than what's going on in the scalar UDF. I'd be amazed if, on getting a better idea of the underlying problem, you need the scalar UDF at all. Can you give us some more detail about what you want to do, and how much data might be involved? Can you describe the business rules around "...creates some type of code..."? Can you post some examples of the data, and what you want the finished product to look like? I'm glad you gave us a row count. A UDF is really not going to be your friend over 5 million rows :-) – DatumPoint Apr 21 '16 at 7:51
  • You are searching a table in a select statement? – paparazzo Apr 21 '16 at 8:08
  • Just as an aside, if you have functions that do not access data then you should use WITH SCHEMABINDING ( well you should use it any way :) ) then SQLServer will know that no data is being is being accessed that can help in some scenarios. – DaveBally Apr 21 '16 at 12:49

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.

  • 1
    UDF affects performance in select clause as well. Code I gave is just a part of UDF. There is a lot more in it but they are dealing with constants and variables. For now, I am just looking out to remove temp table to see if that makes any difference. – SQLPRODDBA Apr 21 '16 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.