SQL 的 while exists

在 MS SQL 裡，盡量避免用 cursor 去處理迴圈的運算，你也許聽人說過。不管是考量效能問題也好，還是恐嚇你也好，但...下一步呢？你的問題還是需要去解決。有些人可能會提議說，那，就建立一個暫存資料表，處理過一筆資料後，就刪除他，直到這資料表沒資料為止。這真的是一個簡單好方法，可是，你有考慮過效能問題嗎？我們來做個實驗。

想要推薦的方法，是使用 while exists （ ... ） 來解決。

while exists 範例：

declare @seqsn bigint
set @seqsn=682

declare @id bigint
select  top 1 @id=gcf_id from prog_checkpoint_first 
where gcf_seqsn=@seqsn  order by gcf_id

--Do Your Job here
print @id

set @id=@id+1
while exists (select * from prog_checkpoint_first 
where gcf_seqsn=@seqsn and gcf_id>=@id)
begin

 --Do Your Job here
 select  top 1 @id=gcf_id from prog_checkpoint_first 
 where gcf_seqsn=@seqsn and gcf_id>=@id  order by gcf_id

 print @id

 set @id=@id+1

end

使用暫存資料表的方法：

declare @seqsn bigint
set @seqsn=682

declare @id bigint


select  gcf_id into #tmp from prog_checkpoint_first  
where gcf_seqsn=@seqsn  

while (select count(*) from #tmp)>0
begin
 select top 1 @id=gcf_id from #tmp 
 
 --Do Your Job here
 print @id

 delete #tmp where gcf_id = @id
end
drop table #tmp

我接著在自己的資料庫上建了一個測試資料表：prog_checkpoint_first

CREATE TABLE [dbo].[prog_checkpoint_first](
 [gcf_id] [bigint] IDENTITY(1,1) NOT NULL,
 [gcf_seqsn] [bigint] NOT NULL,
 CONSTRAINT [PK_prog_checkpoint_first] PRIMARY KEY CLUSTERED 
(
 [gcf_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

緊接著，就是新增測試資料。我新增了2萬筆資料進去，然後分別執行「while exists 範例」與「使用暫存資料表的方法」

declare @count bigint
set @count=1

while @count<=20000
begin
 insert into prog_checkpoint_first (gcf_seqsn) values (682)
 set @count=@count+1
end

最後觀察這兩種方式的執行時間。
「while exists 範例」：2 秒
「使用暫存資料表的方法」：34秒

這結果，真的差很大。所以，當你有需要透過 SQL去處理迴圈問題，如果可預期資料量很大，那會建議使用 while exists 的寫法。如果資料量很小，則用那種都好。