Performance Issue using scalar-valued function

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)

Performance Issue using scalar-valued function

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-11-15 : 01:42:02

hi,
i am having a performance issue using a scalar-valued function

my table structure Employees

ID Employee code
-- -------- ----
1 XXX a
1 XXX b
2 YYY a
2 YYY b
2 YYY c
3 ZZZ a

My select statement

select ID,Employee,dbo.fnConcateEmployeeCode(ID) from Employees

scalar-valued function

ALTER FUNCTION [dbo].[fnConcateEmployeeCode](@ID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Output VARCHAR(8000)

SELECT @Output = COALESCE(@Output+',', '') + CONVERT(varchar(20),E.Code)
FROM dbo.Employees
WHERE E.[ID]=@OrderID
ORDER BY E.ID

RETURN @Output
END

the result would be

1 XXX a,b
2 YYY a,b,c
3 ZZZ a

the query is working fine, if the where condition in select statement is narrow (i.e select on specific rows)

in some cases i need to select the whole table using this query (the table has thousands of records).the select is taking very long to execute.i get a connection time out error

is there any other way around to achieve this functionality,or how can i optimize the function to get faster results.

Note:The query gives the results very fast on whole table, below 1 sec
if i make the select directly on the table without the function

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-11-15 : 03:23:43

i tried it even this way below

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + E.Code+ ','
FROM dbo.Employees E
Where E.ID=@ID
ORDER BY E.ID

SELECT @listStr = SUBSTRING(@listStr , 1, LEN(@listStr)-1)

RETURN @listStr

i still have the same problem.........

lampan
Starting Member

4 Posts

Posted - 2007-11-15 : 04:36:01

Since the function you have written will get called per row, it will obviously kill performance.

You can keep a table with Id & concated codes. But then you will need to update this table everytime Employees table gets updated.. OR you can add a column to you Employee master table [where Id will be PK], & this col will always have updated concated codes.

Hope this helps..

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-11-15 : 04:53:15

i have already tried the above way which you have mentioned,I have created the table with ID and Concatenated Codes
But filling the new table with Concatenated Codes is taking more than 30 mins
And the second approach Which you have mentioned cant be feasible,because i cant have ID as primary key,ID gets repeated in the table....

can we have any other approach, other than using functions ..........
i even tried writing a cursor but the same results....

DECLARE @Code_UniqueID Varchar(20),
@CodeList varchar(100)
SET @PCodeList = ''
DECLARE crs_Code CURSOR
FOR SELECT E.Code
FROM Employees E
WHERE E.[ID]=@ID
ORDER BY E.ID

OPEN crs_Code
FETCH NEXT FROM crs_Code INTO @Code_UniqueID

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CodeList = @CodeList+CAST(@Code_UniqueID AS varchar(20))+ '; '
FETCH NEXT FROM crs_Code INTO @Code_UniqueID
END

SET @CodeList = SUBSTRING(@CodeList,1,DATALENGTH(@CodeList)-2)

CLOSE crs_Code
DEALLOCATE crs_Code

--SELECT @CodeList
Return @CodeList
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources