[SQL] Index and Update

• Update 語法中有 where 的搜尋條件，因此會使用到相關索引的 "seek" 或 "scan"，找出相關資料在進行更新。所以如同 "Select" 查詢一樣，"where" 條件將會決定某些 "index" 是否能快速找尋相關資料為重要因素
• CASE 1
• Update 對 Heap 與 Clustered 影響比較
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Person.Person 創建 2 個 table 如下
• tbPerson01
• tbPerson02：
1. unique Clustered index (BusinessEntityID)

• 查詢使用 "index"

update tbPerson01 set FirstName = 'Stone' where BusinessEntityID in (300)
update tbPerson02 set FirstName = 'Stone' where BusinessEntityID in (300)

• 查詢不使用 "index"

update tbPerson01 set FirstName = 'Stone' where LastName in ('Chen')
update tbPerson02 set FirstName = 'Stone' where LastName in ('Chen')

• Result：
1. "Update" 的 "Where" 條件可以所用到 "index" 的 "鍵值資料行"，在尋找資料上會較為快速
2. "scan" (掃描) 與 "seek" (搜尋) 在查詢效率上的差異
• CASE 2
• index 造成額外索引維護成本
• 資料行如果剛好在 "index" 的 "鍵值資料行"或是 "include"內的資料行，此 "update"操作需將相關 "index" 一起維護，會增加額外成本
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Person.Person 創建 2 個 table 如下
• tbPerson01
1. unique Clustered index (BusinessEntityID)
• tbPerson02：
1. unique Clustered index (BusinessEntityID)
2. NonClustered index (PersonType)
3. NonClustered index (FirstName)
4. NonClustered index (LastName)

• 查詢

update tbPerson01 set FirstName = 'Stone' where BusinessEntityID in (300)
update tbPerson02 set FirstName = 'Stone' where BusinessEntityID in (300)

• 查詢結果

• Result：
1. "update" 操作只會真對受到影響的 "index" 進行維護，包括其它 "index" 的 "鍵值資料行" 以及 "index" 所內含的 "include" 資料行
2. "update" 操作所設定 "where" 條件如同 "select" 一樣，會選擇適當的 "index"來進行資料過濾
• REF：
• SQL Server 效能調校

[MVC] Admin Template

• 最近需統整後台的專案，因此找尋是否有現有的方法
• Admin Template：後臺管理的介面
• Bootstrap：用於開發 HTML、CSS 和 JS 的開源工具包。 透過我們的 Sass 和 mixins、響應式網格系統、預先建構的組件以及基於 jQuery 開發的插件來 快速建構你的想法或是應用程式
• 透過上列的條件進行尋找，發現網路上蠻多資訊，如下：
• 20 Free Bootstrap 3 Admin Dashboard Templates 2017
• 26个最佳免费HTML5/Bootstrap管理员后台界面模板下载
• 40+ BEST FREE BOOTSTRAP ADMIN TEMPLATES 2017
• 由於使用 Visual Studio 進行開發，因此在進行尋找 Visual Studio + Bootstrap Admin Template，目前找尋到兩個專案如下：

• ASP MVC 5 with sb admin 2 bootstrap template

• 使用 sb admin 套件進行開發

• 可從線上專案進行加入，此篇文章有詳細的介紹 (sb admin 2 bootstrap with ASP MVC)

• 優點：
1. 直接從範本專案直接點選，就可以產生 Admin Template
2. 選單可由 Data 直接進行變更

• 缺點：
1. 此專案最後更新時間為 2015/7/20，目前沒有進行更新
2. sb admin 此套件也是沒在進行更新

• ASP.NET MVC Application with a Custom Layout

• 使用 AdminLTE 套件進行開發

• 只能線上下載，在進行改修此專案

• 優點：
1. 此專案最後更新時間為 2016/2/10，版本是比較新
2. AdminLTE 此套件持續更新，並提供許多 js 的範例程式
• 缺點：
1. 選單並沒有獨立出來，需在另外開發
2. 因為專案，修改會需要改許多地方

[SQL] Index and Insert

• Insert 語法中並無 where 的搜尋條件，因此不會使用到相關索引的 "seek" 或 "scan"，相對不會產生成本。但需針對所有 "Index"，新增該資料的 "鍵值資料行"與相關資料行
• CASE 1
• index 對 heap 和 clustered 影響比較
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Person.Person 創建 2 個 table 如下
• tbPerson01
• tbPerson02：
1. unique Clustered index (BusinessEntityID)

• 查詢

insert into tbPerson01 (BusinessEntityID, PersonType, FirstName, LastName)
values (300, 'EM', 'Dale', 'Chen');

insert into tbPerson02 (BusinessEntityID, PersonType, FirstName, LastName)
values (300, 'EM', 'Dale', 'Chen');

• 查詢結果

• Result：
1. heap 與 clustered 資料表進行 Insert 操作，並不會造成不同操作成本，即使大量的新增，成本都是相同
• CASE 2
• index 造成額外索引維護成本
• 兩個相同為 Clustered 資料表，但其中一個有較多 NonClustered 索引數的資料表是否會產生額外成本
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Person.Person 創建 2 個 table 如下
• tbPerson01
1. unique Clustered index (BusinessEntityID)
• tbPerson02：
1. unique Clustered index (BusinessEntityID)
2. NonClustered index (PersonType)
3. NonClustered index (FirstName)
4. NonClustered index (LastName)
• 查詢

insert into tbPerson01 (BusinessEntityID, PersonType, FirstName, LastName)
values (300, 'EM', 'Dale', 'Chen');

insert into tbPerson02 (BusinessEntityID, PersonType, FirstName, LastName)
values (300, 'EM', 'Dale', 'Chen');

• 查詢結果

• Result：
1. 當資料表包含多個 "index" 時，"insert" 就必需同時維護所有 "index" 資料，所以會產生額外的維護成本
2. 執行成本估算方法如下
• "Heap" 或 單個"Clustered"，維護成本相同
• "Heap" 或單個"Clustered" 加 N個 "NonClustered"，可視為 1 + N 個 "index" 維護成本
• REF：
• SQL Server 效能調校

[SQL] JOIN - Loop Join, Merge Join, Hash Join

• Query Optimizer 會採用最佳執行計畫查詢，但會被限制在即有的條件，選擇最佳的執行計畫，需熟知每種 "Join" 使用時機，才能了解當下不良情況來進行改進，達成提升查詢效率
• 因此觀察相同查詢語法與相同資料表，下列不同所影響查詢方式
• 資料量的大與小
• 資料表之間關聯 "索引" 建立
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Sales.SalesOrderHeader 與 Sales.SalesOrderDetail 創建 2 個 table 如下

• CASE 1：
• 查詢

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
where o.SalesOrderID = 46666

• 查詢結果
• 使用 "堆積" 資料表，造成 "遺漏索引" 
• 因 SalesOrderID = 46666 ，表示少量資料查詢，採用 "Loop Join"

• CASE 2：
• 查詢

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
where o.SalesOrderID > 10000

• 查詢結果
• 使用 "堆積" 資料表，造成 "遺漏索引"，因 "堆積" 此兩資料表並無排序處理
• tbSalesOrderHeader 資料量較少，當成 "Build Input" 
• tbSalesOrderDetail 資料量較多，當成 "Probe Input"
• 因 SalesOrderID > 10000 ，表示少量資料查詢，採用 "Hash Join"

• CASE 3：
• 加入 "索引"

• 查詢

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
where o.SalesOrderID > 10000

• 查詢結果
• 因兩資料表都建立 "索引"，也會事先進行 "排序"
• 因此 Query Optimizer 採用 "Merge Join"

• Result：
1. 少量資料查詢，採用 "Loop Join" (巢狀迴圈聯結)
2. 大量資料查詢，資料表中所對應資料行並未排序，採用 "Hash Join" (雜湊比對聯結)
3. 大量資料查詢，資料表中所對應資料行已經排序，採用 "Merge Join" (合併聯結)
• REF：
• SQL Server 效能調校

[SQL] JOIN - Hash Join

• Hash Join (雜湊聯結)：輸入兩資料表分為不同角色
• Build Input (建置輸入)
• 第一階段，較小的資料表
• 掃描或計算整個 "Build Input"，依據 "間直資料行" 產生 "Hash Key" (雜湊鍵) 並建立 "Hash Table" (雜湊表)，再依據 "Hash Key" (雜湊鍵) 將每個 "Row" (資料列) 置入 "Hash Bucket" (雜湊桶)
• Probe Input (探查輸入)
• 第二階段，較大的資料表
• 掃描或逐列計算整個 "Probe Input"，針對每個 "探查輸入" 的資料列計算 "Hash Key" (雜湊鍵值)，並掃描對應至 "Hash Bucket" (雜湊桶)，並產生符合項目
• "Merge Join" 與 "Hash Join" 之間差異，什麼樣的情況適合使用 "Merge Join" 或 "Hash Join"
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Sales.SalesOrderHeader 與 Sales.SalesOrderDetail 創建 3 個 table 如下
• tbSalesOrderHeader
• unique Clustered index：SalesOrderID
• tbSalesOrderDetail01
• tbSalesOrderDetail02
• unique Clustered index：SalesOrderID, SalesOrderDetailID

• 查詢

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail01 od ON o.SalesOrderID = od.SalesOrderID

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail02 od ON o.SalesOrderID = od.SalesOrderID

• 查詢結果
• 查詢 1：沒建立 "SalesOrderID" 為鍵值資料行的索引，因此採用 "Hash Join"，若沒使用 "Hash Join" 將會增加 "排序" 作業，才能使用 "Merge Join"，這樣成本更高
• 查詢 2：建立 "SalesOrderID" 為鍵值資料行的索引，而經過排序，所以採用 "Merge Join"

• 查詢 1 中 "tbSalesOrderHeader" 與 "tbSalesOrderDetail01" 屬於 "一對多" (或稱父子關係)，因 "tbSalesOrderHeader" 資料列必定少於 "tbSalesOrderDetail01"
• 所以 "Hash Join" 中的 "Build Input" (建置輸入) 為 "tbSalesOrderHeader"，可見 "tbSalesOrderHeader" 被置放於上面的位置
• Result：
1. "Merge Join" 與 "Hash Join" 較適合大型的查詢，查詢資料較多的情況
2. "Merge Join" 適合兩資料表都有 "索引" 或是經過事先排序過的情況
3. 不適合 "Merge Join" 的情況下，將採用 "Hash Join"
• REF：
• SQL Server 效能調校

[SQL] JOIN - Merge Join

• Merge Join (合併聯結)
• 基本原則： Join 的兩個輸入資料表比對的 "資料行"，必須事先經過排序，若沒經過排序，執行過程中將會進行 "排序" 作業，才能進行 "Merge Join"
• 因此沒排序的兩資料表進行比對 "資料行"，使用 "Merge Join" 作業，查詢效率並不會比較好
• 如 "Merge Join" 輸入的兩資料表所比對 "資料行"，已經有 "B-tree" 索引取得已排序資料進行比對，這樣 "Merge Join" 查詢效率為最快速 "Merge" 演算法
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Sales.SalesOrderHeader 與 Sales.SalesOrderDetail 創建 2 個 table 如下
• tbSalesOrderHeader
• unique Clustered index：SalesOrderID
• tbSalesOrderDetail
• unique Clustered index：SalesOrderID, SalesOrderDetailID

• 查詢

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
where o.SalesOrderID between 70000 and 80000

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
where o.SalesOrderID = 70000

• 查詢結果
• 查詢 1：查詢範圍 SalesOrderID 70000 ~ 80000 (大量資料)，採用 "Merge Join"
• 查詢 2：查詢範圍 SalesOrderID 70000  (小量資料)，採用 "Loop Join"
• 此兩資料表連結是透過 "SalesOrderID"，因  tbSalesOrderHeader 的 SalesOrderID 為 "叢集索引" 的 "鍵值資料行"，而 tbSalesOrderDetail 的 SalesOrderID 為 "叢集索引" 的 "複合鍵值資料行" 的第一個位置，因此 SalesOrderID 在此兩資料行皆已排序，所以查詢1 Query Optimizer 採用 "Merge Join"

• Result：
1. 小型資料查詢採用 "Loop Join"，而 "Merge Join" 適合查詢大量資料
2. "Merge Join" 最適合兩資料行皆事先排序，最佳情況是雙方資料行皆已建立 "索引"
3. 注意：兩資料表間應存有 "Primary Key (PK)" 與 "Foreign Key (FK)" 的對應關係
• Foreign Key (FK)：會是排序過且唯一的 "叢集索引"
• Foreign Key (FK)：不一定會經過排序的 "索引"
• REF：
• SQL Server 效能調校

[SQL] JOIN - Loop Join

• Loop Join (巢狀回圈聯結) 
• 透過兩個查尋資料量(大與小)，觀察資料量對執行影響
• 驗證 "Loop Join" 的運作模式與使用的最佳時機
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Production.Product 與 Sales.SalesOrderDetail 創建兩個 table 如下
• tbProduct
• unique Clustered index：ProductID
• tbSalesOrderDetail
• unique Clustered index：SalesOrderID, SalesOrderDetailID
• NonClustered：ProductID

• 查詢

select od.SalesOrderID,
       od.SalesOrderDetailID,
       p.Name
from tbSalesOrderDetail od
join tbProduct p ON od.ProductID = p.ProductID
where p.ProductID between 770 and 773

select od.SalesOrderID,
       od.SalesOrderDetailID,
       p.Name
from tbSalesOrderDetail od
join tbProduct p ON od.ProductID = p.ProductID
where p.ProductID between 770 and 800

• 查詢結果
• 查詢 1：查詢範圍 ProductID 770 ~ 773 (小量資料)，採用 "Loop Join" 適合小量資料查詢
• 查詢 2：查詢範圍 ProductID 770 ~ 800 (大量資料)，採用 "Merge Join"
• tbProduct 與 tbSalesOrderDetail 屬於 "一對多" 的關係，tbProduct 資料量必小於 tbSalesOrderDetail ，查詢 1的 "Loop Join" 是使用 tbProduct 當 "外部輸入" 而 tbSalesOrderDetail  當 "內部輸入"

• Result：
1. "Loop Join" 中的 "外部輸入" 資料列應該較少，而 "內部資料" 資料列較多，在 "外部輸入" 讀取資料後至 "內部資料" 找尋符合的資料列
2. "Loop Join" 適合查詢條件資料列較少的情況使用，否則會採用 "雜湊聯結" 或 "合併聯結"
• REF：
• SQL Server 效能調校