實務上我很排斥使用cursor來處理row by row。
Cursor會大量耗用網路和SERVER資源，當然很多人都建議使用while loop取代cursor(參考[SQL SERVER][TSQL]取代Cursor操作)，但最近深入測試才知道while loop骨子裡也是使用cursor，只是while loop沒有明確宣告而已(參考[SQL SERVER]優化Cursor)，因為使用cursor處理資料邏輯，可讀性很高，而且邏輯也夠直覺且容易理解，造成cursor遍地開花，但SQL Server已經發展到SQL2016了，面對某些row by row需求，其實你可以有更好的選擇，下面我簡單示範一下。
我拿之前例子比較效能 ([SQL SERVER][TSQL]刪除自動建立統計值)
需求:建立刪除資料表自動建立統計值陳述句
方法1: 使用cursor串連
select GETDATE()
go
--使用cursor串連
declare
@owner varchar(100),
@tname varchar(500),
@sname varchar(100),
@s nvarchar(max) = N'';
declare autostatistics cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
for
select sch.name as 'Owner',obj.name as '資料表名稱',st.name as '統計值名稱'
from sys.stats st
inner join sys.objects obj
on st.object_id=obj.object_id
JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
where st.stats_id > 0
and st.stats_id < 255
and st.auto_created=1
and object_name(obj.object_id)
in
(
'PrefetchA',
'ScrapReason',
'PrefetchB',
'Shift',
……
)
open autostatistics;
fetch next from autostatistics into @owner, @tname, @sname;
while @@FETCH_STATUS = 0
begin
set @s += CHAR(13) + CHAR(10) + N'drop statistics '
+ +QUOTENAME(@owner) + '.'
+ QUOTENAME(@tname) + '.'
+ QUOTENAME(@sname) ;
fetch next from autostatistics into @owner, @tname, @sname;
end
close autostatistics;
deallocate autostatistics;
--print @s
select GETDATE()
go
select DateDiff(MS,'2016-07-10 17:32:30.663','2016-07-10 17:32:31.030')
執行3次平均=(456+367+420)/3=
方法2: 使用XML 串連
select GETDATE()
go
--使用XML 串連
declare @s nvarchar(max) = N'';
select @s =
(
select CHAR(13) + CHAR(10) + 'drop statistics '
+ QUOTENAME(o) + '.'
+ QUOTENAME(t) + '.'
+ QUOTENAME(s)
from
(
select o=sch.name,t=obj.name,s=st.name
from sys.stats st
inner join sys.objects obj
on st.object_id=obj.object_id
JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
where st.stats_id > 0
and st.stats_id < 255
and st.auto_created=1
and object_name(obj.object_id)
in
(
'PrefetchA',
'ScrapReason',
'PrefetchB',
'Shift',
……..
)
) AS x
FOR XML PATH(''), TYPE
).value('.[1]', 'nvarchar(max)');
--print @s
select GETDATE()
go
select DateDiff(MS,'2016-07-10 17:40:19.320','2016-07-10 17:40:19.723')
執行3次平均= (403+377+365)/3
方法3: 使用直接串連
select GETDATE()
go
--使用直接串連
declare @s nvarchar(max) = N'';
SELECT @s += CHAR(13) + CHAR(10) + 'drop statistics '
+ QUOTENAME(o) + '.'
+ QUOTENAME(t) + '.'
+ QUOTENAME(s)
FROM
(
select o=sch.name,t=obj.name,s=st.name
from sys.stats st
inner join sys.objects obj
on st.object_id=obj.object_id
JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
where st.stats_id > 0
and st.stats_id < 255
and st.auto_created=1
and object_name(obj.object_id)
in
(
'PrefetchA',
'ScrapReason',
'PrefetchB',
'Shift',……
)
) AS x;
--print @s
select GETDATE()
go
select DateDiff(MS,'2016-07-10 17:45:44.493','2016-07-10 17:45:44.837')
執行3次平均= (344+343+427)/3
方法4: 使用while loop
select GETDATE()
go
--使用while loop
declare @result table
(
serial int identity(1,1),
owner varchar(100),
tname varchar(500),
sname varchar(100)
)
insert into @result
select sch.name as 'Owner',obj.name as '資料表名稱',st.name as '統計值名稱'
from sys.stats st
inner join sys.objects obj
on st.object_id=obj.object_id
JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
where st.stats_id > 0
and st.stats_id < 255
and st.auto_created=1
and object_name(obj.object_id) in
(
'PrefetchA',
'ScrapReason',
'PrefetchB',
'Shift',……
)
declare @step int
declare @s nvarchar(max) = N''
set @step=1
while @step <= (select MAX(serial) from @result)
begin
select @s += CHAR(13) + CHAR(10)
+ N'drop statistics ' + QUOTENAME(owner) + '.'
+ QUOTENAME(tname) + '.'
+ QUOTENAME(sname)
from @result
where serial = @step;
set @step = @step + 1;
end
--print @s
select GETDATE()
go
select DateDiff(MS,'2016-07-10 17:55:07.893','2016-07-10 17:55:08.613')
執行3次平均= (720+246+386)/3
結果:while loop 並沒有較省時間(優化過的cursor反而還比較快)，XML或直接串連是較快
|
方法 |
使用cursor串連 |
使用XML 串連 |
使用直接串連 |
使用while loop |
|
平均時間(ms) |
414.3 |
381.6 |
371.3 |
450.6 |
參考
