user defined function hanging - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

user defined function hanging

Author

Topic

trahma
Starting Member

2 Posts

Posted - 2009-05-15 : 12:02:14

I'm kind of stumped at this point. I have a block of code that when I run hangs on my SQL2005 server. In the past it's run fine but over the past couple weeks, it seems to despise me.

Here's the odd thing, I can take the code out of the UDF and run it on it's own and it returns in <1 second. Putting the code into a UDF yields the function taking over 45 minutes to complete.

Here's my UDF which seems to hate the thought of completing at the moment:

CREATE  FUNCTION [dbo].[fnAgentProductionReport]
(	
	@sp		DATETIME,
	@ep		DATETIME
)
RETURNS @report TABLE
(
	unqempid	int,
	[name]		varchar(128),
	Sch			int,
	callTime	int,
	pr			float,
	conf		int,
	confTime	int,
	cr			float
)
AS
BEGIN
	-- increment day to account for sql using midnight as a base date
	--SET @ep = @ep + 1


	INSERT INTO @report (unqempid, [name], Sch, callTime, pr, conf, confTime, cr)
		SELECT DISTINCT
			c.unqempid, e.firstname + ' ' + e.lastname AS Name,0,0,0,0,0,0
		FROM
			contacts c
		INNER JOIN
			employees e
			ON c.unqempid = e.unqempid
		WHERE
			contactdt between @sp and @ep 

	UPDATE 
		@report
	SET 
		sch = fn.count
	FROM
		@report r
	INNER JOIN 
		fnEmpScheduled(@sp,@ep) fn 
		ON r.unqempid = fn.unqempid

	UPDATE 
		@report
	SET 
		callTime = fn.seconds
	FROM
		@report r
	INNER JOIN 
		fnEmployeeTrackingTime(@sp,@ep,1) fn 
		ON r.unqempid = fn.unqempid

	UPDATE 
		@report
	SET 
		pr = round(convert(float,sch) / convert(float,convert(float,callTime)/3600),2)
	WHERE
		isnull(callTime,0) IS NOT NULL AND convert(float,convert(float,callTime)/3600) > 0

	UPDATE 
		@report
	SET 
		conf = fn.count
	FROM
		@report r
	INNER JOIN 
		fnEmpConf(@sp,@ep) fn 
		ON r.unqempid = fn.unqempid

	UPDATE 
		@report
	SET 
		confTime = fn.seconds
	FROM
		@report r
	INNER JOIN 
		fnEmployeeTrackingTime(@sp,@ep,2) fn 
		ON r.unqempid = fn.unqempid


	UPDATE 
		@report
	SET 
		cr = round(convert(float,conf) / convert(float,convert(float,confTime)/3600),2)
	WHERE
		isnull(confTime,0) IS NOT NULL AND convert(float,convert(float,confTime)/3600) > 0
	RETURN
END

So in trying to troubleshoot what the holdup is (thinking it's an indexing issue somewhere) I modified the udf to this:

DECLARE	@sp	DATETIME
DECLARE	@ep	DATETIME

SET @sp = '5/8/09'
SET @ep = '5/16/09'


CREATE TABLE ##report 
(
	unqempid	int,
	[name]		varchar(128),
	Sch			int,
	callTime	int,
	pr			float,
	conf		int,
	confTime	int,
	cr			float
)

	-- increment day to account for sql using midnight as a base date
	--SET @ep = @ep + 1


	INSERT INTO ##report (unqempid, [name], Sch, callTime, pr, conf, confTime, cr)
		SELECT DISTINCT
			c.unqempid, e.firstname + ' ' + e.lastname AS Name,0,0,0,0,0,0
		FROM
			contacts c
		INNER JOIN
			employees e
			ON c.unqempid = e.unqempid
		WHERE
			contactdt between @sp and @ep 

	UPDATE 
		##report
	SET 
		sch = fn.count
	FROM
		##report r
	INNER JOIN 
		fnEmpScheduled(@sp,@ep) fn 
		ON r.unqempid = fn.unqempid

	UPDATE 
		##report
	SET 
		callTime = fn.seconds
	FROM
		##report r
	INNER JOIN 
		fnEmployeeTrackingTime(@sp,@ep,1) fn 
		ON r.unqempid = fn.unqempid

	UPDATE 
		##report
	SET 
		pr = round(convert(float,sch) / convert(float,convert(float,callTime)/3600),2)
	WHERE
		isnull(callTime,0) IS NOT NULL AND convert(float,convert(float,callTime)/3600) > 0

	UPDATE 
		##report
	SET 
		conf = fn.count
	FROM
		##report r
	INNER JOIN 
		fnEmpConf(@sp,@ep) fn 
		ON r.unqempid = fn.unqempid

	UPDATE 
		##report
	SET 
		confTime = fn.seconds
	FROM
		##report r
	INNER JOIN 
		fnEmployeeTrackingTime(@sp,@ep,2) fn 
		ON r.unqempid = fn.unqempid


	UPDATE 
		##report
	SET 
		cr = round(convert(float,conf) / convert(float,convert(float,confTime)/3600),2)
	WHERE
		isnull(confTime,0) IS NOT NULL AND convert(float,convert(float,confTime)/3600) > 0


SELECT * FROM ##report

Any thoughts as to what I'm doing wrong or what could be going on?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-15 : 12:18:27

Have you updated statistics on the underlying tables? If so, the cached plan may need to be recompiled. If not, try updating the stats and see if it improves.

trahma
Starting Member

2 Posts

Posted - 2009-05-15 : 12:31:17

I feel semi stupid for not noticing this prior to posting. One of my nightly maintenance routines was bombing and I didn't notice it didn't run update statistics. Any insight as to why it would still run outside of being a UDF still though?

-Justin

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-15 : 13:32:49

Probably a bad plan in cache. On some of my systems I force recompiles once a day or so, to fix situations like this. I also flush the procedure cache too if things generally start to bog down, and it helps.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-15 : 13:38:46

Remove this part

WHERE
		isnull(callTime,0) IS NOT NULL

It will ALWAYS be true.
Also almost all the updates can be done in original insert because the functions used later in the update are static since same parameter is used as function parameter.

E 12°55'05.63"
N 56°04'39.26"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-15 : 13:46:57

quote:
Originally posted by Peso

Remove this part
WHERE
isnull(callTime,0) IS NOT NULL
It will ALWAYS be true.
Also almost all the updates can be done in original insert because the functions used later in the update are static since same parameter is used as function parameter.

Yeah, I was looking at that and was puzzled also. Should that be

NULLIF(calltime, 0) IS NOT NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources