Partition06PartitionTable搭配Switch新增資料

在上一篇Partition05PartitionTable新增資料效能測試有提到使用PartitionTable會降低新增資料的效能(刪除與更新也會)，之前在Partition04PartitionTable搭配Switch刪除資料有示範如何搭配Switch刪除資料，而本篇則要示範在搭配Switch可以新增資料，處理PartitionTable新增資料效能低落的問題。

--1.建立測試的Table，一個為PartitionTable，一個是非PartitionTable，要放置轉入資料的Table

IF OBJECT_ID('InsertTable') IS NOT NULL

BEGIN DROP TABLE InsertTable END

CREATE TABLE InsertTable

(

ChineseYM INT ,

tName varchar(50) ,

tOther [varchar](30)

)

ON [PRIMARY];

GO

ALTER TABLE InsertTable

ADD CONSTRAINT [CK_ChineseYM] CHECK (ChineseYM >= 10008 AND [ChineseYM]  IS NOT NULL);

GO

ALTER TABLE [dbo].[InsertTable] CHECK CONSTRAINT [CK_ChineseYM]

GO

--2.新增測試資料

INSERT INTO InsertTable

SELECT  * FROM  [TestTable]

WHERE ChineseYM=10008

GO

--3.檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM InsertTable

執行結果：

--4.使用Switch轉入資料到PartitionTable

ALTER TABLE InsertTable  SWITCH TO  ArchivedTable PARTITION 3;

GO

--5.檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM InsertTable

執行結果：

結論：在PartitionTable使用一個新的Table並搭配Switch可以處理PartitionTable新增資料時造成效能低落的問題。

Partition05PartitionTable新增資料效能測試

在上一篇Partition04PartitionTable搭配Switch刪除資料有提到在PartitionTable刪除資料的方法，而這篇測試主要是測試PartitionTable對於資料異動(Insert)時所帶來的影響。

--1.建立測試的Table，一個為PartitionTable，一個是非PartitionTable

IF OBJECT_ID('ArchivedTable') IS NOT NULL

BEGIN

   DROP TABLE ArchivedTable

END

CREATE TABLE [dbo].[ArchivedTable](

        [ChineseYM] [int]  ,

        [tName] [varchar](50) ,

        [tOther] [varchar](30)

) ON ChineseYearScheme (ChineseYM);

IF OBJECT_ID('NonPartitionTable') IS NOT NULL

BEGIN

   DROP TABLE NonPartitionTable

END

CREATE TABLE [dbo].[NonPartitionTable](

        [ChineseYM] [int]  ,

        [tName] [varchar](50) ,

        [tOther] [varchar](30)

) ON [PRIMARY];

--清除CACHE

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

--2.測試新增資料時的效能

INSERT INTO NonPartitionTable

SELECT  * FROM  [TestTable]

GO

執行結果：花費1分09秒

INSERT INTO ArchivedTable

SELECT  * FROM  [TestTable]

GO

執行結果：花費2分59秒

結果比較：

PartitionTable	花費2分59秒
非PartitionTable	花費1分09秒

結論：PartitionTable降低了Insert的效能，因為在新增資料時多了判斷資料要歸類到哪一個Partition，所以造成效能上的低落，這是很多人共有的問題。使用Switch可以解決Insert的效能問題，下一篇我將為大家示範如何解決。

Partition04PartitionTable搭配Switch刪除資料

在Partition03PartitionTable搭配Switch轉移資料討論到使用Partition Table搭配Switch可將資料從原本的Table轉移到另一個Table，本篇則是上一篇的應用本，在Partition Table使用使用Switch搭配Truncate Table提升刪除大量資料的效能。

--1.觀看資料筆數

SELECT COUNT(*) FROM ArchivedTable

GO

執行結果：

--2.建立要放置轉出資料的Table

IF OBJECT_ID('TruncateTable') IS NOT NULL

BEGIN

   DROP TABLE TruncateTable

END

CREATE TABLE TruncateTable

(

ChineseYM INT ,

tName varchar(50))

ON [PRIMARY];

GO

ALTER TABLE [dbo].[TruncateTable]  WITH CHECK ADD  CONSTRAINT [CK_TruncateTable] CHECK  (([ChineseYM]>=(10001) AND [ChineseYM]  IS NOT NULL ))

GO

ALTER TABLE [dbo].[TruncateTable] CHECK CONSTRAINT [CK_TruncateTable]

GO

--3.檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM TruncateTable

執行結果：

-- 4.將資料從ArchivedTable轉移到TruncateTable

ALTER TABLE ArchivedTable  SWITCH PARTITION 3 TO TruncateTable ;

GO

--5.檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM TruncateTable

執行結果：

--6.刪除資料

TRUNCATE TABLE TruncateTable

GO

執行結果：

--7.再次檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM TruncateTable

GO

執行結果：

-- 8.將PARTITION 3轉回給ArchivedTable

ALTER TABLE TruncateTable   SWITCH TO ArchivedTable PARTITION 3  ;

GO

--9.測試使用DELETE刪除資料

DELETE ArchivedTable

WHERE ChineseYM=10008

執行結果：

比較結果：

PartitionTable	Switch搭配Truncate Table	4ms
NonPartitionTable	Delete	2079ms

結論：發現在PartitionTable使用Switch搭配Truncate Table比不使用PartitionTable直接Delete效能快501倍左右，所以PartitionTable對於刪除大量資料在效能上有顯著的提升。

下一篇我會測試與說明PartitionTable對於新增資料效能的影響。

Partition03PartitionTable搭配Switch轉移資料

在上一篇Partition02建立PartitionTable介紹如何建立Partition Table後，這篇要介紹Partition Table搭配Switch轉移資料。

Partition Table搭配Switch可將資料從原本的Table轉移到另一個Table，這是一個很實用而且常用到的範例，熟悉如何使用Partition Table轉移資料後，之後對於使用Partition Table新增資料或刪除資料可以更得心應手，如果要做好DBA的工作熟悉Partition Table的轉換資料機制是不可或缺的技巧。

--1.觀看原本的資料

Use Performance

GO

SELECT * FROM ArchivedTable

GO

執行結果：

--2.建立要放置轉出資料的Table

IF OBJECT_ID('TruncateTable') IS NOT NULL

BEGIN

   DROP TABLE TruncateTable

END

CREATE TABLE TruncateTable

(ChineseYM INT PRIMARY KEY,

tName varchar(50))

ON [PRIMARY];

GO

--3.將資料從ArchivedTable轉移到TruncateTable

ALTER TABLE ArchivedTable  SWITCH PARTITION 3 TO TruncateTable ;

GO

--4.1檢查Partition的內容

SELECT $PARTITION.ChineseYearPartitions(ChineseYM) AS [Partition Number], COUNT(*) AS total

FROM ArchivedTable

GROUP BY $PARTITION.ChineseYearPartitions(ChineseYM)

ORDER BY $PARTITION.ChineseYearPartitions(ChineseYM)

COMPUTE SUM (COUNT(*))

執行結果：

--4.2檢查TABLE內容

SELECT * FROM ArchivedTable

SELECT * FROM TruncateTable

執行結果：

--5.將資料從TruncateTable轉入ArchivedTable，因為沒有CONSTRAINT，再轉換資料時ArchivedTable不知道正確的資料範圍

ALTER TABLE TruncateTable   SWITCH TO ArchivedTable PARTITION 3  ;

GO

/*

訊息4982，層級16，狀態1，行1

ALTER TABLE SWITCH 陳述式失敗。來源資料表'Performance.dbo.TruncateTable' 的檢查條件約束所允許的值，

於目標資料表'Performance.dbo.ArchivedTable' 資料分割'3' 上定義的範圍並不允許。

*/

--6.增加CONSTRAINT，在轉換時ArchivedTable才會知道正確的資料範圍

ALTER TABLE [dbo].[TruncateTable]  WITH CHECK ADD  CONSTRAINT [CK_TruncateTable] CHECK  (([ChineseYM]>=(10001)))

GO

ALTER TABLE [dbo].[TruncateTable] CHECK CONSTRAINT [CK_TruncateTable]

GO

-- 在一次將資料從TruncateTable轉入ArchivedTable

ALTER TABLE TruncateTable   SWITCH TO ArchivedTable PARTITION 3  ;

GO

--7.檢查Partition的內容

SELECT $PARTITION.ChineseYearPartitions(ChineseYM) AS [Partition Number], COUNT(*) AS total

FROM ArchivedTable

GROUP BY $PARTITION.ChineseYearPartitions(ChineseYM)

ORDER BY $PARTITION.ChineseYearPartitions(ChineseYM)

COMPUTE SUM (COUNT(*))

執行結果：

--8.檢查TABLE內容

SELECT * FROM ArchivedTable

SELECT * FROM TruncateTable

GO

執行結果：

Partition02建立PartitionTable

介紹實作Partition Table。

在Partition01簡介有介紹Partition與其優缺點，這篇主要是介紹如何建立Partition Table。

建立Partition Table的步驟：

l   建立PARTITION FUNCTION

l   建立PARTITION SCHEME

l   建立Table並套用PARTITION SCHEME

l   新增資料並檢視資料分布

--1.建立PARTITION FUNCTION

USE Performance

GO

CREATE PARTITION FUNCTION ChineseYearPartitions (int)

AS RANGE RIGHT FOR VALUES ( 9901, 10001)

GO

ChineseYearPartitions會有三個PARTITION，如表一：

Partition1<9901

9901<= Partition2< 10001

Partition3>=10001

(表一：ChineseYearPartitions的PARTITION範圍)

--2.建立Partitioning Scheme

CREATE PARTITION SCHEME ChineseYearScheme

AS PARTITION ChineseYearPartitions

TO ([PRIMARY], [PRIMARY], [PRIMARY] )

GO

--3.建立Table時套用PARTITION SCHEME:ChineseYearScheme

CREATE TABLE ArchivedTable

(ChineseYM INT PRIMARY KEY,

tName varchar(50))

ON ChineseYearScheme (ChineseYM);

GO

--4.新增資料，此時SQL SERVER會依照PARTITION FUNCTION ChineseYearPartitions配置資料到所屬的PARTITION分區

INSERT INTO ArchivedTable (ChineseYM, tName)

SELECT 9811,'tName1' UNION ALL

SELECT 9812,'tName2' UNION ALL

SELECT 9902,'tName1' UNION ALL

SELECT 9903,'tName3' UNION ALL

SELECT 9907,'tName2' UNION ALL

SELECT 10003,'tName1' UNION ALL

SELECT 10010,'tName3'

GO

--5.檢查Partition的內容

SELECT $PARTITION.ChineseYearPartitions(ChineseYM) AS [Partition Number], COUNT(*) AS total

FROM ArchivedTable

GROUP BY $PARTITION.ChineseYearPartitions(ChineseYM)

ORDER BY $PARTITION.ChineseYearPartitions(ChineseYM)

COMPUTE SUM (COUNT(*))

執行結果：

下一篇我將介紹Partition Table如何搭配Switch轉移資料。