SQL Server '05: SP and UDF performance problem

[aconybeare]

Hi,

I have a UDF which on it's own works well but when I call it within the SP below the the query hangs (should return 1 or two rows, table contains about 50,000 rows)

Here is my UDF -

CREATE FUNCTION  uf_gGetPerson_Phone(
	@Jnl_Code AS VarChar(10), 
	@User_Id AS numeric,
	@Phone_Type AS VarChar(100)
)
RETURNS VarChar(255)
with schemabinding
AS
BEGIN 
	DECLARE @Detail varchar(255) 
	SET @Detail=(
					SELECT TOP 1 ph.Detail 
					FROM Sirius.phone ph 
					LEFT OUTER JOIN Sirius.Phone_Jnl pj ON ph.Phone_Id=pj.Phone_Id AND pj.Status='A' AND pj.Jnl_Code=@Jnl_Code 
					WHERE ph.User_Id=@User_Id 
					AND ph.Phone_Type=@Phone_Type 
					AND ph.Status='A' 
					ORDER BY CASE pj.Jnl_Code WHEN @Jnl_Code THEN 0 ELSE 1 END ASC, ph.Default_Opt DESC, ph.Sort_Order, ph.Phone_Id
				)
	RETURN @Detail
END

Here is my SP; I've hilighted the problem line, When I replace the call to the UDF (sirius.uf_gGetPerson_Phone) with the actual query from the UDF my SP executes immediately -

CREATE PROCEDURE up_gGetNewAuthors(
   @MS_No varchar(15),
   @Version_No smallint,
   @Submitting_Author numeric=null
)
As

SET NOCOUNT ON 

DECLARE @Jnl_Code varchar(10)

IF @Submitting_Author Is NULL BEGIN
	SELECT @Jnl_Code=Jnl_Code, @Submitting_Author=Submit_Auth_ID FROM MS_Version WHERE MS_No=@MS_No AND Version_No=@Version_No
END ELSE BEGIN
	SET @Jnl_Code=(SELECT Jnl_Code FROM Manuscript WHERE MS_No=@MS_No)
END
--   Print 'Submitting Author ID: '+Convert(varchar,@Submitting_Author)

SELECT 
	a.Author_ID
	,p.User_ID
	,p.Prefix
	,p.First_Name
	,p.Last_Name
	,p.Suffix
	,p.User_Name
	,sirius.uf_gGetPerson_Phone(@Jnl_Code,p.User_Id,'Email') As Email
	,(SELECT Title FROM MS_Version WHERE MS_No=@MS_No AND Version_No=@Version_No) As Paper_Title
	,(SELECT Count(ph.Phone_Id) FROM People p2 JOIN Phone ph ON p2.User_Id=ph.User_Id AND ph.Status='A' AND ph.Phone_Type='Email' WHERE ph.Detail=sirius.uf_gGetPerson_Phone(@Jnl_Code,p.User_Id,'Email') AND p2.Status NOT IN('T') AND p2.Deleted=0) As Active_Email_Count	
FROM People p 
JOIN Authors a ON p.User_ID=a.User_ID
WHERE a.MS_No=@MS_No 
AND a.Version_No=@Version_No
AND a.Deleted=0
AND p.Deleted=0
AND p.Status IN('T')
AND p.Owner=@Submitting_Author
ORDER BY a.Sort_Order, a.Author_ID


SET NOCOUNT OFF

Return @@Error
GO

Here is the problem subquery clipped out -

declare @User_Id int		set @User_Id=1
declare @Jnl_Code varchar(10)	set @Jnl_Code='BJ'

SELECT Count(ph.Phone_Id) 
FROM People p2 
JOIN Phone ph ON p2.User_Id=ph.User_Id 
AND ph.Status='A' 
AND ph.Phone_Type='Email' 
WHERE ph.Detail=sirius.uf_gGetPerson_phone(@Jnl_Code,@User_Id,'Email') 
AND (p2.Status < 'T' OR p2.Status > 'T')
AND p2.Deleted=0

Any help with this will be greatly appreciated, I'd be happy to restructure my query if it's going to improve the performance

Cheers Al