一. 前言:
延續前一篇 : SQL Server: Data Compare using SSDT and Visual Studio 2015 採用了 Visual Studio 2015 + SSDT 進行不同資料庫的內容比對.
該篇的資料筆數少, 看不太出來效能的問題, 故本篇將以 360 萬筆的資料進行測試.
二. 環境:
(一) Visual Studio 2015: 必須安裝 SQL Server Data Tools (SSDT), 由此下載.
(二) SQL Server 2014 測試資料: 相關的 SQL Script 可 由此下載
三. 環境建置過程:
請依以下順序建立測試環境:
1.. 00-CreateDatabase.sql
(1) 建立 2 個資料庫: IndexTestDB 及 IndexTestDB_Backup.
(2) 請預留約 1.5GB 的空間, 以建立上述2個資料庫.
2.. 01-0-CreateTable.sql
(1) 在 IndexTestDB 及 IndexTestDB_Backup 分別建立資料表 MyTable.
3.. 01-1-CreateFunction.sql
(1) 建立必要的函式 (在後續進行 INSERT 時會用到)
4.. 02-0-InsertData.sql
(1) 新增資料至 IndexTestDB.dbo.MyTable (共計 360 萬筆)
5.. 02-1-InsertData_another_db.sql
(1) 新增資料至 IndexTestDB_Backup.dbo.MyTable (共計 360萬筆)
6.. 03-Delete_another_db.sql
(1) 刪除 IndexTestDB_Backup.dbo.MyTable 的部份資料 (TxDate = 20160301)
7.. 04-Update_another_db.sql
(1) 異動 IndexTestDB_Backup.dbo.MyTable 的部份資料 (TxDate = 20160302) : 所有的 MarkXX 欄位, 全都 UPDATE 成 'Y'
8.. 05-Select_all_data.sql
(1) 將資料由 磁碟 讀取到 記憶體, 以除去後續測試由 磁碟 讀取的影響因素
(2) 執行前, 約佔 265MB; 執行後, 約佔 1.27GB
四. 測試結果:
1.. 06-Except_each_other.sql
(1) 以 TSQL 的 EXCEPT 語法, 進行差異的比對, 取得所需時間
(2) 重要的程式碼, 說明如下:
-- -------------------------------------------
-- 注意: 這裡會回傳 20,000 筆的資料;
-- 10,000筆是 IndexTestDB_Backup 刪掉的;
-- 10,000筆是 IndexTestDB_Backup 有異動的;
-- -------------------------------------------
SELECT *
FROM IndexTestDB.dbo.MyTable
EXCEPT
SELECT *
FROM IndexTestDB_Backup.dbo.MyTable
;
-- -------------------------------------------
-- 注意: 這裡會回傳 10,000 筆的資料;
-- 刪掉的, 不會被抓出來;
-- 10,000筆是 IndexTestDB_Backup 有異動的;
-- -------------------------------------------
SELECT *
FROM IndexTestDB_Backup.dbo.MyTable
EXCEPT
SELECT *
FROM IndexTestDB.dbo.MyTable
;
(3) 執行總時間為: 51 sec 左右, 如下圖:
另外, 請留意:
A. 在回傳結果方面, IndexTestDB_Backup 被刪掉的部份, 在 from IndexTestDB_Backup to IndexTestDB 的狀況下, 不會被顯示出來.
B. 在進行差異比對時, 難以得知那個欄位有被異動到, 及其值分別為何.
2.. 以 Visual Studio 2015 + SSDT 進行差異比對, 取得所需時間
(1) Visual Studio 2015 操作方式
(2) SQL Server Profiler 觀察到的結果, 花費約 88 秒
註: IndexTestDB 及 IndexTestDB_Backup 的讀取是同時進行的); 但總執行時間 (含 Visual Studio 2015 + SSDT 的處理), 約 100 秒
註: IndexTestDB 及 IndexTestDB_Backup 的讀取是同時進行的); 但總執行時間 (含 Visual Studio 2015 + SSDT 的處理), 約 100 秒
(3) Visual Studio 2015 + SSDT 的結果呈現:
A. PKEY 相同, 但內容有異者, 以粗體字強調顯示
B. 存在於來源 (即只有 IndexTestDB 才有的資料),因為先前在 IndexTestDB_Backup 刪掉了 10,000 筆資料, 所以有 10,000 筆資料被呈現出來
C. 若需進行資料同步 (from 來源 to 目標), 可以
- 執行 [產生指令碼...], 再複製/貼上到 SQL Server Management Studio 作執行 或
- 執行 [更新目標]
五. 結論 :
雖然 EXCEPT 指令的速度比較快, 但卻難以比對內容的差異; 亦無法同步來源端與目標端的資料.
當然, 需視實際狀況, 而選取不同的方式.
另外, 在 Visual Studio 2015 + SSDT 的比對過程, 發現其記憶體並沒有大量增加, 如下圖. 所以, 雖然在 SQL Server Profiler 看到的是一次 Select 的指令, 但可能在 Visual Studio 2015 有其另外的處理機制.
六. 參考文件:
2.. MSDN, EXCEPT and INTERSECT (Transact-SQL)
沒有留言:
張貼留言