[SQL SERVER][Performance]刪除重複資料效能比較 – RiCo技術農場- 點部落

–方法3

–先新增資料

declare @start time(7),@end time(7),@elaspedtime numeric(20,7) 

set @start=GETDATE() 

while 1=1

begin 

 delete top(1) from ##mytable

 where c1 in 

 ( select c1 from ##mytable 

   group by c1

   having COUNT(*)>1 );

  if @@ROWCOUNT=0 break

end 

set @end=GETDATE()

set @elaspedtime=convert(int, datediff(ms, @start, @end));

select @elaspedtime as '花費時間'

--查看結果

select * from ##mytable  

 

 

 

花費時間(ms)=20000

 

ps:每一次的刪除作業就得先在子查詢取得鍵值,直到@@ROWCOUNT=0才跳出,

這樣的做法效能奇差無比,但如果你需要一個燒機方法,這方法應該算首選…XD

 

–我的方法

–先新增資料

declare @start time(7),@end time(7),@elaspedtime numeric(20,7) 

set @start=GETDATE() 

;with mycte(c1,c2,row)

as

(

select *,ROW_NUMBER() over(partition by c1 order by c1 ) as 'row' 

from ##mytable 

)

delete mycte where row>1

set @end=GETDATE()

set @elaspedtime=convert(int, datediff(ms, @start, @end));

select @elaspedtime as '花費時間' 

--查看結果

select * from ##mytable