調校前端軟體 (Java、.NET、Borland) 使用，強化 SQL Server 效能

**作者：**台灣微軟資料庫講師楊志強

本頁內容

前言
瞭解前端效能調校的策略
瞭解 SQL Server 2000 強大物件
調校 Java 連結 SQL Server 2000 效能
調校 ADO 與 .NET 連結 SQL Server 2000 效能
調校 Borland 連結 SQL Server 2000 效能
結論

前言

企業該如何瞭解 SQL Server 的強大功能，進而正確使用前端系統開發工具，讓應用程式的效能最佳化，著實為下一階段開放性平台的整合重大議題。本文章將針對不同的前端系統開發工具進行翔實的使用說明，教導如何正確使用 SQL Server 並介紹 SQL Server 的強大物件，來加速前端程式開發的速度與執行效能。將逐一介紹不同的前端開發工具如 Java、ASP、.NET 與 Borland，如何正確連結與使用 SQL Server 來開發出高效能的應用軟體，並深入介紹 SQL Server 的強大物件如預存程序、使用者自訂函數等的使用，為企業建構一個整合的高效能環境

瞭解前端效能調校的策略

日常的 OLTP 系統每天常需要面對的就是該怎樣提升系統的效能？如何加速系統的反應速度？如何縮短查詢的時間？等等的效能強化問題。這些效能調校的範圍舉凡從系統分析師、系統設計人員、應用程式開發人員、資料庫管理師（DBA）與作業系統管理員都是參與的對象，每一種角色都有所屬的工作與職責，透過整體的完整搭配，才可將整個系統的效能做一全面性的改善。

一般傳統式的效能調校多著重於硬體與工作情況的改善，該種傳統式效能調校在現今複雜的環境與高效能規格的硬體環境下，將扮演比較次要的角色。反而，取而代之為應用程式設計、資料庫設計、SQL 語法是三大主要影響整體效能因素，下表中可以表示出，應用程式軟體的優化範圍將影響整個系統效能的 70% 的效能。

企業日常運行的 OLTP 系統在設計過程中多以交易導向、效能導向並注重反應時間，使用的資料庫上面的設計著重於可用度與延展度等，具有幾個主要的特性：

• 處理資料量少，多為單一資料處理

• 資料同步性高，需要即時性的狀態

• 相同的交易方式，少自主性的使用

• 數量龐大，同時間上線人數多

因此在進行 OLTP 系統效能調校的目的上必須瞭解使用目標，一般的原則有

• 快速的交易

  由於 OLTP 系統屬於多人使用的環境，單一筆交易的耗用資源盡量低，以減少系統的負荷，避免 Cursors 的使用，除非特殊需求才用，透過索引強化資料存取與鎖定效能。可以透過 SQL 指令控制或透過 Server 統一設定，降低不正確的使用造成的 BLOCK 或是 DEADLOCK 的方式。透過 SQL 控制的方式則可以在每個 Session 連結時候設定 SET LOCK_TIMEOUT 以調整前端程式等待的時間，而資料庫端的設定方式，可以透過 SET QUERY_GOVERNOR_COST_LIMIT 防止長時間的查詢，造成系統效能下降情況發生。

  

  圖 1：透過 SESSION 設定等候時間，彈性化程式的使用

  

  圖 2：伺服器端統一設定可防止長時間查詢造成系統效能下降

• 最佳化 CPU 使用

  當使用 SQL 指令進行查詢作業時，必須經過幾個階段，Parsingà Standardizationà Query Optimizationà Compilation 最後才產出結果，所以指令的執行計畫，若能高重複使用率，則可降低重複編譯 (re-compilation) 時間，達到效能強化目的。

• 最佳化 IO 使用

  資料庫架構設計多會進行正規劃，去除重複資料，降低資料列大小，以方便查詢、排序與加快索引建立，主要是因為較小資料表中，同一個資料分頁可以容納較多的資料筆數。另外正規劃後，管理上概念清楚、容易維護且較具彈性，可以依據使用需求改變資料結構。但是，從效能調校的原則來看，避免過多的 JOIN 將是改變效能的另一個重要法則，也因此資料庫架構設計常需要反正規劃的設計。

  進行反正規劃設計時需要對應用程式架構的熟悉度極佳才能達到功效，透過反正規劃可以查詢減少查詢時 JOIN 的使用 、降低資料表上的 Foreign Key 建立、減少索引的建立進而節省空間，或是可以預先將彙總結果進行計算與存放，減少處理成本，進行時需要一併考量資料的更動成本與查詢成本。但是，也相對地會因為反正規劃造成系統降低修改速度、應用程式會依據變化而需要調整、增加資料表的大小與某些情況下會增加程式撰寫的複雜性，例如同步修改等的工作。

  進行 IO 效能調校時，反正規劃與正規劃二者必須折衷於更動成本與查詢成本處理，如果你每次查詢資料需要透過 6 個資料表的 JOIN，表示需要反正規劃了，以降低 work tables, tempdb 的使用，一般反正規劃的技術除了新增重複性欄位與衍生欄位，也可以透過增加 indexed views 進行處理。

• 最佳化 Index 使用

  索引主要的功能，便是使用空間縮短作業時間提供比 TABLE SCAN 更好的另一種方法，前端程式進行資料處理時，若後端最佳化執行器偵測到有適合的索引使用時，便會產生數個執行計畫 (Execution Plan)，透過 IO、CPU 成本比較找出最佳的計畫予以執行。因此，在規劃索引時便要考慮日行作業系統的特性，過多的索引除了浪費儲存空間之外，也會影響其他的 IUD (新增、更改、刪除)作業，而屬於決策性的 DSS 系統或報表系統對索引的使用觀念與 OLTP 系統有所不同，前者需要多個索引輔助系統的查詢速度的加快。所以索引的使用與維護成本的折衷考慮時，一般 OLTP 系統，每個資料表最多僅建立 6-8 個索引，並依據交易情況，調整索引的填充率 (fill factor)。一般在 SQL Server 中索引主要分成兩大類，第一類是叢集索引，類似早期電話簿，Key 值緊跟著資料列，多使用在對 KEY 值進行區間查詢，與排序等等。第二類是非叢集索引類似圖書館的書卡，僅記錄書本的名稱與所在位置，提供給可以提供給查詢最佳化執行器更多選擇。

  
  圖 3：叢集式索引資料的排序與 Key 值一樣

  

  圖 4：非叢集式索引中索引頁僅記錄 Key 值與指到資料列的相對應數字

• 有效地 SARG(Search Argument) 使用

  進行資料查詢的 SQL 指令必須撰寫成有效使用查詢參數 (SARG)，如此情況下，最佳化執行器才可以使用既有的索引，避免造成 TABLE SCAN 或是 Clustered Index Scan。有效地 SARG 撰寫原則如下

  (1) 是否有 WHERE 條件
  (2) 使用 Exact match (= )
  (3) 使用區間查詢 (between, >, <, >=, <=)
  (4) 使用 AND，注意 LIKE 的使用方式

  使用方式如下

  column inclusive_operator <constant or variable>

  或是

  <constant or variable> inclusive_operator column

  例如以下都是好的 SARG 使用方式

  name = 'jones'
  salary > 40000
  60000 < salary
  department = 'sales'
  name = 'jones' AND salary > 100000
  Like ‘LEWIS%’

  而 Non-SARG 使用方式如下

  NOT, !=, <>, !>, !<, NOT EXISTS, NOT IN, and NOT LIKE

  LIKE ‘%LEWIS%’，其中該種 LIKE 使用方法將會造成 TABLE SCAN 將無法提升效能。

  但是有些情況下，可以嘗試將 Non-SARG 的語法改寫成 SARG 的指令，加強系統對索引的使用，範例如下:

  Non-SARG
  WHERE SUBSTRING(au_lname, 1, 2) = 'Mc'

  改寫成 SRAG

  WHERE au_lname >='Mc' AND au_lname < 'Md'

  或是

  Non-SARG
  WHERE ABS(change_in_quantity) < 4

  改寫成 SARG

  WHERE change_in_quantity < 4 AND change_in_quantity > -4

• 工具的使用

  效能調校過程中，除了上述介紹法則之外，有效的利用工具可以增加判斷的時效性與正確性，一般進行效能調整常用的工具有 Query Analyzer、索引微調精靈與 SQL Profiler 等。

  Query Analyzer:

  使用該工具可以圖形化分析 SQL 指令執行計畫與索引使用的情況，更可以瞭解整個資料庫作業的執行過程與耗用成本的情況。其中以『評估的執行計劃』最為程式開發人員所常使用，透過顯示評估的執行計劃可以馬上判斷出 SQL 指令的預計執行情況，而不需要等到真正執行之後才瞭解問題可能發生的地方。此外還可以透過 SQL 指令剖析預計執行的 SQL 程式的 IO/CPU/ 索引等使用情況。使用文字方式顯示查詢的執行計畫方式有

  SET SHOWPLAN_TEXT ON
  SET SHOWPLAN_ALL ON

  利用 STATISTICS 陳述式，顯示 SQL 程式的執行歷程

  SET STATISTICS TIME ON
  SET STATISTICS IO ON
  SET STATISTICS PROFILE ON，或是使用圖形方式展示執行計畫

  

  圖 5：透過 Query Analyzer 可以圖形化方式分析 SQL 程式執行結果

  索引微調精靈:

  它可讓一般程式開發人員選取和建立一組資料庫最合適的索引與統計資料，而不需要非常瞭解資料庫結構、工作量或資料庫內部情況的專家來執行這項工作，透過該工作可以將一般的工作負載檔案或是依照查詢指令自動決定最佳建議的索引。但是要特別注意的就是，索引微調精靈不會改寫查詢指令的撰寫方式，僅會在分析後的結果依照情況建議建立叢集索引、非叢集索引、Indexed View 或是複合索引(多欄位索引)等。

  

  圖 6：透過索引微調精靈可以建議索引的建立與預計增加的效能比例

  SQL Profiler:

  可從伺服器中擷取資料庫活動事件，並將事件儲存於追蹤檔案中，可以在事後進行分析或是當嘗試診斷問題時，用於重新執行特定的一連串步驟。SQL Profiler 可以用於下列活動：

  1. 問題查詢，逐步找出造成問題的原因。

  2. 尋找並診斷慢速執行的查詢。

  3. 將引發問題的一系列 SQL 陳述式擷取出來。

  4. 監看資料庫的效能，以微調工作負載。

  

  圖 7：啟動 SQL Profiler 的畫面，可以透過範本的使用加強資料擷取的作業

  

  圖 8：SQL Profiler 的事件分類與類別，可以針對特殊事件追蹤

  

  圖 9：選取需要在 SQL Profiler 中顯示資料，例如電腦位置、使用者帳號等

  

  圖 10：透過篩選器可以將焦點落在監看的程式或是符合的條件中

  

  圖 11：透過 SQL Profiler 追蹤伺服器端的結果，可以看到 SQL 指令與執行狀態(CPU/IO/TIME)

瞭解 SQL Server 2000 強大物件

效能調校方面的重要觀念就是，妥善利用伺服器端的物件來增強程式執行效能，降低網路流量與增加物件重複使用以降低 CPU 的負擔。資料庫中的預存程序 (Stored Procedure) 與使用者定義函數 (User-Defined Function) 都是能有效增強系統效能的關鍵性物件，透過該伺服器端的物件使用，除了上述的功能之外，對於前端程式的開發更能有效達到前端程式的效能強化，降低撰寫資料庫程式的複雜性。以下將針對該兩種物件的定義與使用技巧進行說明：

預存程序:

預存程序就是一組經過命名的 SQL 集合，可以依照程式開發者的定義，自訂輸入參數 (input parameter) 與輸出參數 (output parameter)，更可直接將結果以資料集 (result set) 或是回傳值 (return) 回傳至前端應用程式。預存程序可以有效減少 SQL 指令的 parsing、Standardization、Query Optimization、Compilation 等過程時間，並能有效透過快取功能，增強使用的效能，此外預存程序可以依照需求自行將定義內容加密，也增強物件名稱延緩解析的功能與支援暫存 # 和 ## 預存程序的功能。

整個預存程序的架構如下所示

CREATE PROCEDURE YOUR_PROC_NAME 
  @PARAMATER1 DATA_TYPE, 
  @PARAMETER2 DATA_TYPE OUTPUT, 
  … 
  [WITH {RECOMPILE | ENCRYPTION}]
AS
  DECLARE @Variable2 DATA_TYPE
  DECLARE @Variable2 DATA_TYPE
  …
  {T-SQL Statement}

若要輸出資料集時，可以透過 SELECT 將結果輸出

若要輸出 RETURN 整數值，直接使用 RETURN

GO

使用者定義函數:

建立使用者自訂函數可以將資料庫程式需要大量 Join 運算的作業，分解成多層次的運算處理，這樣可以有效縮短大量資料 JOIN 運算的時間，增強查詢的效能。使用者定義函數與前端應用程式函數主要的差異在於後端資料庫的函數主要著重於 row-set 導向資料處理，而前端應用程式主要著重於 CPU 導向的處理，兩者匹配可以強化系統的整體效能。使用者定義函數主要分成三類，第一種是純量函數 (SCALAR FUNCTION)，對單一值運算後傳回單一值，可以用在 Select List、Where 條件區、CHECK Constraint 定義與 DEFAULT 定義。如下範例主要透過輸入員工編號，找出每個員工的銷售業績值。

CREATE FUNCTION dbo.fn_sale
   (@empid int)
RETURNS MONEY
AS
 BEGIN
    RETURN(select sum(quantity*unitprice)
           from   dbo.orders o inner join dbo.employees e
           on     o.employeeid=e.employeeid inner join dbo.[order details] od
           on     o.orderid=od.orderid
           where  e.employeeid=@empid ) 
 END
GO

若要找出銷售值超過 100,000 的員工基本資料與銷售業績時，使用方法如下圖

第二種類是 Table-Valued 函數，其中可以根據定義方法分成 Multi-statement 與 Inline 兩種定義格式，使用上類似有參數的 VIEW，用在 From 子句中。下列範例展示透過參數輸入回傳該員工所輸入的訂單明細。

CREATE FUNCTION dbo.fn_table_orders
  (@p_employeeid integer)
RETURNS @p_orders table
  (orderid     integer not null primary key ,
   customerid  nchar(10) not null,
   employeeid  integer not null)
AS
BEGIN
   INSERT INTO @p_orders
   SELECT orderid,customerid,employeeid
   FROM   dbo.orders
   WHERE  employeeid = @p_employeeid  
   RETURN
END
GO

若要找出第 4 號員工的銷售訂單時，使用方法如下圖

圖 13：透過伺服器端的物件的使用除了可以方便物件的修改，更可加強系統的執行效能。

調校 Java 連結 SQL Server 2000 效能

當 Java 程式設計人員進行 JDBC 連結 SQL Server 時，可以使用 Type 4 的 JDBC 驅動程式，速度最快。當使用 Type 4 的驅動程式時，必須將下列的三個檔案置於應用程式伺服器端，這樣才可透過 SQL Server for JDBC 驅動程式進行資料庫的連結。驅動程式檔案分別為 msbase.jar、mssqlserver.jar 與 msutil.jar 使用上面，Java 程式中利用以下指令載入 SQL Server for JDBC 的驅動程式

Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);

完成載入之後，接下來就是設定連結資料庫資訊，其中指明伺服器名稱與 Port 數，最後利用 Connection 類別與資料庫進行連結

"jdbc:microsoft:sqlserver://leader:1433;databasename=northwind";

傳送 SQL 時，可利用 Statement 類別，其中 Statement 類別的使用可以分成下列三種

1. 一般 Statement：執行靜態的 SQL 指令

2. PreparedStatement：執行條件式的 SQL 指令

3. CallableStatement：執行資料庫上面的 Stored Procedure

當執行需回傳查詢結果，則使用 executeQuery 方法，非查詢指令時(I.U.D)，使用 executeUpdate，最後再透過 ResultSet 承接回傳的資料集，方法如下

ResultSet rs=stmt.executeQuery(“SELECT …FROM …”);

在 Java 程式中調校 SQL Server 資料庫使用技巧主要著重幾個要點

第一、透過 PreparedStatement 類別傳送 SQL，這樣可以將 SQL 指令預先編譯及儲存，比較有效率，適合重複性執行 SQL 指令的使用執行與 SQL 指令中包含變數，執行時期再給定值。使用的方法如下

PreparedStatement pstmt=
conn. PreparedStatement(“ Insert into epartment(dept_id,dept_name,location) 
values(?,?,?)”);
pstmt.setInt(1,4);
pstmt.setString(2,”台灣”);
pstmt.setString(3,”台北市”);
pstmt.executeUpdate();

第二、透過 CallableStatement 類別執行預存程序，可以將複雜的 SQL 存在 Server 端，減少傳送複雜指令與重新編譯，加快速度並透過參數執行取得結果。使用的方法如下

CallableStatement cstmt=conn.prepareCall(“{?=call proc_name(?,?,?)}”);

其中，？=表示預存程序有回傳值(return value)

再者，？,？,？表示輸入參數

調校 ADO 與 .NET 連結 SQL Server 2000 效能

以前前端程式連結 SQL Server 的方式有 RDO、DAO 最後演進出 ADO(Active Data Object) 方式，是擁有較佳效能的資料庫存取物件，適用於 Visual Basic / ASP / 3rd Party Tool (Delphi) 等，在 ADO 架構中主要包含 Connection 物件、Command 物件與 Recordset 物件。其中 Connection 物件主要連結應用程式與資料庫使用，資料來源種類決定 Provider 例如 SQLOLEDB 則使用 OLE DB provider for Microsoft SQL Server，使用方法如下

Set cnn = new Connection
cnn.provider=“SQLOLEDD”
cnn.connectionString=“User ID=xx;Password=xx;Data Source=xxx;
Initial catalog=northwind”
cnn.open

再者，Command 物件主要進行資料庫的查詢與 I.U.D. 作業，可以進行預存程序的使用後，將回傳的資料集指定給 Recordset 物件，搭配 Prepared 屬性可以強化重複性動作的指令，加強效能。

最後，Recordset 物件主要儲存 Command 物件所傳回的資料集，也可以使用 Open 方法進行資料的查詢動作。演進到 .NET 的 Framework 之後，ADO 也升級到 ADO.NET 的架構，可以處理一般的連線作業，更可進行離線狀況的資料處理。整個升級架構如下圖所示。

透過 Namespace 的架構，可以讓專屬 SQL Server 的應用程式使用 System.Data.SqlClient，強化 SQL Server 資料庫的處理效能，或者也可以透過 System.Data.OleDB 方式，處理異質資料庫的作業，例如 Oracle 等。一般使用 System.Data.SqlClient 連結資料庫方式如下

宣告 cnn 為 connection 物件
	Public cnn As New SqlConnection
在 Form Load 進行資料庫連結
cnn.ConnectionString = _
 "Integrated Security=SSPI;Data Source=leader;Initial Catalog=Northwind"
     cnn.Open()
在Button1 Click 事件時透過 Command 將資料選出，交給 DataGrid 顯示
Dim dsNorthwind As New DataSet
Dim daNorthwind As New SqlDataAdapter
Dim cmNorthwind As New SqlCommand
cmNorthwind.Connection = cnn
cmNorthwind.CommandText =”SELECT firstname,lastname FROM employees”
daNorthwind.SelectCommand = cmNorthwind
daNorthwind.Fill(dsNorthwind)
dg1.DataSource = dsNorthwind.Tables(0)

調校 Borland 連結 SQL Server 2000 效能

坊間利用 Borland 的開發程式連結資料庫的用戶，除了 ADO 的連結方式之外，還有使用 BDE 的連結方法，透過 BDE 的連結方式，可以透過 Native 的 SQL Server 驅動程式，這樣可以加快資料庫的存取速度。再者，元件的使用時建議使用 Query 元件替代 Table 元件的使用，這樣可以避免伺服器端與前端的資源耗用，並且要廣泛使用預存程序的元件增加系統的執行效率。若使用 ADO 物件時，除了原先的 connection/command 與 recordset 物件之外，尚有 table-type dataset /query-type dataset 與 stored procedure-type dataset 等多種選擇可以使用。

結論

前端程式效能調教的重點主要著重資料庫設計的調校、索引的使用 / 反正規劃設計與交易使用的調校，在多人使用資料系統，儘量減少 BLOCK 情況發生。在應用程式的調校方面，利用正確的SQL撰寫方式，重複執行記錄使用率，降低重新編譯發生，使用應用程式連結設定 (API)方面，儘量使用越接近資料庫的 Native Driver(例如 ADO.NET)，最後透過工具(Profiler / 索引調校精靈 / Query Analyzer) 檢視效能可以加速找出癥結點與主要改善的問題。