[SQL Server][T-SQL]Sequence序號使用時的效能

2017-05-11

3773
0
T-SQL

下午協助同事抓SQL資料庫效能問題，暫時沒有頭緒，不過碰上了已經在記憶體中忘光的SQL 2012新功能(對不起老師): 順序物件(Sequence)，來筆記，順便觀察快取設定對取號效能的影響。

重新查了一下順序物件的用法，還蠻適合使用在要號的邏輯中，話不多說，直接在Tempdb開三個順序物件(seq1,seq2,seq3)，並且新增三個資料表t1,t2,t3。

seq1設定no cache
seq2設定cache 100
seq3使用預設

use tempdb
create sequence seq1
     as int
     start with 1
     increment by 1
     no cache;

create sequence seq2
     as int
     start with 1
     increment by 1
     cache 100;

create sequence seq3
     as int
     start with 1
     increment by 1;

create table t1(c1 int)
create table t2(c1 int)
create table t3(c1 int)

NO CACHE: 依序寫入1,000筆資料3次:

declare @start datetime = getDate();
declare @seq int = 0;
declare @i int = 1;

while @i < 1000 begin
select @seq = next value for seq1
     insert into t1 VALUES(@seq)
     set @i += 1;
end;

declare @end datetime = getDate();
declare @diff int = datediff(ms, @start, @end);
print @diff;

GO 3

執行結果:

CACHE 100: 依序寫入1,000筆資料3次:

declare @start datetime = getDate();
declare @seq int = 0;
declare @i int = 1;

while @i < 1000 begin
select @seq = next value for seq2
     insert into t2 VALUES(@seq)
     set @i += 1;
end;

declare @end datetime = getDate();
declare @diff int = datediff(ms, @start, @end);
print @diff;

GO 3

執行結果:

預設CACHE: 依序寫入1,000筆資料3次:

declare @start datetime = getDate();
declare @seq int = 0;
declare @i int = 1;

while @i < 1000 begin
select @seq = next value for seq3
     insert into t3 VALUES(@seq)
     set @i += 1;
end;

declare @end datetime = getDate();
declare @diff int = datediff(ms, @start, @end);
print @diff;

GO 3

執行結果: