查詢效能微調 (SQL Server Compact)

您可以最佳化使用的查詢，藉以改善 SQL Server Compact 4.0 應用程式效能。下列各節將說明可以用來最佳化查詢效能的方法。

改善索引

建立有用的索引是達到較佳查詢效能最重要的一種方式。有用的索引可協助您以較少的磁碟 I/O 作業和較低的系統資源使用率來搜尋資料。

若要建立有用的索引，您必須了解資料的使用方式、查詢的類型和執行的頻率，以及查詢處理器可以如何使用索引來迅速找到您的資料。

當您選擇要建立的索引之後，請檢查您的關鍵查詢，因為這項查詢的效能將會極為影響使用者經驗。請建立特別有助於這些查詢的索引。新增索引之後，請重新執行查詢，以了解效能是否有改善。如果未改善，請移除索引。

大部分的效能最佳化方式都會有優缺點。例如，使用越多索引，SELECT 查詢的執行速度可能會越快。不過，DML (INSERT、UPDATE 及 DELETE) 作業將變得極為緩慢，因為每項作業必須維護更多索引。因此，如果您的查詢大部分是 SELECT 陳述式，使用越多索引會越有幫助。但如果您的應用程式執行許多 DML 作業，您就應該謹慎使用您所建立的索引。

SQL Server Compact 支援顯示計劃，顯示計劃可幫助存取和最佳化查詢。SQL Server Compact 使用和 SQL Server 2008 R2 相同的顯示計劃結構描述，但 SQL Server Compact 使用的是運算子的子集。如需詳細資訊，請參閱 Microsoft 執行程序表結構描述，網址為 https://schemas.microsoft.com/sqlserver/2004/07/showplan/。

下列章節將提供建立有用索引的其他相關資訊。

建立高度選擇性的索引

關鍵查詢之 WHERE 子句中所用的資料行索引通常可以改善效能。不過，這取決於索引的選擇性而定。選擇性是合格資料列與總資料列的比率。如果比率過低，表示索引具有高度選擇性。這項索引可以排除大部分的資料列，並大幅減少結果集的大小。因此，這是一項值得建立的有用索引。反之，非選擇性的索引則沒有用。

唯一的索引具有最佳的選擇性。由於只有一個資料列會符合，因此這種索引對於需要只傳回一個資料列的查詢最有用。例如，唯一識別碼資料行的索引將可協助您迅速找到特定資料行。

您可藉由執行 SQL Server Compact 資料表上的 sp_show_statistics 預存程序來評估索引的選擇性。例如，如果您正在評估性 "Customer ID" 和 "Ship Via" 這兩個資料行的選擇性，可以執行下列預存程序：

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

ALFKI               0            7                   0

ANATR               0            4                   0

ANTON               0           13                   0

AROUT               0           14                   0

BERGS               0           23                   0

BLAUS               0            8                   0

BLONP               0           14                   0

BOLID               0            7                   0

BONAP               0           19                   0

BOTTM               0           20                   0

BSBEV               0           12                   0

CACTU               0            6                   0

CENTC               0            3                   0

CHOPS               0           12                   0

COMMI               0            5                   0

CONSH               0            4                   0

DRACD               0            9                   0

DUMON               0            8                   0

EASTC               0           13                   0

ERNSH               0           33                   0

(90 rows affected)

及

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

1               0            320                   0

2               0            425                   0

3               0            333                   0

(3 rows affected)

結果顯示，"Customer ID" 資料行的重複程度較低。這表示此資料行的索引會比 "Ship Via" 資料行的索引更具選擇性。

如需有關使用上述預存程序的詳細資訊，請參閱 sp_show_statistics (SQL Server Compact)、sp_show_statistics_steps (SQL Server Compact) 和＜sp_show_statistics_columns (SQL Server Compact)＞。

建立多重資料行索引

多重資料行索引是單一資料行索引的自然延伸。在評估比對索引鍵資料行的字首集之篩選條件運算式時，多重資料行索引很有用。例如，複合索引 CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 可協助評估下列查詢：

• ...WHERE "Last Name" = 'Doe'

• ...WHERE "Last Name" = 'Doe' AND "First Name" = 'John'

• ...WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

不過，對於這項查詢則沒有用：

• ...WHERE "First Name" = 'John'

當您建立多重資料行索引之後，您應該將大部分最左邊的選擇性資料行放在索引鍵中。這樣在比對多項運算式時，會讓索引更具選擇性。

避免索引小型資料表

小型資料表是指將內容納入單一或少數資料頁面的資料表。請避免索引超小型資料表，因為通常進行資料表掃描會更有效率。而且這樣可節省載入及處理索引頁的成本。如果您不建立超小型資料表的索引，就可減少設計工具選取索引的機會。

SQL Server Compact 會將資料儲存在 4 Kb 的頁面中。雖然實際的計數可能會由於儲存引擎額外負擔而稍大，但您仍可藉由使用下列公式求出此頁面計數的近似值。

<資料行大小的總和 (以位元組為單位)> * <資料列數目>

<頁數> = -----------------------------------------------------------------

4096

例如，假設資料表具有下列結構描述：

資料行名稱	類型 (大小)
訂單識別碼	INTEGER (4 個位元組)
產品識別碼	INTEGER (4 個位元組)
單價	MONEY (8 個位元組)
數量	SMALLINT (2 個位元組)
折扣	REAL (4 個位元組)

此資料表有 2820 個資料列。根據本公式，大約需要 16 頁來儲存其資料：

<頁數> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15.15 頁

選擇索引項目

我們建議您一定要建立主索引鍵的索引。此外，建立外部索引鍵的索引通常也很有用。這是因為主索引鍵和外部索引鍵通常會用來聯結資料表。這些索引鍵的索引會讓設計工具考慮使用更有效率的索引聯結演算法。如果您的查詢使用其他資料行來聯結資料表，則建立這些資料行的索引通常也會因相同理由而同樣有用。

當主索引鍵和外部索引鍵的條件約束建立後，SQL Server Compact 便會自動建立它們的索引，並在最佳化查詢時加以利用。請務必盡量縮小主索引鍵和外部索引鍵的大小。如此，聯結的執行速度才會更快。

以篩選子句使用索引

索引可用來加快某些篩選子句類型的評估速度。雖然所有篩選子句都可減少查詢的最終結果集，但是某些篩選子句有效協助減少必須掃描的資料量。

搜尋引數 (SARG) 可限制搜尋範圍，因為它會指定確切的符合項目、某個範圍值，或是兩個或多個以 AND 聯結的項目結合。該引數具有下列其中一種形式：

• 資料行 運算子 <常數或變數>

• <常數或變數> 運算子 資料行

SARG 運算子包括 =、>、<、>=、<=、IN、BETWEEN 和 LIKE (在字首比對的情況中，例如 LIKE 'John%')。SARG 可以包含以 AND 聯結的多重條件。SARG 可以是比對特定值的查詢，例如：

• "Customer ID" = 'ANTON'

• 'Doe' = "Last Name"

SARG 也可以是比對某個範圍值的查詢，例如：

• "Order Date" > '1/1/2002'

• "Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'

• "Customer ID" IN ('ANTON', 'AROUT')

未使用 SARG 運算子的運算式無法改善效能，因為 SQL Server Compact 查詢處理器必須評估每個資料列才能判斷資料列是否滿足篩選子句。因此，未使用 SARG 運算子之運算式的索引沒有用。非 SARG 運算子包括 NOT、<>、NOT EXISTS、NOT IN、NOT LIKE 和內建函式。

使用查詢設計工具

在判斷基底資料表的存取方式時，SQL Server Compact 最佳化工具會判斷 SARG 子句的索引是否存在。如果索引存在，設計工具便會藉由計算傳回的資料列數目來評估索引。然後，設計工具便估計使用該索引找出合格資料列的成本。如果這項成本低於資料表掃描的成本，設計工具就會選擇索引存取。如果索引的第一個資料行或資料行的字首集用於 SARG，而且 SARG 建立了下限、上限或兩者來限制搜尋範圍，則索引可能就會很有用。

了解回應時間與總時間

回應時間是指查詢傳回第一筆記錄所需的時間。總時間則是指查詢傳回所有記錄所需的時間。就互動式應用程式而言，回應時間很重要，因為這個時間就是使用者在處理查詢後收到視覺化確認的感受時間。對於批次應用程式而言，總時間則會反映整體處理能力。您必須先判斷應用程式及查詢適用的效能條件，然後再據以設計。

例如，假設查詢傳回 100 筆記錄，而且以前五筆記錄來擴展清單中。在此情況下，您不會在意傳回 100 筆記錄所需的總時間。反而，您會希望查詢能迅速傳回前幾筆記錄，讓您能夠擴展清單。

許多查詢作業可以在不儲存中繼結果的情況下執行。這些作業就是所謂的管線作業。管線作業的範例包括規劃、選擇以及聯結。配合這些作業方式實作的查詢可立即傳回結果。但是其他作業 (例如 SORT 和 GROUP-BY) 則需要先使用所有輸入，然後才能將結果傳回父系作業。這些作業就是所謂的具體化作業。以這些作業方式實作的查詢最初通常會由於具體化而有所延遲。不過在最初的延遲過後，它們通常就可以非常迅速地傳回記錄。

含有回應時間需求的查詢應該避免具體化。例如，使用索引來實作 ORDER-BY 的回應時間會比使用排序的回應時間要快。下一節將進一步詳細說明。

索引 ORDER-BY / GROUP-BY / DISTINCT 資料行以獲得較佳的回應時間

ORDER-BY、GROUP-BY 和 DISTINCT 作業都是排序的類型。SQL Server Compact 查詢處理器會以兩種方式實作排序。如果記錄已由索引排序，則處理器必須單獨使用索引。否則，處理器就必須先使用暫時工作資料表來排序記錄。這種初步排序在 CPU 效能較低且記憶體有限的裝置上會導致明顯的初始延遲，而且如果回應時間很重要，就應該避免採用。

在多重資料行索引的內容中，若要讓 ORDER-BY 或 GROUP-BY 考慮特定的索引，ORDER-BY 或 GROUP-BY 資料行就必須按正確的順序符合索引資料行的字首集。例如，索引 CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 可協助最佳化下列查詢：

• ...ORDER BY / GROUP BY "Last Name" ...

• ...ORDER BY / GROUP BY "Last Name", "First Name" ...

但無法協助最佳化：

• ...ORDER BY / GROUP BY "First Name" ...

• ...ORDER BY / GROUP BY "First Name", "Last Name" ...

若要讓 DISTINCT 作業考慮多重資料行索引，規劃清單就必須符合所有索引資料行 (雖然它們不需要按正確的順序)。上一個索引可以協助最佳化下列查詢：

• ...DISTINCT "Last Name", "First Name" ...

• ...DISTINCT "First Name", "Last Name" ...

但無法協助最佳化：

• ...DISTINCT "First Name" ...

• ...DISTINCT "Last Name" ...

重新寫入子查詢，以便使用 JOIN

有時您可以重新寫入子查詢，以便使用 JOIN，並達到較佳的效能。建立 JOIN 的優點在於，您可以按照不同於查詢所定義的順序來評估資料表。而使用子查詢的優點則是，您通常不需要掃描子查詢中的所有資料列即可評估子查詢運算式。例如，當 EXISTS 查詢看見第一個合格的資料列時，就可傳回 TRUE。

例如，若要判斷至少含有一個 25 百分比以上折扣項目的所有訂單，您可以使用下列 EXISTS 子查詢：

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

您也可以使用 JOIN 重新寫入這項子查詢：

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

使用 Outer JOIN 的限制

OUTER JOIN 與 INNER JOIN 的不同之處為，設計工具不會嘗試重新排列 OUTER JOIN 資料表的聯結順序，但會重新排列 INNER JOIN 資料表。系統會先存取外部資料表 (LEFT OUTER JOIN 中的左側資料表以及 RIGHT OUTER JOIN 中的右側資料表)，然後再存取內部資料表。這項固定的聯結順序可能會導致執行計劃未達最佳效能。

如需有關包含 INNER JOIN 之查詢的詳細資訊，請參閱 Microsoft 知識庫文件。

使用參數化查詢

如果您的應用程式執行一系列的查詢，而且各項查詢中只有某些常數不同，您就可以使用參數化查詢來改善效能。例如，若要傳回不同客戶的訂單，您可以執行下列查詢：

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

參數化查詢可藉由單次編譯查詢及多次執行已編譯計畫，進而產生較佳的效能。在程式設計方面，您必須掌握含有快取查詢計劃的命令物件。因為摧毀上一個命令物件並建立新的命令物件，就會摧毀快取計畫。然後系統就會要求重新編譯查詢。如果您必須以交錯方式執行多項參數化查詢，可以建立多項命令物件，讓每項物件都快取參數化查詢的執行計畫。如此一來，您便可以有效避免重新編譯所有查詢。

僅在必要時才查詢

SQL Server Compact 查詢處理器是功能強大的工具，可查詢儲存在關聯式資料庫中的資料。不過，任何查詢處理器都有相關的衍生成本。因為查詢處理器必須先編譯、最佳化並產生執行計畫，然後才能開始執行計畫的實際工作。這種情況尤其適用於快速完成的簡易查詢。因此，您自己實作查詢有時可以獲得大幅的效能改善。如果您的關鍵元件將每一毫秒都計算在內，我們建議您考慮自己實作簡易查詢的替代方案。如果是既龐大又複雜的查詢作業，最好還是留待查詢處理器來執行。

例如，假設您想要查詢按訂單識別碼排列之一系列訂單的客戶識別碼。以下有兩種方式可達成此目標。首先，您可以遵循下列步驟來查詢：

1. 開啟 Orders 基底資料表

2. 使用特定的 "Order ID" 找出資料列

3. 擷取 "Customer ID"

或者，您可以發出下列查詢：

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

查詢式解決方案比手動解決方案要簡單但緩慢，因為 SQL Server Compact 查詢處理器會將宣告性 SQL 陳述式轉譯為您可以手動實作的三項相同的作業。然後，系統依序執行這三項步驟。當您選擇要使用的方式時，需視您的應用程式是以簡單或效能為重點。