[SQL Server] 游標使用的藝術

在資料庫的設計中,用戶端程式的存取通常會扮演重要的角色,因為用戶端的數量,使用的存取方式,SQL 指令,交易處理等都會影響到資料庫應用程式的效能。我們一般在想定資料庫發生效能問題時,最有可能的幾個因素是 CPU/Memory 以及 I/O 能力,對應到用戶端的程式處理的話,通常就是 SQL 指令,連線開關以及資料的存取方式,這三種通常會具有資料庫效能的決定力。

在資料庫的設計中,用戶端程式的存取通常會扮演重要的角色,因為用戶端的數量,使用的存取方式,SQL 指令,交易處理等都會影響到資料庫應用程式的效能。我們一般在想定資料庫發生效能問題時,最有可能的幾個因素是 CPU/Memory 以及 I/O 能力,對應到用戶端的程式處理的話,通常就是 SQL 指令,連線開關以及資料的存取方式,這三種通常會具有資料庫效能的決定力。

而資料的存取方式又分好幾種,其中一種是資料庫游標 (Database Cursor,當游標存於伺服器時,即稱伺服器游標 server cursor),顧名思義,它是一種在資料庫伺服器內控制瀏覽資料列的一種記憶體暫存工具,就像 Excel 試算表那樣,可以上下左右移動,而 SQL Server 中的游標又稱為 Scrollable Cursor,也就是可以自由上下移動的游標,游標最大的好處,就是允許應用程式可在資料集 (由 SQL 指令查詢的) 中自由的移動要處理的資料列,並且對它進行處理,應用程式可以得到較大的控制權,且可以隨使用者的想法自由的在資料集的資料列間移動,而且隨著 DBMS 能力的演進,甚至可以在游標開啟時看到其他使用者修改的資料。

雖然游標這麼好用,但它卻有個很大的缺點,前面我們有提到游標是一個記憶體暫存的工具,它會暫存住目前用戶端瀏覽到的資料列的指標,而且在一個多人存取的環境下,一旦游標開太多,就會有記憶體快速消耗的問題存在,若游標又可以偵測到資料列的修改時,那麼記憶體暫存的量會更大,因此 ADO.NET 為什麼只提供 Forward-Only Cursor,即是為了避免開發人員大量開啟無謂的游標,耗盡伺服器的資源所致。不過以前使用 ADO 開發的程式,就能自由設定游標,所以有時程式的資料存取效能會很差,其中一個原因可能就是誤用了游標。

SQL Server 目前擁有四種游標

1. Forward-Only Cursor (adOpenForwardOnly = 0)

這是最簡單的游標,因為它只能單向且向前移動,不需要記錄整個資料集的相對位置,因此不但速度最快,也是最省伺服器資源的游標。也因為它只會記錄游標目前的位置,所以別的使用者的修改不會反應在這個游標上。

2. Static Cursor (adOpenStatic = 3)

這個游標會將資料集複製到 tempdb 中,並將它設為唯讀 (read-only),此時用戶端程式的查詢都會到 tempdb 中取出,記憶體不會佔用太多空間,但會耗用到 tempdb 的資源,且別的使用者的修改不會反應在這個游標上。

但如果是在 ADO 程式上,用 Client 游標的話,也可以使用這個游標,此時 ADO 會將資料集複製一份回傳給用戶端,所有的游標操作就不會在伺服器而是在用戶端,使用者可以自由修改資料集中的資料,但若資料集過大的話,傳輸的時間會花很多。

3. Dynamic Cursor (adOpenDynamic = 2)

這個游標除了會記錄除了游標位置,相對位置以及資料集區間外,也會即時偵測游標所在的位置的資料列是否有被變更,如果有的話會立即反應到游標中,算是一種即時性的游標,但也因為要記錄以及動態偵測,所以這個游標的反應是四種游標中最慢的,也是最耗系統資源的。

4. Keyset Cursor (adOpenKeyset = 1)

這個游標和 Static Cursor 很類似,它會把資料集存到 tempdb 中,但是它會額外儲存一個索引鍵欄位,每個資料列都有一個自己的索引鍵,而別的使用者若對這個資料集中的資料進行 UPDATE 時,可以立即反應到游標中,但也因為它是以索引鍵識別,所以只要是索引鍵無法反應的操作 (即 INSERT, DELETE) 都無法反應到游標上。這種游標在資源消耗上是第二高的。

由以上的說明,相信有經驗的程式設計師都會選用 Forward-Only 游標,因為在多人環境下這種游標的反應和速度是最快的,只有很少的 scenario 才會用到其他三種游標,就算是在伺服器上,也要考量 SQL Batch 的游標使用,否則不但速度會慢的驚人,資源的耗用也會非常可觀。

Reference:

MSDN ADO API Enumeration Reference
SQL Server 2008 R2 Database Engine: Cursor Type
Hitchhiker's Guide for Visual Basic and SQL Server, 6th, Traditional Chinese Edition, p.586-587