June 17, 2008 at 8:17 am
Matt Miller (6/17/2008)
Yup - I can fix the one issue on tempDB, but it still performs rather poorly on few, big groups. So - while it can beat the concat function under certain data distribution scenarios, I'd say the concat function is STILL the better option.
Matt, can you share this code? I have a suspicion as to the problem and a possible solution.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2008 at 8:37 am
--Clean up testing scenario
drop table #petnames
drop table #petnames2
drop table persons
drop table person_pets
drop table #temp
go
drop function concatf
go
CREATE FUNCTION Concatf(@ID AS INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @RetStr AS VARCHAR(8000)
SELECT @RetStr=COALESCE(@RetStr+',','')+NAME
FROM Person_Pets
WHERE Person_ID = @ID;
RETURN @RetStr
END
go
--set up testing scenario
create table persons(id int identity(1,1) primary key clustered, name varchar(20))
create table person_pets(PpID int identity(1,1) primary key clustered,person_ID int, name varchar(20))
declare @sample int
set @sample=250000;
insert persons(name)
select top(@sample) left(cast(newId() as varchar(100)),10)
from sys.all_columns sc1, sys.all_columns sc2;
insert person_pets(person_ID,name)
select top 1000000 rand(checksum(newid()))*@sample+1,
left(cast(newId() as varchar(100)),10)
from sys.all_columns sc1, sys.all_columns sc2;
--a couple of vars
declare @g datetime
set @g=getdate();
declare @tmppetnames varchar(8000)
set @tmppetnames='';
declare @dummy varchar(8000)
declare @prevperson int
set @prevperson=0;
--====== capture all of the pets
Selectpp1.person_Id,
identity(int,1,1) as rid,
pp2.person_ID as nextID, --set up a "lookahead" to find when to push this to
pp1.[Name],
Cast('' as varchar(8000)) as pet_name
Into #temp
From person_pets pp1 left join person_pets pp2 on pp1.PpID=pp2.ppid+1
order by person_id,nextid
create unique clustered index pktmp on #temp(person_Id,nextID,RID)
--====== inline in all of the pet names
Update #temp
Set @tmppetnames=case when @prevperson=person_ID then @tmppetnames +',' else '' end + Name,
pet_name=case when person_ID=nextID then Null else @tmppetnames end,
@prevperson=person_id,
@dummy=@tmppetnames
From #temp with (index(pktmp), tablockx)
select persons.*, pets
into #petnames
from
persons
left outer join
(select person_id, max(pet_name) pets
from #temp
group by person_id) petlist
on persons.id=petlist.person_id
select 'running', datediff(ms,@g,getdate());
go
--concatenation method
declare @g datetime
set @g=getdate();
create index j on person_pets(person_ID,name)
select persons.*, dbo.concatf(ID) as pnames
into #petnames2
from persons
select 'func', datediff(ms,@g,getdate());
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 17, 2008 at 11:09 am
Ah, it's SQL, not CLR. It looks like the Triangular Concatentation problem (ie., sequential cumulative string concatenation is O(n2), esp. with immutable strings), which is solvable in CLR, but I've never figured out how to solve it with just SQL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2008 at 11:12 am
rbarryyoung (6/17/2008)
Ah, it's SQL, not CLR...
Du'h, it's not the SQL2005 forum... :blush:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2008 at 11:40 am
rbarryyoung (6/17/2008)
rbarryyoung (6/17/2008)
Ah, it's SQL, not CLR...Du'h, it's not the SQL2005 forum... :blush:
I know. It also doesn't help that the OP really did have 2005. I was already started thinking, so I kind of had to finish the thought.
CLR would have been easy (I don't have to run through and worry about order, etc...). I was just curious whether this could be a second arrow in the old quiver for SQL 2000, but on third look, no, ain't going to fly. It's just not solid enough, with too many pitfalls on the perf side. Of course, the more "solid" you make it, the slower it operates, making it damn near impossible to take on the T-SQL function's performance.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply