UDF runs fine in dev but is a hog in production

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UDF runs fine in dev but is a hog in production

Author

Topic

bobswi
Starting Member

4 Posts

Posted - 2009-01-08 : 13:11:51

I run the below select in dev (smaller server 2gb memory, 4 cpu's, etc) against ~3000 records and the result is returned in ~30seconds
When I run the same thing in production (beefy server, 32Gig ram, 8 cpus), it just runs and runs. A top 10 takes 1-3 minutes, I profiled it and ~400,000 reads, vs. dev's ~400 reads. Obviously the production box is not executing the UDF in-line, but what is confusing me is the dev box runs it like it is in-line.
Is there some setting that could be affecting this at a server level?
both boxes are on SQL 2000 SP4(2187). I tried playing with max worker threads and max degree of parellelism, and simulating some extra traces we have on the prod box in dev, but I can't seem to figure out why it takes forever in prod.

Sample Select:
SELECT dbo.fnCleanSpecialChars_Test(LTRIM(RTRIM(FIRST_NAME_IN)),'') ,
dbo.fnCleanSpecialChars_Test(LTRIM(RTRIM(MIDDLE_NAME_IN)),'') ,
dbo.fnCleanSpecialChars_Test(LTRIM(RTRIM(LAST_NAME_IN)),'')
FROM tblNewDoctor_Test
WHERE ProcDtTm IS NULL

Function:
CREATE FUNCTION dbo.fnCleanSpecialChars_test
(
@StringToClean AS VARCHAR(50),@CharactersAllowed AS VARCHAR(50)
)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @AsciiNum AS INT
DECLARE @CleanedString AS VARCHAR(50)
DECLARE @localCharactersAllowed as varchar(50)
SET @localCharactersAllowed = @CharactersAllowed
SET @CleanedString = @StringToClean

IF @CleanedString IS NULL OR LTRIM(RTRIM(@CleanedString)) =''
Set @CleanedString = NULL

SET @AsciiNum = 1
WHILE @AsciiNum <= 127
BEGIN
IF CHARINDEX(CHAR(@AsciiNum) , @localCharactersAllowed) = 0
BEGIN
SET @CleanedString = CASE
WHEN @AsciiNum BETWEEN 1 and 31 OR @AsciiNum BETWEEN 33 and 44 OR @AsciiNum BETWEEN 46 AND 47
OR @AsciiNum BETWEEN 58 and 64 OR @AsciiNum BETWEEN 91 and 96 OR @AsciiNum BETWEEN 123 and 127 THEN
REPLACE(@CleanedString, CHAR(@AsciiNum), '')
WHEN @AsciiNum =32 THEN --Double Space to single space
REPLACE(@CleanedString, ' ', ' ')
WHEN @AsciiNum =45 THEN --(- with space)
REPLACE(@CleanedString, CHAR(@AsciiNum), ' ')
ELSE
@CleanedString
END
END
ELSE
SET @CleanedString =@CleanedString
SET @AsciiNum = @AsciiNum + 1
END

WHILE CHARINDEX(' ', @CleanedString) <> 0
BEGIN
SET @CleanedString = REPLACE(@CleanedString, ' ', ' ')
END

RETURN @CleanedString
END

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-08 : 14:01:27

We had a similar problem not too long ago and realized that the indexes on the production server table were highly fragmented. We basically recreated the indexes and that resolved the problem.

bobswi
Starting Member

4 Posts

Posted - 2009-01-08 : 14:14:24

Yep, that was my first thought, or some problem with the plan in cache. We've recreated the table with identical indexes and smaller subset of data (3000), same as what is in development. We also performed a dbcc freeproccache / dbcc dropcleanbuffers on both dev & prod to get a fresh plan in cache, and we still have a difference in performance.

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-08 : 19:43:37

I hope there is no memory leak on production.. I have come across that before when everything else was working fine.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-01-09 : 04:36:58

Are you 100% sure it's the UDF? How does the performance of the code below behave in DEV/PROD?
Can you post the execution plans from DEV&PROD of your code/code below?
Can you transfer the offending code into your presentation layer?

SELECT LTRIM(RTRIM(FIRST_NAME_IN)),
LTRIM(RTRIM(MIDDLE_NAME_IN)),
LTRIM(RTRIM(LAST_NAME_IN))
FROM tblNewDoctor_Test
WHERE ProcDtTm IS NULL

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-09 : 06:53:47

The problem is the UDF. I tested this trying to get it to run reasonably fast for any number of rows (using test PC with dual processor and 2G RAM) but could not do it.
Running for a Single row in a new test table - I cancelled it after 4 minutes.

Select *
into newTable1
from Table1
where id =1 --gets 1 row instantly

SELECT dbo.fnCleanSpecialChars_Test(LTRIM(RTRIM(e)),'')
FROM newTable1

Select * from newTable1

Drop table newTable1

If you provide a list of chars you don't want to allow - we should be able to give you an UPDATE statement rather than looping through each row 127 times.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-09 : 07:28:55

Here is an UPDATE using nested REPLACE (I haven't entered all the ASCII's but easy to add more:

Update Table1 set E=
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(E,CHAR(32),' ')
, CHAR(45),' ')
, CHAR(1),' ')
, CHAR(2),' ')
, CHAR(3),' ')
, CHAR(4),' ')
, CHAR(5),' ')
, CHAR(6),' ')
, CHAR(7),' ')
, CHAR(8),' ')
, CHAR(9),' ')
, CHAR(10),' ')

I ran this on a million rows for a variety of different datatypes which were not indexed - it took a few seconds on my test PC.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-01-09 : 08:30:15

Is it possible that the loop is badly coded for some data values?
and that the "WHILE @AsciiNum <= 127" condition is not being reached??

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-09 : 09:23:23

Yes, but the point is there is no need to loop.

To fix infinite loop:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnCleanSpecialChars_test]
(
@StringToClean AS VARCHAR(50),@CharactersAllowed AS VARCHAR(50)
)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @AsciiNum AS INT
DECLARE @CleanedString AS VARCHAR(50)
DECLARE @localCharactersAllowed as varchar(50)
SET @localCharactersAllowed = @CharactersAllowed
SET @CleanedString = @StringToClean

IF @CleanedString IS NULL OR LTRIM(RTRIM(@CleanedString)) =''
Set @CleanedString = NULL

SET @AsciiNum = 0
WHILE @AsciiNum < 127 -- can we not ignore nulls AND @CleanedString IS NOT NULL
--ensure increment dispite result
SET @AsciiNum = @AsciiNum + 1
BEGIN
IF CHARINDEX(CHAR(@AsciiNum) , @localCharactersAllowed) = 0
BEGIN
SET @CleanedString = CASE
WHEN @AsciiNum BETWEEN 1 and 31 OR @AsciiNum BETWEEN 33 and 44 OR @AsciiNum BETWEEN 46 AND 47
OR @AsciiNum BETWEEN 58 and 64 OR @AsciiNum BETWEEN 91 and 96 OR @AsciiNum BETWEEN 123 and 127 THEN
REPLACE(@CleanedString, CHAR(@AsciiNum), '')
WHEN @AsciiNum =32 THEN --Double Space to single space
REPLACE(@CleanedString, ' ', ' ')
WHEN @AsciiNum =45 THEN --(- with space)
REPLACE(@CleanedString, CHAR(@AsciiNum), ' ')
ELSE
@CleanedString
END
END
ELSE
SET @CleanedString =@CleanedString

END

WHILE CHARINDEX(' ', @CleanedString) <> 0
BEGIN
SET @CleanedString = REPLACE(@CleanedString, ' ', ' ')
END

RETURN @CleanedString
END

You are doing multiple loops - looping through every row and inside each row another loop of 127.
If you UPDATE using nested REPLACE you are using a set based approach which is almost always the fastest method. Using loops in this case is slow and increases the chance of locking which may explain why it runs slower on a production server.

An alternative to checking data would be to put validation before data is entered - either at point of entry (e.g. in Application) or as rules on the columns.
Either way avoid looping through every row.

bobswi
Starting Member

4 Posts

Posted - 2009-01-12 : 12:50:39

Hi again, thanks for the replies. We are rewriting the proc to not use the UDF, in the meantime I was investigating why the performance difference is there.

Thanks

bobswi
Starting Member

4 Posts

Posted - 2009-01-16 : 17:42:35

We opened a case up with Microsoft and they found that when executing a scalar UDF many times, with a server-side trace on SP:StmtCompleted or SP:StmtStarted, the trace will cause extreme cpu, reads, and execution time, due to the trace trying to access each iteration of the UDF call. We've turned the trace off (which we were using to monitor the production server), and it runs the same as development now! =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources