《server最佳化程式设计指南》由会员分享，可在线阅读，更多相关《server最佳化程式设计指南（19页珍藏版）》请在金锄头文库上搜索。
1、Microsoft SQL Server最佳化程式設計指南前言要設計具備高效能的Microsoft SQLServer應用程式是一門牽涉極廣的學問，這當中涵蓋了開發工具的熟悉程度、資料庫的設計、應用程式的結構、SQL敘述的設計、程式介面的決定等等。而且各個項目所佔的比重，也會因為應用環境的不同，對程式的需求，開發團隊的成員及偏好而有所差異。問題是我們多半沒有足夠的時間去嘗試所有的可能性，來決定那一種安排會是最適當的選擇。我們甚至沒什麼時間來研讀所有的手冊或資料來進行判斷，在這樣的情況下，我們該如何起步，才不至偏離合理的方向太遠？所幸要設計效能良好的SQLServer應用程式有幾種不同的方法，我
2、們可以選擇任何一種，而依舊有很大的機會讓自己的應用程式能有良好的表現。但是，這不代表我們就不需要知道一些基本的觀念，事實上，當我們越能夠掌握一些基本的概念和設計原則，我們就有越好的判斷能力來選擇適當的開發方式。在這篇文章中，我們就是希望能提出一些基本的方法，協助您能擁有做出正確判斷的基礎，並更進一步開發出高效能的應用程式。在開發主從式架構的應用程式中，我們首先面對的問題就是決定要在何處執行我們的應用程式。是不是大多數的工作都必須在前端工作站上完成，或是應該在資料庫中就處理完成，或者應該交由其他的伺服器集中進行？而這個問題當中同時隱含了另外一個問題，就是該選擇什麼樣的開發工具。一旦我們決定了所採
3、用的編譯程式、除錯程式或是執行的平台等產品時，多半也會限制了開發架構的選擇。因此，我們必須同時決定開發的架構以及所採用的工具。在這裡，我們會先談到三層式的開發架構，然後再討論四種建立這類架構的方法。在這個同時，我們也會討論到在該架構下，資料庫實際的製作方式，包括資料庫存取的型式、使用者介面的最佳化處理，以及善用SQLServer內建功能的秘訣。三層式架構三層式架構基本型式從理論上來看，三層式架構主要劃分成三個不同的元件，如圖一。三層式架構資料庫服務商業服務操作介面服務儲存資料，維護整合性資料運算，檢核資料顯示資料，輸出入檢查資料服務 這部份的服務主要在提供資料儲存、對照及資料整合性驗證的服務。
4、例如確認客戶編號的合理性，或是檢查客戶資料表和訂單資料表之間的對照關係是否正確。商業服務 這部份的服務主要是在處理各類商業規則的判斷，以及商業運作上所牽涉到的資料處理工作。例如新增客戶的訂單，同時並檢核訂單金額是否在客戶的信用額度之內。使用介面服務這部份的服務主要在處理使用者介面的操作，包括資料輸入的方式、資料呈現的方式等等。例如，顯示特定客戶的所有訂單，或是顯示各個客戶近半年來的交易金額分析圖等等。 在導入應用程式時，我們有許多做法來安排這些負責不同服務的元件實際安裝在各個機器上的方式。接下來我們就提出四種實際製作的方式，來達成三層式架構的設計目標。o 以前端工作站為主的兩層式設計o 以後端
5、伺服器為主的兩層式設計o 實體三層式設計o Internet設計以前端工作站為主的兩層式設計在決定設計架構時，構成實作方式差異的最主要因素是在商業服務的設計位置上。傳統的設計多半是以兩層的方式設計，而把商業服務的運算工作安排到前端工作站上。在這種架構中，伺服器只有提供SQL Server的資料庫服務。目前市面上多數利用Microsoft Visual Basic或是 PowerBuilder開發的系統都是屬於這類架構。圖二是這個架構的示意圖。圖二 以前端工作站為主的兩層式架構針對這種設計架構的一個改進方式是把商業服務的程式碼包裝成COM的物件，以提供充份的重複運用能力。我們可以利用Visual
6、 Basic設計類別(class)的方式來包裝商業服務，然後在其他的Visual Basic應用程式中使用這些商業服務元件。雖然說把負責使用者介面處理的應用程式和負責商業規則處理的元件放在同一台前端工作站上執行仍然算是兩層式的架構，但是將程式碼分隔之後，卻可以讓我們很輕易的把這樣的程式轉變成實體三層式的設計。稍後我們會討論這部份的設計方式。採用這種以前端工作站為主的兩層式設計最大的好處在於支援這種架構的工具都具備相當有威力的特性，而且在市場上也有較多的選擇。當然，這種架構並不是毫無缺點，其中的一個缺點就是可能形成較多的網路流量，因為所有構成決定依據的資料都必須傳送到前端工作站才能夠進行判斷。不
7、過，從儲存處理狀態例如使用者目前所瀏覽到的記錄的角度來看，前端工作站會是比較適當的場所。 以後端伺服器為主的兩層式設計在以後端伺服器為主的兩層式設計中，商業運作邏輯和資料處理都是在伺服器的資料庫中進行。一般來說，這些商業運作邏輯多半是以stored procedure或trigger的方式表現。圖三是這個架構的示意圖。圖三 以伺服器為主的兩層式設計這種類型的開發架構比較令人困擾的部份在於商業運作邏輯的設計上，必須藉助對SQL敘述相當熟嫻的設計人員。同時它在除錯的環境上也較為缺乏，不容易和既有的開發環境給合。所幸在Microsoft VisualC+ 4.2以及Visual Basic 5.0兩
8、個產品的企業版開始，都提供了Microsoft SQL Server的除錯程式，讓開發人員可以逐步檢視TransactSQL的程式碼，設定中斷點並且檢視區域變數的內容。 這種以後端伺服器為主的設計方式最大的優勢是它的執行效能。由於商業運作邏輯是和資料處理的程式碼在同一個空間中執行，並且和SQL Server的資料庫引擎充份結合，因此可以減少網路間的資料轉移動作，同時可以降低工作轉移的需求。這種架構的缺點在於它限制了所能夠使用的開發工具。這些stored procedure都必須以資料庫所支援的語言撰寫，相對也增加了相容的限制。雖然Microsoft SQLServer可以支援T-SQL以外的語
9、法撰寫程式，但是也增加了複雜的程度，同時它的執行效率也不及TransactSQL。 實體三層式設計實體的三層式設計也稱做三層式架構，它也常被誤認為是三層式設計唯一的製作方式。在實體三層式設計中，商業邏輯是在獨立的執行空間中執行，它可能和資料庫伺服器跑在同一台電腦上，也可能是在不同的伺服器上執行。這種設計和前面兩種設計最大的差異在於這三個層次在執行時都是在不同的執行空間中，就算是在同一台電腦上進行處理，各個層次間的通訊也必須跨越不同的執行空間。圖四是這個架構的示意圖。圖四 實體三層式設計Internet設計Internet的出現引發了另一股三層式設計的旋風：將使用者介面的服務分割到瀏覽程式和We
10、b伺服器上。Web伺服器主要的工作在建立使用者瀏覽頁面的格式，而瀏覽程式則是負責顯示頁面，同時下載其他必要的資料。而在Web伺服器和資料庫之間，仍然存在安排商業邏輯的不同選擇。 一般在Internet上的設計是同時在Web伺服器上進行使用者介面及商業處理的服務。某些產品可以讓商業處理程式納入Web伺服器的程序中執行，減少程序交替所形成的負荷。其中的一個例子是Microsoft Internet Information Server (IIS) 所提供的資料處理服務Microsoft Internet Database Connector (IDC)。IDC可以連結任何ODBC的資料來源包括SQ
11、LServer進行資料庫的存取工作並構成對應的HTML頁面，直接送給前端的瀏覽程式。圖三是架構示意圖。圖三 Internet設計示意圖Internet架構的最大好處在於它可以透過瀏覽程式進行資料存取的工作，提供了跨平台的能力。同樣一個應用程式，可以透過瀏覽程式在Windows 3.1、Windows 95、Windows NT、Apple Macintosh、OS/2以及UNIX等作業系統上執行。所有前端工作站所必須具備的功能都可以透過標準的Web瀏覽程式完成。而容易管理則是Internet架構的另一個優點，一旦我們更新了Web伺服器上的設計，所有前端工作站也同步完成更新的作業。在少數的伺服器
12、上管理Web的頁面資料要比在許多的前端工作站上管理應用程式要容易多了。由於一些Web的產品可以把設計工作延伸到傳統的程式服務上，也擴大了Internet架構的三層式運用範圍。像IIS 3.0所提供的ASP功能，可以讓程式設計人員利用VBScript控制既有的程式元件進行商業運算的服務，充份提高可用能力。然而目前的Internet架構仍然不適合進行大量的線上處理工作(online transaction processing , OLTP)，但是在這篇文章中所提到的許多觀念依舊適用。 資料庫存取風格設計高效能資料庫應用程式的另外一個要素是應用程式和資料庫溝通的方式。有些應用程式只把資料庫視為儲存
13、記錄的場所，而應用程式本身會進行絕大多數的資料處理工作，像是資料的篩選、統計或是比對。其他的應用程式則可能把資料庫視做資料管理的環境，讓資料庫進行大多數資料處理的動作。前一類型的資料庫多半是屬於ISAM(indexed sequential access method)型式的資料庫，而後者則是像SQL Server這類的關聯式資料庫。接下來我們就要比較這兩者之間的差異。ISAM資料庫的存取許多和資料庫有關的應用程式最初都是以ISAM型式的資料庫做為主要存取的對象，之後才轉換成關聯式資料庫。通常將資料轉移到關聯式資料庫上能提供更好的資料分享能力，也能夠符合一些資料庫的標準。這種轉移的動作多半是將
14、各個ISAM資料庫檔案轉入關聯式資料庫的資料表，而應用程式保持原狀。SQLServer可以配合這些ISAM型式的應用程式，但是在搭配上多少有些缺憾，如果應用程式能夠充份運用到關聯式資料庫的存取特性的話，在效能上將會有大幅度的改善。ISAM程式範例我們可以利用下面的例子來說明為何ISAM風格的應用程式在存取SQLServer資料庫時很難發揮最佳的效益。這個程式使用到三個資料表，一個是客戶的基本資料表，然後是訂單細目資料表，最後是訂貨總計資料表。這個程式主要的工作是找出客戶“Viki Parrott”的所有訂單，然後計算這些訂單的訂購總額，然後把結果填入訂貨總計資料表中。這個程式進行工作的程序如下
15、： 1. 前端工作站送出BEGIN TRANSACTION的要求給伺服器。 2. 前端工作站要求伺服器在customer資料表上開啟一個cursor ，這個cursor必須篩選出姓氏為Parrott的客戶。3. 前端工作站從cursor中依序取出各個客戶記錄，直到找到正確的客戶資料為止。4. 一旦找到正確的客戶資料，便取出對應的客戶編號，然後再要求伺服器在orders資料表上開啟一個客戶編號相同的cursor，以取得該客戶的所有訂單資料。 5. 前端工作站從cursor中取出所有的記錄，並計算這些訂單的訂購金額總和。6. 前端工作站送出一個命令要求更新invoices資料表中該客戶的訂購總額。
16、7. 前端工作站送出COMMIT TRANSACTION的命令。ISAM資料庫的效能考量為什麼ISAM型式的應用程式在存取關聯式資料庫時的表現不佳，以下是可能的幾個原因： 形成網路負荷ISAM風格的應用程式很容易就會在網路上傳遞一些不必要的資料。在上面例子中，前端程式分別取得客戶資料和訂單資料到前端工作站進行比對及運算的工作，但是這些工作都可以在伺服器端直接進行。更重要的是，這個例子至少牽涉到六趟前端工作站和伺服器之間的溝通才能夠順利完成整個工作。在主從式架構的解決方案中，網路的溝通次數常常是構成效能不佳的最主要因素，它甚至比網路資料量的多寡影響還大。 過度使用cursorCursor在關聯式資料庫中是個很有用的工具，但是它的代價不小。在大多數的情況下，這些cu
