Performance issues with Function

[Raul2005]

Some one have an idea how to improve performance using this function that runs over 2680 records.


alter FUNCTION dbo.ConcatRows
(
@input int
)
RETURNS varchar(8000) AS
BEGIN

DECLARE @Output varchar(4000)
SELECT @Output = COALESCE(@Output, '') + Cast(' ' as varchar(10))+ Cast(Name as varchar(30))
FROM TABLE01
WHERE [Number] = @input

RETURN @Output
END



select Number,Name,Manager, dbo.ConcatRows(Number)
from TABLE01 group by Number,Name,Manager


Thanks

 

[NoCoolHandle]

Do you have an index on [Number]

and you shouldn't need the cast on your ' ' string.

also rather that use Coalesce to set your variable to being a non null value, how about useing set to fix the issue.

e.g.

DECLARE @Output varchar(4000)
set @Output=''
    SELECT @Output = @output + Cast('  ' as varchar(10))+ Cast(Name as varchar(30)) 
    FROM  TABLE01
    WHERE [Number] = @input

I don't know if isnull is more efficent than Coalesce, but the idea of coalesce is alternatives... isnull only tests a single field. so might work better.

---------------------------------------------------------

Bottom line you are asking SQL to do something bad. You should write your query to "Batch" the concatination into a single action rather than passing 1 row in at a time.

Your function is an example of why some folks here don't like them in general.

What you should do is..

elect Number,Name,Manager, Cast('  ' as varchar(10))+ Cast(Name as varchar(30)) 

from TABLE01 group by Number,Name,Manager

HTH

Rob

 

[vongrunt]

Something looks wrong here... what is the primary key on table TABLE01? And how many rows returns SELECT from above?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

UDF and indexes don't play very will in SQL Server 2000 (Table Scan)

http://sqlservercode.blogspot.com/

 

[Raul2005]

Rob

The query you suggest doesnt help to acomplish what I am looking for.

select Number,Name,Manager, Cast(' ' as varchar(10))+ Cast(DName as varchar(30))
from TABLE01 group by Number,Name,Manager

Sample:
Number Name manager DName
01 hrt jorge stp
01 hrt jorge thf
02 ola migue stp1
02 ola migue thf1
. . . .
. . . .

The output should be
01 hrt jorge stp thf
02 ola migue stp1 thf1


Any ideas????

 

[icrf]

Searching around turned up this article, which suggests that your scalar UDF is probably your best bet, unless you write your own CONCAT() aggregate and extend SQL Server.

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Also, you used "Name" in the UDF in your original post instead of "DName" as you just did in your last. I assume the latter's correct, as the former wouldn't give the results you quoted (and is probably what caused vongrunt concern).

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[Evadman]

I am totaly missing something. This piece:
COALESCE(@Output, '')
will ALWAYS return an empty string, no exceptions. You are declaring @Output right above it, and nothing is being done to @output before it gets to the COALESCE function, so it will ALWAYS be null.

Therefore, the sql statement:

SELECT @Output = COALESCE(@Output, '') + Cast('  ' as varchar(10))+ Cast(Name as varchar(30))

can be rewritten as:

SELECT @Output = '' + '  ' + Cast(Name as varchar(30))

Which is the same as:

SELECT @Output = '  ' + Cast(Name as varchar(30))

That means the select statement can be rewritten as:

select [Number],Name,Manager, '  ' + Cast(Name as varchar(30)) as Expr1
from TABLE01 group by Number,Name,Manager

What am I missing? Post some data from the table and the output your function returns for that data, because NoCoolHandle is spot on based on what you have posted.

 

[icrf]

That's what I thought, too, until I ran a test. Apparently, when you say SELECT @Output = DName FROM Table01 and there are 10 records in Table01, it sets @Output 10 times, and it only retains the last one (or it assumes this, and only does it once). However, if you build on @Output like he has in his UDF, you do in fact run that for every record returned and build up a concatenated string.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[Raul2005]

SOME SAMPLE DATA
Number Name manager DName
01 hrt jorge stp
01 hrt jorge thf
02 ola migue stp1
02 ola migue thf1
03 tet pedro AAA
03 TET pedro BBB
. . . .
. . . .

The output should be
01 hrt jorge stp thf
02 ola migue stp1 thf1
03 TET pedro AAA BBB

The function and the select statment is running over a view

Thanks

 

[NoCoolHandle]

Is there a limit to the number of rows that you expect for each "Number"?

 

[NoCoolHandle]

try

Create Table X (Number char(2), Name varchar(20) ,manager varchar(20) , DName varchar(20) )
go
Insert into X 
Select 
'01',     'hrt' , 'jorge' ,    'stp'
Union
Select '01',     'hrt' , 'jorge',     'thf'
Union
Select'02' ,    'ola' , 'migue',     'stp1'
Union
Select
'02'  ,   'ola' , 'migue' ,    'thf1'
Union
Select
'03'   ,  'tet' , 'pedro' ,    'AAA'
Union
Select
'03'    , 'TET',  'pedro' ,    'BBB'
go

Create Function ConcatRowData
(@number int)
returns varchar(8000)
as
begin
Declare @D varchar(8000)
Declare @Ret varchar(8000)
set @ret = ''
set @D = ''
while not @d  is null
	Begin
		 Select @D = min(Dname) from x where Dname > @D and number = @number
		if not @d is null
		begin
		set @ret = @ret + ' ' +  @d
		end
	End
Return @ret
End
go
Select distinct  number, name,manager,  dbo.ConcatRowData(number) from X