登入

Select the product you need help with

ACC2000:如何最佳化 Microsoft Access 2000 中的查詢

文章編號: 209126 - 檢視此文章適用的產品。

本文曾發行於 CHT209126

中級使用者：需具備基本巨集、編碼及互操作性技巧。

本文討論如何最佳化 Microsoft Access 2000 中的查詢效能。主題包括 Microsoft Jet 資料庫引擎的查詢最佳化器、查詢時間、分析效能與改善查詢效能的設計祕訣。

本文假設您的資料庫具有區域資料表，而非連結 (或附加) 資料表。如果您的資料表是連結資料表，這些資訊也適用，但對於連結資料表而言，還有其他課題會影響查詢效能。如需有關改善連結資料表效能的其他資訊，請在 Microsoft Knowledge Base 中搜尋下列字串：

odbc and optimizing and tables

回此頁最上方 | 提供意見

其他相關資訊

查詢最佳化器

Microsoft Jet 資料庫引擎包含幾個元件，其中對查詢而言最重要 (也最複雜) 的元件是最佳化器。最佳化器是 cost-based，也就是說它會給每個查詢工作指定一個時間耗用成本，然後選擇最不費時的工作清單來執行，以產生想要的結果集。工作執行所需的時間越長，就會被視為越耗用時間成本或越費時。

最佳化器使用統計資料來決定使用何種查詢策略。這些統計資料包括資料表的記錄數、資料表中的資料頁數、資料表的位置、是否有索引、索引的唯一性如何，等等。根據這些統計資料，最佳化器會在處理特殊查詢時選擇最佳的內部查詢策略。

每當電腦編譯查詢時，統計資料都會更新。當您儲存查詢 (或其基礎資料表) 的任何變更，以及當您壓縮資料庫時，查詢都會被標記為需要編譯。如果查詢被標記為需要編譯，下一次執行查詢時，統計資料就會編譯及更新。編譯所需的時間通常為 1-4 秒。

如果您在資料庫新增大量數目的記錄，您應該開啟並儲存查詢，以重新編譯查詢。例如，如果您使用小型的範本資料集來設計與測試查詢，則在資料庫新增額外的記錄後，應該重新編譯查詢。這樣做可以確保在使用應用程式時的最佳查詢效能。

注意：您無法檢視 Jet 資料庫引擎的最佳化作業方式或者指定如何最佳化查詢。但是，您可以使用 Database Documenter 來確定索引是否存在以及索引的唯一性。

如需有關 Database Documenter 的其他資訊，請按下面的文件編號，參閱 Microsoft Knowledge Base 中的下列文件：

207782

(http://support.microsoft.com/kb/207782/EN-US/ )

ACC2000:Error Using Database Documenter If Objects Are Open

查詢時間

[選取] 查詢有二個重要的時間度量單位：顯示第一個資料畫面的時間，以及取得最後一個記錄的時間。如果查詢只傳回一個資料畫面，這二個度量單位就會相同。如果查詢傳回許多記錄，這些時間度量單位會有很大的差異。

如果當您檢視 [資料工作表檢視] 中的 [選取] 查詢時，上述二個度量單位相同，您會看到資料畫面以及查詢所傳回的記錄總數，例如「記錄 1 之 N。」如果 Jet 資料庫引擎顯示第一個資料畫面比完成查詢與擷取最後的記錄要快，您會看到資料畫面，但看不到「記錄 1 之 N」中的 N。直到完成查詢或捲動到最後的記錄之前，N 值是空白的。

這種行為是因為 Jet 資料庫引擎選擇下列其中一種效能策略的結果：完成查詢，再顯示資料；或者顯示資料，再完成查詢。您無法控制使用何種策略；但是，Jet 資料庫引擎會選擇最有效率的策略。

分析效能

如果使用 Microsoft Access 7.0、Access 97 或 Access 2000，您可以使用「效能分析器」來分析資料庫的查詢。因為查詢效能分析與 Jet 資料庫引擎關係密切，「效能分析器」只會在 Jet 資料庫引擎實際使用索引來最佳化查詢時建議您新增索引。這表示「效能分析器」可以提供比以下本文〈改善查詢效能的祕訣〉一節所列的一般建議更符合您資料庫需要的效能祕訣。

要在 Microsoft Access 7.0、Microsoft Access 97 或 Microsoft Access 2000 中執行「效能分析器」，在 [工具] 功能表上按一下 [分析]，然後按一下 [效能]。

改善查詢效能的祕訣

要改善查詢效能，請嘗試以下祕訣：

壓縮資料庫。因為壓縮資料庫會重新組織資料表的記錄，使記錄依資料表的主索引鍵順序儲存於相鄰的資料庫資料頁，因此可以加速查詢。因為要擷取所有的記錄只需讀取最少數目的資料庫資料頁，因而改善了資料表記錄的循序掃描效能。壓縮資料庫之後，請執行每個查詢，使用更新的資料表統計資料來編譯查詢。
建立用來設定查詢準則的欄位的索引、建立聯結二邊的欄位的索引或建立這些欄位之間的關聯。建立關聯之後，如果沒有外部索引鍵索引，Microsoft Jet 資料庫引擎會建立外部索引鍵索引；否則它會使用現有的索引。

注意：如果 Microsoft Access 資料表很小，而且聯結的欄位有索引，Microsoft Jet 資料庫引擎會自動最佳化聯結您硬碟資料表與 ODBC 伺服器資料表的查詢。在這種狀況下，Microsoft Access 藉由只從伺服器請求必要的記錄來改善效能。請確認您從不同來源聯結的資料表在聯結欄位上有建立索引。
定義資料表欄位時，請選擇欄位資料最適合的最小資料類型。同時，給予要作為聯結的欄位相同或相容的資料類型，例如 [自動編號] 或 [數字] (如果 [欄位大小] 屬性設定為 [長整數])。
建立查詢時，只增加您要的欄位。如果您不要顯示用來設定準則的欄位，請按一下清除 [顯示] 核取方塊。
如果表單或報表的 [記錄來源] 屬性設定為 SQL 陳述式，請將 SQL 陳述式儲存為查詢，然後將 [記錄來源] 屬性設定為查詢的名稱。
避免子查詢的計算欄位。如果您在另一個查詢新增包含計算欄位的查詢，計算欄位中的運算式可能會降低高階查詢的效能。下列的範例中，查詢 Q1 用來作為查詢 Q2 的輸入：
Q1:SELECT IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed") AS X FROM MyTable;
Q2:SELECT * FROM Q1 WHERE X="Order Confirmed";
因為 Q1 中的 IIf 運算式無法最佳化，Q2 也無法最佳化。如果子查詢中有無法最佳化的巢狀運算式，整個查詢就無法最佳化。

以下所示為建構查詢的另一個方法：
Q1:SELECT * FROM MyTable WHERE MyColumn = "Yes";
如果輸出需要運算式，請試著將運算式放在表單或報表的控制項。例如，您可以將之前的查詢變更為提示輸入 MyColumn 值的參數查詢，然後根據此查詢建立表單或報表。根據 MyColumn 的值，您可以在表單或報表上新增顯示 "Hello" 或 "Goodbye" 的計算控制項。

如下所示建構查詢：
PARAMETERS [To see confirmed orders, enter Yes.To see unconfirmed orders, enter No.] Text;
SELECT *
FROM MyTable
WHERE MyColumn = [To see confirmed orders, enter Yes.To see unconfirmed orders, enter No.];
在表單或報表的計算控制項鍵入：
=IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed")
當您以聯結欄位的值群組記錄時，請指定與您計算總和 (計算聚合) 的欄位位於同一資料表的欄位的 [群組對象] 屬性。例如，在北方範例資料庫中，如果您建立計算 [詳細訂單] 資料表 [數量] 欄位總和的查詢，且以 [訂單編號] 群組，建議您指定 [詳細訂單] 資料表 [訂單編號] 欄位的 [群組對象] 屬性。如果您指定 [訂單] 資料表 [訂單編號] 欄位的 [群組對象] 屬性，Microsoft Access 必須先聯結所有的記錄，然後再執行聚合，而不是執行聚合，再聯結必需的欄位。

要加速查詢速度，請儘可能不要使用 [群組對象] 屬性。您可以在適當的地方使用 First 函數來取代。

如果總和查詢包括聯結，請考慮將查詢中的記錄群組，並將查詢新增至執行聯結的其他查詢。這樣可以改善某些查詢的效能。
儘可能避免在計算欄位與非索引欄位使用限制性的查詢準則。使用可以最佳化的準則運算式。
如果在具有一對多關聯的資料表間的聯結使用準則限制欄位的值，請測試當準則放在聯結的「一」邊或「多」邊時，哪個查詢執行速度較快。在某些查詢中，您可以藉由在聯結的「一」邊而非「多」邊的欄位新增準則，而加速查詢效能。
索引用來排序的欄位。
如果資料不會經常變更，請使用產生資料表查詢從查詢結果建立資料表。使用結果資料表 (而不是查詢) 作為表單、報表或其他查詢的基礎，並確定根據這裡建議的原則新增索引。
避免使用範圍聚合函數，例如 DLookup 函數存取不在查詢中的資料表的資料。範圍聚合函數是 Microsoft Access 特有的，這表示 Jet 資料庫引擎無法最佳化使用範圍聚合函數的查詢。請在查詢新增函數存取的資料表或建立子查詢。
如果您建立交叉查詢，請儘可能使用固定欄位標題。
在索引的欄位使用 Between...And、In 與 = 運算子。
對於 ODBC 資料來源的大量更新查詢，請將 FailOnError 屬性設定為 [是] 來最佳化伺服器效能。

回此頁最上方 | 提供意見

?考

如需有關最佳化 Microsoft Access 2000 效能的其他資訊，請按一下 [說明] 功能表上的 [Microsoft Access 說明]，在「Office 小幫手」或「解答精靈」鍵入 optimize performance，然後按一下 [搜尋] 檢視該主題。
如需有關使用索引的其他資訊，請按下面的文件編號，參閱 Microsoft Knowledge Base 中的下列文件：

209564

(http://support.microsoft.com/kb/209564/EN-US/ )

ACC2000:Compound Indexes Must Restrict First Indexed Field

回此頁最上方 | 提供意見

?考

本文件是根據 Microsoft Knowledgebase 文件編號 Q209126 翻譯的. 若要參考原始英文文件內容, 請至以下網址:

http://support.microsoft.com/support/kb/articles/Q209/1/26.asp

(http://support.microsoft.com/kb/209126/en-us?ln=en-us&sd=gn&fr=0)

回此頁最上方 | 提供意見

屬性

文章編號: 209126 - 上次校閱: 2000年11月13日 - 版次: 1.0

這篇文章中的資訊適用於:

Microsoft Access 2000 Standard Edition

kbhowto kbinfo kbdta kbusage KB209126

Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

回此頁最上方 | 提供意見