Msqlserver Tens Single-table data remove duplicate records using temporary tables

Because the last weekend lowercase data data repeatedly written to the database, there is no way to go heavy!

Recently used statements:

Use data

SET NOCOUNT ON
Delete Dorecordproperty from
(

Select Tid,sn,count (0) as Num,max (ID) as Maxid from Dorecordproperty with (NOLOCK) group by TID,SN have COUNT (0) >1

) p where P.tid=dorecordproperty.tid and p.sn=dorecordproperty.sn and Dorecordproperty.id<>p.maxid

SET NOCOUNT OFF

The statement was executed for 5 minutes, and there was no result, and the execution went on, and there was no estimate.

If you perform a separate

Select Tid,sn,count (0) as Num,max (ID) as Maxid from Dorecordproperty with (NOLOCK) group by TID,SN have COUNT (0) >1

9 seconds to find out the results.

Do you want to put the duplicate data in the temporary table, and then delete, the effect will be?

So there's the following statement

Use data
Go

SET NOCOUNT ON

CREATE TABLE #Tmp--Creating a temporary table #tmp
(
TID int,
SN varchar (50),
Maxid int,
Num INT
);

Insert into #Tmp (TID,SN,NUM,MAXID)
Select Tid,sn,count (0) as Num,max (ID) as Maxid from Dorecordproperty with (NOLOCK) group by TID,SN have COUNT (0) >1

Delete Dorecordproperty from
#Tmp p where P.tid=dorecordproperty.tid and p.sn=dorecordproperty.sn and Dorecordproperty.id<>p.maxid

Select COUNT (0) as num from #Tmp--query data for temporary tables
TRUNCATE TABLE #Tmp-Clears all data and constraints for the staging table
drop table #Tmp
SET NOCOUNT OFF

Sure enough, in 40 seconds, the execution was successful.

The above is on the server: CPU 2u e5-2620; memory 96G HDD is 1T enterprise level 7200 RPM; Data is operational on SQL Server 2008.

Msqlserver Tens Single-table data remove duplicate records using temporary tables