查詢折迭範例

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

案例

想像一下，使用Wide World Importers 資料庫進行 Azure Synapse Analytics SQL 資料庫時，您負責在 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 KeyDescription 、 和 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：Power 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分析環境。

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

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

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

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

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

資料傳輸中

當查詢執行時，它會嘗試從資料來源擷取資料做為其第一個步驟之一。 從資料來源擷取哪些資料是由查詢折迭機制所定義。 此機制會識別可從查詢卸載至資料來源的步驟。

下表列出從 fact_Sale 資料庫資料表要求的資料列數目。 資料表也包含 SQL 語句的簡短描述，這些語句會傳送給資料來源要求這類資料。

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

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

針對展示的範例，Power Query必須向資料來源要求超過 3.6 百萬個數據列，而沒有查詢折迭和部分查詢折迭範例。 針對完整的查詢折迭範例，它只要求 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來檢查 Microsoft Power Query傳送的要求，並由 Microsoft SQL Server接收。
• 如果您將新步驟新增至完全折迭的查詢，而新步驟也會折迭，Power Query可能會將新要求傳送至資料來源，而不是使用先前結果的快取版本。 在實務上，此程式可能會導致少量資料上看似簡單的作業，需要比預期更久的時間重新整理預覽。 這項較長的重新整理是因為Power Query重新查詢資料來源，而不是處理資料的本機複本。