SQL Server Performance Forum – Threads Archive

Execution takes lots of time???

Dear All, I have 5 table respondent_bak and respondentlov_bak, schedulegroup_bak, scheduleonetoone_bak, respondentssn_bak, quantresp_bak.
thw following tables have foreign keys based on respondent_bak tables respondent id.
respondentlov_bak, schedulegroup_bak, scheduleonetoone_bak, respondentssn_bak, quantresp_bak. Respondent table have duplicate records on lastname, officephonenumber, emailaddress and
dateofbirth columns. I have to delete duplicate records from respondent table. There is another one column lastupdated in respondent table. I need to retain max(lastupdated) record in duplicated records. Before this I have to updated the corresponding respondentid with this max(lastupdated) respondentid in related tables.
FOr this
I wrote following procedure to delete the duplicate records. But it takes lot’s of time.
Each table contains near about 3 to 4 lack records. duplicate records are near about 5000.
Please some help me on this situation. Procedure
**************** create procedure del_respondent
@respondentidvarchar(20),
@lastnamevarchar(50),
@officephonenumbervarchar(50),
@emailaddressvarchar(50),
@leaverespondentidvarchar2(20),
@leavelastupdateddatetime
as DECLARE @intErrorCode INT Declare @delrespondent table
(
respondentid varchar(20),
lastupdated datetime
) Declare c1 cursor for
select distinct lastname, officephonenumber, emailaddress, dateofbirth
from respondent_bak
where respondenttype in (2,4) Open c1 Fetch c1 into @respondentid, @lastname, @officephonenumber, @emailaddress While @@fetch_status = 0
BEGIN
BEGIN TRAN
— inserting records into table variable
insert into @delrespondent
select respondentid, lastupdated
from respondent_bak
whererespondentid = @respondentid and
lastname = @lastname and
officephonenumber = @officephonenumber and
emailaddress = @emailaddress SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — taking most resent record into variables
select top 1 respondentid, lastupdated into @leaverespondentid, @leavelastupdated
from respondent_bak
whererespondentid = @respondentid and
lastname = @lastname and
officephonenumber = @officephonenumber and
emailaddress = @emailaddress
order by lastupdated desc SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — delete respondentid from table variable which have to leave in respondent table
delete from @delrespondent
where respondentid = @leaverespondentid; SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — update respondentlov table
update respodentlov_bak
set respondentid = @leaverespondentid
where respondentid in (select respondentid from @delrespondent) SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — update schedulegroup table
update schedulegroup_bak
set respondentid = @leaverespondentid
where respondentid in (select respondentid from @delrespondent) SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — update respondentssn table
update respondentssn_bak
set respondentid = @leaverespondentid
where respondentid in (select respondentid from @delrespondent) SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — update scheduleonetoone table
update scheduleonetoone_bak
set respondentid = @leaverespondentid
where respondentid in (select respondentid from @delrespondent) SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — update quantresp table
update quantresp_bak
set respondentid = @leaverespondentid
where respondentid in (select respondentid from @delrespondent) SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — delete duplicate records from respondent table
delete from respondent_bak
where respondentid in (select respondentid from @delrespondent) SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM — delete records from table variable
delete from @delrespondent SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM Fetch c1 into @respondentid, @lastname, @officephonenumber, @emailaddress COMMIT TRAN
END
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT ‘Unexpected error occurred!’
ROLLBACK TRAN End Close c1 Deallocate c1 **************************************** Thanks in advance. If possible tell me other solution on this senario.

---

Dump duplicate 5000 rows into temp table or table variable and use this temp table for your updates and deletes… When you are deleting duplicates you can use "set rowcount 1" in deleting duplicate rows. Mohammed U.

---

]]>