Slow Function

ubsmail
ubsmail used Ask the Experts™
on
Any suggestions to speed up this funtction? 350 Rows takes over 1 minute. Maybe using stuff? 
CREATE function GetStockDesc(@StockNumber varchar(8000))
RETURNS varchar(8000) AS     
BEGIN    
DECLARE @StockDesc varchar(8000)
set  @StockDesc = ''

 
Select  @StockDesc = isnull(rtrim(ltrim(@StockDesc)) + ', ', '') + brandcat.desc1
From    brandcat
Where  brandcat.number  =  @StockNumber


if(LEN(@StockDesc) > 0)
set @StockDesc  = substring(LTRIM(rtrim(@StockDesc)), 2,LEN(@StockDesc))  

 

  
  
RETURN  @StockDesc    
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Anthony Perkins
Top Expert 2012

Commented:
I guess you did not see my suggestion in your other related question.  Here it goes again:
CREATE FUNCTION GetStockDesc (
			@StockNumber varchar(8000))

RETURNS varchar(8000)

AS 

BEGIN    
    DECLARE @StockDesc varchar(8000)
 
    SELECT  @StockDesc = ISNULL(@StockDesc + ', ', '') + LTRIM(brandcat.desc1)
    FROM    brandcat
    WHERE   brandcat.number = @StockNumber
  
    RETURN  @StockDesc    
END

Open in new window

Top Expert 2012
Commented:
And also make sure there is an index on number or post the code from the VIEW brandcat so that we can make further suggestions.

Also you need to let everyone know you are using SQL Server 2000 or they will suggest you use FOR XML to resolve this.
ubsmail

Author

Commented:
Thanks Acperkins I posted prior to the resoultion on the other post.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial