第25個冬天

  有一張table資料量累積十分龐大 跨年度累積量很可觀 那在耗時查詢上 程式碼: SET STATISTICS IO ON ; SET STATISTICS TIME ON ; dbcc dropcleanbuffers --clear buffer SELECT * FROM [AdventureWorks2014].[Sales].[SalesOrderHeader] SET STATISTICS IO OFF ; SET STATISTICS TIME OFF ; 經過時間 = 420 ms左右 當資料庫中某個table的資料量爆多，在查詢資料時候會明顯感覺到速度很慢 此時可以考慮分區表 分區表是自SQL Server2005以後出現的功能，這特性允許把邏輯層面的一個table 在物理層面可分為多個部分。 Partition Table可以就物理層面去將一個大表分成幾個小表，但從邏輯層面看，還是原先那個單一個大表。(換言之，你程式碼不會有需要改動。) 白話而言，資料會是分段的存儲，較常使用的技法是透過年分(建立的日期)，對於特定當年資料做CRUD的操作。而對於較舊資料幾乎不做寫入操作只進行唯讀查詢。 這類情境可能就適合Partition Table的功用。 若對於資料操作只涉及到一部分資料而不是全部資料情況可以考慮透過分區表，若一張資料表中的data經常不管年分之類的甚至很頻繁有增刪改查操作就建議最好不要分區。 通常隨著資料增長，只使用單一個資料庫文件存放的弊端就會慢慢浮上檯面。 因此若使用多個文件分布資料到多個硬碟中可以集大提高I/O效能 再來就是對於備份和復原等操作也會輕鬆一些，尤其針對資料量略大的DB。 分區表:就邏輯層面仍算同一個(同一個檔案組)和多個的(不同檔案組) 較為透明可見的 分表:物理上分區(clone出來,因此在邏輯層面看算多個,物理層也算多個) DB中分區其實分為 水平分表(Horizontal Partitioning):一張資料表的資料分成多個表(結構不變) 垂直分表(Vertical partitioning):將一張資料表按照欄位分成不同的表(結構發生改變) 大部分都優先推使用水平分區如此一來程式碼會比較不會有需要更動的地方 分區的意義在於去將大量資料從物理上切分為幾個互相獨立的小部分。 ...

由於近期工作專案 有一段涉及到By 系統管理員資料表(只有工號) 去和 公司員工表(有工號、姓名) 兜出完整的姓名跟工號組合 情況: common..comper 隸屬於common DB的員工資料表(8萬筆) ndamgrsys..ndard_sysmgr_owner 系統管理者表(5~10筆) 因此一開始很直覺的寫了這麼一段SQL查詢 SQL查詢語句 1 select * from common..comper where com_empno IN ( select nso_empno from ndard_sysmgr_owner where nso_role= 'busMgr' ) 邏輯是正確的但是效能查詢上並不優 我從一張大約有8萬筆資料的員工表中來去每一筆做子查詢比對 所以如果 Record有1000筆，則in的子查詢需要跑1000次 Record有1000ㄢ筆，則in的子查詢需要跑10000次... 會將每一筆資料都去比in裡面的子查詢 SQL查詢語句(優化後) 1 2 3 select so.nso_empno,emp.com_cname from ( select nso_empno from ndamgrsys..ndard_sysmgr_owner where nso_role= 'busMgr' ) as so inner join ( select com_empno,com_cname from common..comper ) as emp ON so.nso_empno = emp.com_empno 改為從小的結果集來做join 就可以觀察到SQL查詢成本足足少了原來的三分之二左右 原本是整本字典從第一頁翻到最後一頁地毯式搜尋改為透過查部首、筆畫之類的索引 來加速查找到符合特定幾頁的條件(比方A開頭的英文單字....捨麼部首的....) 參考: https://stackoverflow.com/questions/1200295/sql-join-vs-in-performance https://ithelp.ithome.com.tw/questions/10155255 https://dotblogs.com....