查詢折疊範例

本文針對查詢折疊的三個可能結果，提供一些範例案例。 它也包含有關如何充分利用查詢折疊機制的一些建議，以及它可以在您的查詢中產生的效果。

案例

假設使用 適用於 Azure Synapse Analytics SQL 資料庫的 Wide World Importers 資料庫，您負責在 Power Query 中建立可連線到 fact_Sale 數據表的查詢，並只擷取最後 10 個銷售字段：

• 銷售金鑰
• 客戶金鑰
• 發票日期索引鍵
• 描述
• 數量

注意

基於示範目的，本文使用將Wide World Importers 資料庫載入 Azure Synapse Analytics 教學課程中所述的資料庫。 本文的主要差異在於 fact_Sale 數據表只保存 2000 年的數據，總共有 3,644,356 個數據列。

雖然結果可能與遵循 Azure Synapse Analytics 檔教學課程取得的結果不完全相符，但本文的目標是展示查詢折疊在查詢中的核心概念和影響。

本文示範三種方式，以不同層級的查詢折疊來達成相同的輸出：

• 沒有查詢折疊
• 部分查詢折疊
• 完整查詢折疊

沒有查詢折疊範例

重要

僅依賴非結構化數據源或沒有計算引擎的查詢，例如 CSV 或 Excel 檔案，則沒有查詢折疊功能。 這表示Power Query 會使用Power Query引擎評估所有必要的資料轉換。

線上到資料庫並瀏覽至fact_Sale資料表之後，您可以選取 [常用] 索引標籤的 [縮減資料列] 群組內找到的 [保留底部資料列轉換]。

選取此轉換之後，會出現新的對話方塊。 在這個新的對話框中，您可以輸入您想要保留的數據列數目。 在此情況下，請輸入值 10，然後選取 [ 確定]。

提示

在此情況下，執行此作業會產生最後十個銷售額的結果。 在大部分情況下，我們建議您提供更明確的邏輯，定義數據表上套用排序作業，最後考慮哪些數據列。

接下來，選取 [首頁] 索引標籤的 [管理數據行] 群組內找到的 [選擇數據行轉換]。然後，您可以選取您想要從數據表中保留的數據行，並移除其餘的數據行。

最後，在 [ 選擇數據行 ] 對話框中，選取 Sale Key、 Customer Key、 Invoice Date Key、 Description和 Quantity 數據行，然後選取 [ 確定]。

下列程式代碼範例是您所建立查詢的完整 M 文稿：

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Kept bottom rows" = Table.LastN(Navigation, 10),
  #"Choose columns" = Table.SelectColumns(#"Kept bottom rows", {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"})
in
  #"Choose columns""

沒有查詢折疊：了解查詢評估

在 Power Query 編輯器的 [已套用] 步驟下，您會發現 [保留底部數據列] 和 [選擇] 資料行的查詢折迭指標會標示為數據源外部評估的步驟，換句話說，Power Query 引擎也會評估。

您可以以滑鼠右鍵按下查詢的最後一個步驟，也就是名為 Choose 數據行的查詢，然後選取讀取 [檢視查詢計劃] 的選項。 查詢計劃的目標是提供查詢執行方式的詳細檢視。 若要深入瞭解這項功能，請移至 查詢計劃。

上一個 映像中的每個方塊稱為節點。 節點代表完成此查詢的作業明細。 代表數據源的節點，例如上述範例中的 SQL Server 和 Value.NativeQuery 節點，代表查詢的哪個部分會卸除至數據源。 在此案例 Table.LastN 中，其餘節點會由Power Query引擎評估，並在 Table.SelectColumns 上一個影像的矩形中反白顯示。 這兩個節點代表您新增的兩個轉換： [保留底部數據列 ] 和 [ 選擇數據行]。 其餘節點代表在數據源層級發生的作業。

若要查看傳送至數據源的確切要求，請選取節點中的 Value.NativeQuery [檢視詳細數據]。

此數據源要求是數據源的原生語言。 在此情況下，該語言為 SQL，而這個語句代表數據表中所有數據列和欄位 fact_Sale 的要求。

諮詢此數據源要求可協助您進一步瞭解查詢計劃嘗試傳達的故事：

• Sql.Database：此節點代表數據源存取。 連線 至資料庫，並傳送元數據要求以瞭解其功能。
• Value.NativeQuery：表示由Power Query產生以完成查詢的要求。 Power Query 會將原生 SQL 語句中的數據要求提交至數據源。 在此情況下，表示數據表中的所有記錄和欄位（資料行）。fact_Sale 在此案例中，此案例不理想，因為數據表包含數百萬個數據列，而利息只會在最後 10 個。
• Table.LastN：一旦 Power Query 從數據表接收所有記錄 fact_Sale ，它就會使用 Power Query 引擎來篩選數據表，並只保留最後 10 個數據列。
• Table.SelectColumns:P ower Query 會使用節點的 Table.LastN 輸出，並套用名為 Table.SelectColumns的新轉換，這會選取您想要從數據表中保留的特定數據行。

為了進行評估，此查詢必須從 fact_Sale 數據表下載所有數據列和欄位。 此查詢平均需要 6 分鐘和 1 秒，才能在 Power BI 數據流的標準實例中進行處理（這可考慮數據的評估和載入數據流）。

部分查詢折疊範例

連接到資料庫並瀏覽至 fact_Sale 資料表之後，您會從選取要從數據表保留的數據行開始。 從 [常用] 索引標籤，選取 [管理數據行] 群組內找到的 [選擇數據行轉換]。此轉換可協助您明確選取您想要從數據表中保留的數據行，並移除其餘的數據行。

在 [選擇數據行] 對話框中，選取 Sale Key、Customer Key、 DescriptionInvoice Date Key和 Quantity 數據行，然後選取 [確定]。

您現在建立邏輯，將排序數據表，讓數據表底部有最後一個銷售額。 選取數據 Sale Key 行，這是數據表的主鍵和累加序列或索引。 從數據行的操作功能表，只使用此欄位的遞增順序排序數據表。

接下來，選取數據表內容功能表，然後選擇 [ 保留底部數據列 轉換]。

在 [ 保留底部數據列] 中，輸入值 10，然後選取 [ 確定]。

下列程式代碼範例是您所建立查詢的完整 M 文稿：

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Choose columns" = Table.SelectColumns(Navigation, {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}),
  #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
  #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
  #"Kept bottom rows"

部分查詢折疊範例：瞭解查詢評估

檢查套用的步驟窗格時，您注意到查詢折迭指標會顯示您新增的最後一個轉換， Kept bottom rows標示為將在數據源外部評估的步驟，換句話說，由Power Query引擎評估。

您可以以滑鼠右鍵按下查詢的最後一個步驟，也就是名為 Kept bottom rows的查詢，然後選取 [查詢計劃 ] 選項，以進一步了解查詢的評估方式。

上一個 映像中的每個方塊稱為節點。 節點代表每個需要執行的程式（從左至右），以便評估查詢。 其中有些節點可以在您的數據源進行評估，而其他節點，例如 ，由 [保留底部數據列] 步驟所代表的節點Table.LastN，則會使用Power Query引擎來評估。

若要查看傳送至數據源的確切要求，請選取節點中的 Value.NativeQuery [檢視詳細數據]。

此要求是數據源的原生語言。 在此案例中，該語言為 SQL，而這個語句代表所有數據列的要求，且只有欄位所Sale Key排序數據表中要求的欄位fact_Sale。

諮詢此數據源要求可協助您進一步瞭解完整查詢計劃嘗試傳達的故事。 節點的順序是從從資料來源要求資料開始的循序程式：

• Sql.Database：連線 至資料庫，並傳送元數據要求來瞭解其功能。
• Value.NativeQuery：表示由Power Query產生以完成查詢的要求。 Power Query 會將原生 SQL 語句中的數據要求提交至數據源。 在此案例中，代表所有記錄，只有資料庫中數據表中要求的欄位 fact_Sale 會依 Sales Key 欄位以遞增順序排序。
• Table.LastN：一旦 Power Query 從數據表接收所有記錄 fact_Sale ，它就會使用 Power Query 引擎來篩選數據表，並只保留最後 10 個數據列。

為了進行評估，此查詢必須下載所有數據列，而且只能從 fact_Sale 數據表下載必要的欄位。 在 Power BI 數據流的標準實例中，平均需要 3 分 4 秒的時間處理（這可考慮數據的評估和載入數據流）。

完整查詢折疊範例

線上到資料庫並瀏覽至 fact_Sale 資料表之後，請從選取您要從資料表保留的數據行開始。 從 [常用] 索引標籤，選取 [管理數據行] 群組內找到的 [選擇數據行轉換]。此轉換可協助您明確選取您想要從數據表中保留的數據行，並移除其餘的數據行。

在 [選擇數據行] 中 Sale Key，選取 、 Customer Key、 Invoice Date Key、 Description和 Quantity 數據行，然後選取 [ 確定]。

您現在建立邏輯，將排序數據表，讓數據表頂端有最後一個銷售額。 選取數據 Sale Key 行，這是數據表的主鍵和累加序列或索引。 僅以數據行操作功能表的遞減順序來排序數據表。

接下來，選取數據表內容功能表，然後選擇 [ 保留頂端數據列 轉換]。

在 [ 保留頂端數據列] 中，輸入值 10，然後選取 [ 確定]。

下列程式代碼範例是您所建立查詢的完整 M 文稿：

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Choose columns" = Table.SelectColumns(Navigation, {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}),
  #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
  #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
  #"Kept top rows"

完整查詢折疊範例：瞭解查詢評估

檢查套用的步驟窗格時，您會發現查詢折迭指標會顯示您新增的轉換、 選擇資料行、 已排序的數據列，以及 [保留頂端數據列] 標示為將在數據源評估的步驟。

您可以以滑鼠右鍵按下查詢的最後一個步驟，也就是名為 [保留頂端數據列] 的查詢，然後選取讀取 查詢計劃的選項。

此要求是數據源的原生語言。 在此情況下，該語言為 SQL，而這個語句代表數據表中所有數據列和欄位 fact_Sale 的要求。

諮詢此數據來源查詢可協助您進一步瞭解完整查詢計劃嘗試傳達的故事：

• Sql.Database：連線 至資料庫，並傳送元數據要求來瞭解其功能。
• Value.NativeQuery：表示由Power Query產生以完成查詢的要求。 Power Query 會將原生 SQL 語句中的數據要求提交至數據源。 在此案例中，它只代表對數據表前 10 筆記錄 fact_Sale 的要求，使用 字段以遞減順序 Sale Key 排序之後，只有必要的字段。

注意

雖然沒有子句可用來選取 T-SQL 語言中數據表的底部數據列，但有一個 TOP 子句會擷取數據表的頂端數據列。

為了進行評估，此查詢只會下載 10 個數據列，且只有您從 fact_Sale 數據表要求的欄位。 此查詢在 Power BI 數據流的標準實例中平均處理了 31 秒（這可考慮評估數據並將其載入數據流）。

表現比較

若要進一步了解查詢折疊在這些查詢中的影響，您可以重新整理查詢、記錄完整重新整理每個查詢所需的時間，並加以比較。 為了簡單起見，本文提供使用 Power BI 數據流重新整理機械師擷取的平均重新整理時間，同時以 DW2000c 作為服務等級連線到專用的 Azure Synapse Analytics 環境。

每個查詢的重新整理時間如下所示：

範例	標籤	以秒為單位的時間
沒有查詢折疊	無	361
部分查詢折疊	Partial	184
完整查詢折疊	完整	31

通常的情況是，完全折疊回數據源的查詢會優於未完全折回數據源的類似查詢。 原因有很多。 這些原因的範圍從查詢執行的轉換複雜度，到數據源上實作的查詢優化，例如索引和專用運算，以及網路資源。 不過，查詢折迭會嘗試使用這兩個特定主要進程，以將這兩個進程與 Power Query 的影響降到最低：

• 傳輸中資料
• Power Query 引擎所執行的轉換

下列各節說明這兩個進程在先前提及的查詢中的影響。

傳輸中資料

執行查詢時，它會嘗試從數據源擷取數據做為其第一個步驟之一。 從數據源擷取的數據是由查詢折疊機制所定義。 此機制會識別可卸除至數據源之查詢中的步驟。

下表列出從 fact_Sale 資料庫數據表要求的數據列數目。 此數據表也包含 SQL 語句的簡短描述，以從數據源要求這類數據。

範例	標籤	要求的數據列	描述
沒有查詢折疊	無	3644356	要求數據表中的所有欄位和所有記錄fact_Sale
部分查詢折疊	Partial	3644356	要求所有記錄，但只有數據表中的必要字段 fact_Sale 之後，才依 Sale Key 欄位排序
完整查詢折疊	完整	10	只要求必要欄位和數據表的前 10 筆記錄 fact_Sale 之後，依 Sale Key 字段的遞減順序排序

從數據源要求數據時，數據源必須計算要求的結果，然後將數據傳送給要求者。 雖然已提及計算資源，但將數據從數據源移至 Power Query 的網路資源，然後讓 Power Query 能夠有效地接收數據，併為本機發生的轉換做好準備，可能需要一些時間，視數據的大小而定。

針對展示的範例，Power Query 必須向數據源要求超過 360 萬個數據列，以取得無查詢折疊和部分查詢折疊範例。 針對完整查詢折疊範例，它只要求 10 個數據列。 針對要求的欄位，沒有查詢折疊範例會要求數據表中的所有可用欄位。 部分查詢折疊和完整查詢折疊範例都只針對所需的欄位提交要求。

警告

建議您實作累加式重新整理解決方案，以針對具有大量數據的查詢或數據表使用查詢折疊。 Power Query 的不同產品整合會實作逾時，以終止長時間執行的查詢。 某些數據源也會在長時間執行的會話上實作逾時，嘗試對其伺服器執行昂貴的查詢。 詳細資訊： 搭配數據流 使用累加式重新整理和 語意模型的累加式重新整理

Power Query 引擎所執行的轉換

本文示範如何使用 查詢計劃 來進一步瞭解查詢的評估方式。 在查詢計劃內，您可以看到 Power Query 引擎將執行的轉換作業確切節點。

下表顯示來自 Power Query 引擎所評估先前查詢之查詢計劃的節點。

範例	標籤	Power Query 引擎轉換節點
沒有查詢折疊	無	Table.LastN, Table.SelectColumns
部分查詢折疊	Partial	Table.LastN
完整查詢折疊	完整	—

針對本文所展示的範例，完整查詢折疊範例不需要在Power Query引擎內進行任何轉換，因為必要的輸出數據表會直接從數據源取得。 相反地，其他兩個查詢需要Power Query引擎進行一些計算。 由於這兩個查詢需要處理的數據量，因此這些範例的程式花費的時間比完整查詢折疊範例還要多。

轉換可以分組為下列類別：

運算子的類型	描述
遠端	數據源節點的運算子。 這些運算子的評估發生在Power Query之外。
串流	運算子是傳遞運算符。 例如， Table.SelectRows 使用簡單的篩選通常會在結果通過 運算子時篩選結果，而且在移動數據之前不需要收集所有數據列。 Table.SelectColumns 和 Table.ReorderColumns 是這類運算子的其他範例。
完整掃描	需要收集所有數據列的運算符，數據才能繼續移至鏈結中的下一個運算符。 例如，若要排序數據，Power Query 必須收集所有數據。 完整掃描運算子的其他範例包括 Table.Group、 Table.NestedJoin和 Table.Pivot。

提示

雖然並非每個轉換都與效能觀點相同，但在大多數情況下，轉換較少通常較好。

考慮和建議

• 遵循建立新查詢時的最佳做法，如 Power Query 中的最佳做法中所述。
• 使用查詢折疊指標來檢查哪些步驟會防止查詢折疊。 如有必要，請重新排列它們以增加折疊。
• 使用查詢計劃來判斷特定步驟的Power Query引擎發生哪些轉換。 請考慮重新排列步驟來修改現有的查詢。 然後再次檢查查詢最後一個步驟的查詢計劃，並查看查詢計劃看起來是否比上一個步驟更好。 例如，新的查詢計劃具有比前一個節點少的節點，而且大部分節點都是「串流」節點，而不是「完整掃描」。 對於支援摺疊的數據源，除了 Value.NativeQuery 和數據源存取節點以外的任何節點都代表未折疊的轉換。
• 當可用時，您可以使用 [檢視原生查詢] 或 [檢視數據源查詢] 選項，確保您的查詢可以折迭回數據源。 如果此步驟已停用此選項，而且您使用的是通常啟用它的來源，則您已建立一個停止查詢折疊的步驟。 如果您使用不支援此選項的來源，則可以依賴查詢折迭指標和查詢計劃。
• 使用查詢診斷工具來進一步了解在連接器可使用查詢折疊功能時，傳送至數據源的要求。
• 結合多個連接器的數據源時，Power Query 會嘗試盡可能將工作推送至這兩個數據源，同時符合針對每個數據源定義的隱私權層級。
• 請閱讀隱私權 層級 的文章，以保護您的查詢免於針對數據隱私權防火牆錯誤執行。
• 使用其他工具來檢查數據源所接收要求的查詢折疊。 根據本文中的範例，您可以使用 Microsoft SQL Server Profiler 來檢查 Power Query 所傳送的要求，並由 Microsoft SQL Server 接收。
• 如果您將新步驟新增至完全折疊的查詢，而新的步驟也會折疊，Power Query 可能會將新的要求傳送至數據源，而不是使用先前結果的快取版本。 在實務上，此程式可能會導致少量數據的簡單作業需要比預期更長的時間重新整理。 此較長的重新整理是因為 Power Query 會重新查詢數據源，而不是關閉數據的本機複本。