[SQL] 資料切割或資料減肥的方式?

資料庫 ms sql 資料切割

恩恩 2018-08-22 14:44:17 ‧ 7983 瀏覽

分享至

目前有一個資料表非常大，已經有26 G的容量，想要切隔舊有的資料出來，於是
研究了SQL資料切割，實際執行後才發現公司的SQL版本是標準版，而不是企業版(Enterprise)，無法使用Partitioning功能...

想請教有經驗的大家，有那些方式，可以將資料量很大的TABLE 切割出來，提升資料
查詢的效能，當稽核要往前查看資料時，再還原切割出來的資料，還請指點，謝謝!!

PS:已經有執行過資料庫壓縮和LDF檔減肥了，但MDF檔案還是有26G

看更多先前的討論...收起先前的討論...

純真的人 iT邦大師 1 級 ‧ 2018-08-22 15:07:55 檢舉

可能要用歷史資料了~不過要改程式跟SQL資料時間分野~
例如2018年以前(丟到歷史資料庫)~
2018年目前資料庫

恩恩 iT邦新手 5 級 ‧ 2018-08-22 15:22:44 檢舉

因為程式是廠商寫好的AP，無法改變source code...

純真的人 iT邦大師 1 級 ‧ 2018-08-22 15:29:17 檢舉

那這要強制換Server升級到Enterprise
把資料庫換成Enterprise版了...
又是一筆金額可觀的數字@@..

恩恩 iT邦新手 5 級 ‧ 2018-08-22 16:08:50 檢舉

是筆可觀的數字..沒錯

公司目前尚不願意出錢，工程師要想辦法資料表"瘦身" @@..

CalvinKuo iT邦大師 7 級 ‧ 2018-08-23 11:42:23 檢舉

唉..... 我想到EFGP(ExxyFxxw GP)資料庫的FormInstance資料表fieldValues用ntext存XML表格。
很難想像10年後FormInstance資料表有多大... 你的問題也是我未來的問題
15774資料列佔了1,317.68M(導入半年)

恩恩 iT邦新手 5 級 ‧ 2018-08-23 14:53:21 檢舉

CalvinKuo 你們還有維護合約嗎? 若有的話可以請教客服，資料切割的語法

CalvinKuo iT邦大師 7 級 ‧ 2018-08-24 12:10:30 檢舉

才剛導入半年... 該資料表也沒有海棉大所說日期欄位.
有也是要從fieldValues裡面找字串...
只能跟他們產中客訴。

恩恩 iT邦新手 5 級 ‧ 2018-08-24 13:44:44 檢舉

CalvinKuo 這客訴了，可能也石沉大海....
我是用關聯的方式，找到createdTime欄位。提供你參考!!
select * from FormInstance F where OID in (
select valueOID from LocalRelevantData LR
left join ProcessInstance P on P.contextOID=LR.containerOID
where P.createdTime between '2012/01/01 00:00:00'
and '2013/6/30 23:59:59'
)

CalvinKuo iT邦大師 7 級 ‧ 2018-08-25 15:20:20 檢舉

他們產中有舉他們自家公司的例子，140G資料FormInstance佔33G，每月大概2萬筆單據(我們只有他們10%)，但我們流程與表單相對複雜。未來封存的就開新的公司別查詢(因為有相關資料表，他們沒提供也只能找他們做，他們有舉某銀行的例子給我聽)，維護合約是得簽，因為問題還是很多。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2018-08-23 20:43:22

最佳解答

這個問題問不同職能的人會有不同的答案
管系統網路的人是一種答案(上面提到的 Partitioning...)
寫程式的人會有另一種答案

我是寫程式的
我的做法是
1.確定所有table都有類似「寫入日期」的欄位(沒有就不用往下看了)
2.每個table逐一，用寫入日期做區隔，select出要archive的資料(例:五年之前)
3.用 bcp 之類的指令將資料複製出來
4.刪除原table裡的資料(同步驟2)
5.對database做壓縮

回應 8
分享
檢舉

看更多先前的回應...收起先前的回應...

恩恩 iT邦新手 5 級 ‧ 2018-08-24 13:43:18 檢舉

感謝海綿大大的回應，我也是寫程式派的，所以我使用了你說的bcp的指令。

但目前碰到一個瓶頸...就是日期指令該如何下??
日期我下單引號，會有格式錯誤...

下雙引號，有過，但出現的是bcp的參數說明??

再麻煩海綿大大指導，謝謝!!

海綿寶寶 iT邦大神 1 級 ‧ 2018-08-24 14:46:36 檢舉

指導不敢當
我手邊沒有SQL Server,抱歉沒法測試

依據這篇來看
你的bcp語法沒錯
建議先在query analyzer確定select ..between..23:59:59)是正確的
再貼過來bcp這邊使用(改改單雙引號之類的)

另外補充一點建議
先試個幾筆
要確定匯出來的檔案可以匯回去
再大刀闊斧
否則
我就變成資料殺手的罪人了

CalvinKuo iT邦大師 7 級 ‧ 2018-08-25 15:27:08 檢舉

資料庫先備份，或者把資料還原到測試資料庫，測試bcp匯入匯出資料表。畢竟上線系統也要挑離峰時間做分割作業。

恩恩 iT邦新手 5 級 ‧ 2018-08-27 11:16:54 檢舉

海綿寶寶:
..query analyzer確定select ..between..23:59:59)是正確的

=>這邊我確認過沒問題，但貼到bcp時，不論是單引號或雙引號，怎麼改都無法成功，查資料到一種眼睛痛的程度...
如你說的匯入測試，在匯入中也遇到很多困難，因為資料格式本身有xml的資料，匯入在判斷資料的過程，也是無奈
目前有想放棄bcp的語法。

CalvinKuo
如上。困難重重之下，可能考慮直接資料庫匯出測試資料庫。

海綿寶寶 iT邦大神 1 級 ‧ 2018-08-27 14:28:38 檢舉

如果您生得出另一部 SQL Server 的話
(配備等級不限，只是放資料而已)
可以考慮將資料「切」過去那部 SQL server 冰起來

我的最後一招
就是架一部 MariaDB 來存放資料
不過我先聲明
我沒有實際經驗 SQL Server <-> MariaDB 的資料互轉
一切只是想像

恩恩 iT邦新手 5 級 ‧ 2018-08-28 09:07:57 檢舉

海綿寶寶我目前就是走這一步，找一台主機安裝SQL SERVER，使用匯出精靈的功能將資料匯到這台SQL SERVER，再將原資料刪除。原本以為有寫程式或其他更好的方式，但試了很久，爬了無數的文後，決定走回這條路。感謝你一路的相隨，呵呵~

海綿寶寶 iT邦大神 1 級 ‧ 2018-08-28 11:23:04 檢舉

問題解決就好
沒幫上什麼忙

qpowjohn iT邦新手 4 級 ‧ 2021-12-01 22:24:21 檢舉

針對你第一張圖
' :會失效
" :也會失效
'' :可執行

不小心看到這篇，供後來者參考

登入發表回應

做工仔人!

iT邦大師 1 級 ‧ 2018-08-23 17:10:31

有一個做法:
1.新增一家公司(營運中心) .
2.將舊資料(如2017年以前且完成作業的資料) INSERT 到新的營運中心.但是TABLE NAME 要加一個固定字元. 如 COPTA_O 或 INVTA_O
3.再新的營運中心建 VIEW : SELECT * FROM 原營運中心.COPTA UNION ALL SELECT * FROM COPTA_O
4.再將原營運中心的資料刪除.
5.正常作業:在原營運中心做, 舊資料查詢:在新營運中心做.

簡而言之:
新營運中心沒有COPTA的TABLE , 只有 COPTA的VIEW , 但是COPTA這個VIEW是舊資料(過時資料)與現行資料UNION 起來的.