[SQL] 大量匯入與索引

在大量匯入資料時，會建議停用非叢集索引，待資料匯入完成後再啟用非叢集索引，rebuild 非叢集索引即為啟用

停用索引

SSMS => 目標 Table => 索引 => 全部停用

停用索引頁面會列出索引清單，但不可以停用叢集索引，原因在於停用叢集索引就無法對 Table 進行存取。在下圖索引清單按 delete 鍵刪除後，再透過指令碼產生語法來執行

透過指令碼產生的停用索引語法，語法如下

ALTER INDEX [IX_BulkInsertDemo] ON [dbo].[BulkInsertDemo] DISABLE
GO

在 SSMS 內操作停用叢集索引，會有下圖警告訊息

 

匯入測試

準備大約 120 萬筆資料來進行測試，測試非叢集索引是否停用情況下的效能差異，bulk insert 語法如下

BULK INSERT DBName.dbo.BulkInsertDemo
FROM 'D:\Data.txt'
WITH
(
    BATCHSIZE = 10000,
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n',
    TABLOCK,
    KEEPNULLS
)

測試結果

 

非叢集索引狀態	大量匯入時間
啟用	約 90 秒
停用	約 20 秒

啟用索引

一開始以為 alter index 會有個參數 enable 可以使用，後來查官方文件才知道，所謂的啟用，其實就是 rebuild Index

透過 sys.indexes 可以查到被停用的索引

SELECT
	name ,
	type_desc ,
	is_disabled
FROM sys.indexes
WHERE is_disabled = 1

SSMS 內有 [全部重建] 可以產生對應指令碼，基本上操作同 [全部停用]，就不截圖說明囉

rebuild 語法如下

ALTER INDEX [IC_BulkInsertDemo] ON [dbo].[BulkInsertDemo] REBUILD
GO

• 參考資料
• 停用索引和條件約束