[SQL Server] 如何使用執行計畫(execution plan)判斷statement效能

 前言：

提到資料庫存取，大多會擔心撰寫的statement是否有效能問題，

雖然公司可能會有DBA來review開發人員的statement，

但開發人員仍需初步判斷所寫的statement執行的狀況。

以SQL Server來說，會使用SSMS(SQL Server Management Studio)裡的執行計畫(Execution plan)來判斷。

作法：

可以準備幾個要比較statement來跑執行計畫，一般會先看是否有吃到index，

效能通常是：index seek > index scan

但如果SELECT的資料筆數接近整張table的筆數，SQL Server就可能採table scan將資料回傳。

基本上使用PK來當條件，就會套用cluster index seek。cluster index預設是從table的PK建出來的，每張table只會有一組，如下圖OrderID是[Orders]的唯一PK。

還有一種是nonclustered index seek，nonclustered index在一張table可以有多組，可以視SELECT資料的需求來建立。如下圖CustomerID是[Orders]的其一nonclustered index。

至於index scan，可分為cluster index scan，如下圖。

另一種則是noncluster index scan。

to be continued...

參考資料：

https://docs.microsoft.com/zh-tw/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15

https://docs.microsoft.com/zh-tw/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15

https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs

[Design Pattern] 狀態模式(State Pattern)

前陣子開發電子化表單相關的程式，剛好使用到狀態模式(State Pattern)。

需求是每張表單有其狀態，每個狀態有對應的處理動作，

因此直覺上可以使用狀態模式的概念來處理。

先上個類別圖：

這邊使用抽象類別StateBase，下方的類別就是實際的狀態，

以請假表單來說，會有初始狀態與主管審核後狀態，

Execute方法代表執行狀態對應的動作，可能是狀態移轉或通知對應的人。

而StateContext是管理狀態的類別，其Request方法呼叫state物件的Execute方法，

並注入執行的state instance，以提供狀態執行所需的環境資訊。

剛好這需求，一開始同事是使用switch case來處理各狀態行為，

我改成狀態模式，避免全部的狀態行為都擠在同一個方法，

後續擴充維護也會容易許多，這也是Open–closed principle的精神。

[Web] OAuth Authorization code flow筆記與取得google userinfo範例

 

Authorization Code是OAuth的其中一種授權方式，

可以讓client程式取得使用者(resource owner)外部的資源，需要使用者輸入帳密，

流程如下：

● 觸發驗證流程後，client程式將使用者的瀏覽器導向authorization server的驗證端點(authorize endpoint)。

● 此時網頁會顯示client程式想存取使用者在外部的哪些資源，等待使用者授權。

● authorization server依使用者回應與帳密決定是否授權。

• 若使用者允許，則authorization server會依client程式註冊OAuth服務時填寫的redirection URI，將authorization code導回給client程式。
• 若使用者不允許或無法成功授權，則將錯誤訊息導回client程式。

● client程式再帶authorization code、redirection URI等參數POST到authorization server的權杖端點(token endpoint)。

● authorization server確認參數無誤後，即回傳access token相關資訊，即可以此token跟resource server要資料。

以Flow chart描述流程如下：

來個實際操作吧，這邊使用Google OAuth來取得userinfo：

• 先到google developer console的Credentials頁面，新增OAuth client ID，至少要給redirect URIs，儲存後就會拿到ClientID與Secret。
• 組合取得Authorization code的url，紅色部份依實際狀況填入：

https://accounts.google.com/o/oauth2/v2/auth?

client_id=client id from google oauth setting&

redirect_uri=redirect_uri from google oauth setting&

response_type=code&

scope=profile&

access_type=offline&

state=up to you

• GET呼叫取得Authorization code的url，google server會callback Authorization code，code通常是4%2F開頭，要注意有url encode，所以要轉為4/。
• 接著POST呼叫要token，code就帶Authorization code，通常超過10分鐘會失效，如下圖，即可取得欲存取資源的token。
  
  
  
  
  
  
• 最後拿token即可存取授權的資源：

參考資料：

https://tools.ietf.org/html/rfc6749

https://developers.google.com/identity/protocols/oauth2/web-server