Performance of row concatanation query

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 of row concatanation query

Author

Topic

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-03 : 19:09:34

Background:
Migrating a database that violates every normal form (even 1NF!) and transforming into a normalised database.

SQL2K.....

My problem is one of performance in "compressing" rows

Here is stripped version...
Removed Key definitions (They don't have any on the "old" table anyway) and added the Identity column into the New table for ease....


--Old Table
create table Notes (Record_Number int not null, DN int not null, Number int, Note varchar(50))
go
insert Notes (1,1,1,'Test1')
insert Notes (2,1,2,'Test2')
insert Notes (3,2,1,'Test1')
insert Notes (4,2,2,'Test2')
insert Notes (5,1,3,'Test3')
insert Notes (6,2,3,'Test3')
insert Notes (7,4,1,'Test1')
go
--New table
create table NewNotes(NoteID int identity(1,1) not null,DN int not null, Note varchar(50) not null)
go

As you can see the "Note" has a Sequence (Record_Number), what should be a Foreign Key of DN and a Note Number...

They would like consecutive Notes to be "merged" into a single note.

The defintion of "consecutive" is Record_Number sequence and Number sequence.

From the sample data
Rows 1,2 are compressed into 1 row
Rows 3,4 are compressed into 1 row
Row 5 to a single row
Row 6 to a single row
Row 7 to a single row

My soution so far which has terrible performance.. (I am dealing in millions of rows here...)

First a query to find these sequences...


Select DN, Count(DN) as CNT , min(Record_Number) as StartRec from Notes
group by DN, (Record_Number - Number)

This will generate the DN, the number of rows to compress and the "start" number of the row sequence.

Now create a row compression function...


create function fnCompressNote
(@StartRec int,
@Count int)
returns varchar(1000)
as
begin
declare @Note varchar(1000)
Select @Note = @Note  + Note + '| ' from Notes where Record_Number between @StartRec and (@StartRec + @Count -1)
return @Note
end
go

So to tie it all together....


Select DN, dbo.fnCompressNote(StartRec ,CNT) as Note
from
(
Select DN, Count(DN) as CNT , min(Record_Number) as StartRec from Notes
group by DN, (Record_Number - Number)
) as X

It works.. but realy slowly....

Any other solutions would be most wlecome....

DavidM

"SQL-3 is an abomination.."

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-09-03 : 20:59:56

A suggestion and a question:

You could identify the singleton rows ahead of time and not push them through dbo.fnCompressNote().

What are your index definitions?

Jonathan
{0}

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-03 : 21:36:47

Thanks Johnathon..

I will do that..

I can make any index on the old note.. currently just the DN.

But I will make a clustered Index on the Record_Number...

DavidM

"SQL-3 is an abomination.."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-03 : 21:47:35

David-

Have you looked through this thread and its embedded links?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15651

If you drill down through all of them, you'll find at least 4-5 different techniques for concatenating multiple rows, I don't think any of them use user-defined functions and could very well be faster.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-03 : 22:19:09

Thanks Rob..

I will do that and I should have done that earlier....

DavidM

"SQL-3 is an abomination.."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-04 : 05:37:49

This one too, although it gets a bit speculative towards the end:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=18491

(but the idea of putting an extra variable in the UPDATE to check for order violations is viable and, I would contend, sensible.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources