June 25, 2009 at 4:40 pm
here is the thing:
i have a migration in process, first approach was to use cursors for a set of tables, worked fine for 100 records but for the 4000000 is taking too long, so i split the migration to several "Insert into ..... Select ... from" statements.
now this is going better i migrated almost all records in about 2 hours.
but i have a problem with a comments table that is in this form:
ID4com, COmment1, comment2
1, 'COM1', 'COM2'
1, 'COM3', 'COM4'
1, 'COM5', 'COM6'
2, 'COM1', 'COM2'
2, 'COM3', 'COM4'
3, 'COM1', 'COM2'
so comments are spread over this table, they have to go to a new table with an only ID, i've tried to run a cursor that gather's the comments per ID4COM that are the same to have this:
ID4COM, COMMENT
1, 'COM1 + COM 2 + COM 3 + COM4 + COM 5 + COM 6'
2, 'COM 1 + COM 2.....'
the script with cursors runs fine.... takes more less 2 hours to show me all comments with IDs, but when i try to perform updates with this comments... takes like forerver... i left it running fo 1.5 days and nothing happened.
so now i'm migrating again the table and created a user defined function to return comments only, so i'll place it along the migrating statement (INSERT ... SELECT ...), i left the select statement running and took like 2 days, i'm now waiting for the new statement finishes. but i think there must be a better way.
any comments are appreciated.
the table has over 2.5 million records of comments
Regards
Jorge
June 25, 2009 at 7:06 pm
Hi Jorge
Can you please clarify, the updates you are trying to do for comments is on which table? Is it the same comments table or you are updating the new comments value to an existing table? Can you post the schema of destination table?
June 26, 2009 at 8:08 am
the comments must go on a new comment field in a table like this
newID, priorID, firstname, lastname, date, comment
So the priorID is the ID from the comments table
ID, COM1, COM2
1, 'com1', 'com2'
1, 'com3', 'com4'
2, 'com1', 'com2'
newID, priorID, firstname, lastname, date, comment
1, 1, Agent, Smith, 01/01/00, 'com1 + com2 + com3 + com4'
2, 2, Lolly, Pop, 03/04/65, 'com1 + com2'
the names were on a different table in the legacy system, looks like they done that on restricctions of that time (i really don't want to judge anyone 🙂 ).
(actually the structure is more complex, but i can't post it due client's requirements)
Anyways here are my approaches
1. CURSORS
DECLARE CURSOR cur_name FAST_FORWARD FOR select ID, firstname, lastname from NAMES order by id
open cur_name
FETCH NEXT FROM cur_name into @pID, @pFname, @pLname
WHILE @@FETCH_STATUS 0
BEGIN
set @COMM = ''
DECLARE CURSOR cur_coms FAST_FORWARD FOR select ID, COM1, COM2 from comments where id = @pID
open cur_coms
FETCH NEXT FROM cur_coms into @pID, @pCOM1, @pCOM2
WHILE @@FETCH_STATUS 0
BEGIN
Set @COMM = @COMM + @pCOM1 + @pCOM2
FETCH NEXT FROM cur_coms into @pID, @pCOM1, @pCOM2
END
close cur_coms
deallocate cur_coms
insert into NEWNAMESTABLE values (@PID, @pFname, @pLname, @COMM)
FETCH NEXT FROM cur_name into @pID, @pFname, @pLname
END
close cur_name
deallocate cur_name
for 100 records this works fine (there are some other tables, like CARS and GRANTORS)
but for the 900000 + (added up to the other tables (COMMENTS, CARS, GRANTORS) this ended up like 4000000 rows), and this is taking a long time (1000 records from NAMES in about 1 hour)
2. Separate inserts and post update
insert into NEWNAMESTABLE (priorID, firstname, lastname) select ID, firstname, lastname from NAMES
so i tried to get updates after this, having the priorID saved
DECLARE CURSOR cur_coms FOR_UPDATE FOR select ID, COM1, COM2 from comments
open cur_coms
FETCH NEXT FROM cur_coms into @pID, @pCOM1, @pCOM2
set @currID= @pID
WHILE @@FETCH_STATUS 0
BEGIN
Set @COMM = @COMM + @pCOM1 + @pCOM2
FETCH NEXT FROM cur_coms into @pID, @pCOM1, @pCOM2
if @currID @pID
begin
update NEWNAMESTABLE set comment = @COMM
set @COMM = ''
end
END
close cur_coms
deallocate cur_coms
this wasn't even working goes two rows and ended. So i saved is to a file using scripting.filesystem and SP_OA to save the update statements.
and attemped running it with SQLCMD and OSQL left it about 1 day and nothing happened.
3. INSERT with an user defined function to get the comments data and request it by the time the insert is done. it's running for 2 days now.
hope this can give more hints.
Regards
Jorge
June 28, 2009 at 11:54 pm
instead of using a cursor try this example code below.
drop table finaltbl
drop table comments
create table comments
(id varchar(10),
comm1 varchar(50),
comm2 varchar(50))
insert into comments values(1, 'COM1', 'COM2')
insert into comments values(1, 'COM3', 'COM4')
insert into comments values(1, 'COM5', 'COM6')
insert into comments values(2, 'COM1', 'COM2')
insert into comments values(2, 'COM3', 'COM4')
insert into comments values(3, 'COM1', 'COM2')
select * from comments
SELECT c1.id,
(SELECT comm1 + '+' + comm2 + '+'
FROM comments c2
WHERE c2.id = c1.id
ORDER BY comm1, comm2
FOR XML PATH('')) AS comms
FROM comments c1
GROUP BY id ;
create table finaltbl
(id int identity(1,1),
priorID int,
firstname varchar(50),
lastname varchar(50),
date datetime default getdate(),
comment varchar(500))
insert into finaltbl (priorID, comment)
SELECT c1.id,
left((SELECT comm1 + '+' + comm2 + '+'
FROM comments c2
WHERE c2.id = c1.id
ORDER BY comm1, comm2
FOR XML PATH('')),len((SELECT comm1 + '+' + comm2 + '+'
FROM comments c2
WHERE c2.id = c1.id
ORDER BY comm1, comm2
FOR XML PATH('')))-1) AS comms
FROM comments c1
GROUP BY id ;
select * from finaltbl
drop table comments
drop table finaltbl
Change the code according to your working database. You may have to look at the indexes on the tables to make it work fast.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply