閱讀英文

共用方式為


Power Query 的查詢計劃 (預覽)

Power Query 的查詢計劃是一項功能,可讓您更清楚地檢視查詢的評估。 有助於判斷特定查詢為何無法在特定步驟折疊。

透過實際範例,本文將示範使用查詢計劃功能來檢閱查詢步驟的主要使用案例和潛在優點。 本文中使用的範例已使用適用於 Azure SQL Server 的 AdventureWorksLT 範例資料庫來建立,您可以從 AdventureWorks 範例資料庫下載

注意

Power Query 的查詢計劃功能僅適用於 Power Query Online。

建議的程式,透過檢閱查詢折疊指標,在 Power Query 中使用查詢計畫功能,然後檢閱所選步驟的查詢計劃,最後實作從檢閱查詢計劃衍生的任何變更。

本文已分成一系列建議步驟,以解譯查詢計劃。 這些步驟如下:

  1. 檢閱查詢折迭指標
  2. 選取查詢步驟以檢閱其查詢計劃
  3. 實作查詢的變更。

使用下列步驟,在您自己的 Power Query Online 環境中建立查詢。

  1. Power Query - 選擇資料源,選取 [ 空白查詢]。

  2. 將空白查詢的文稿取代為下列查詢。

    let
      Source = Sql.Database("servername", "database"),
      Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
      #"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
      #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
      #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
    in
      #"Kept bottom rows"
    
  3. 將和 database 變更servername為您自己的環境的正確名稱。

  4. (選擇性)如果您嘗試連線到內部部署環境的伺服器和資料庫,請務必設定該環境的閘道。

  5. 選取 [下一步]。

  6. 在 Power Query 編輯器 中,選取 [設定連線],並將認證提供給您的數據源。

注意

如需連線到 SQL Server 的詳細資訊,請移至 SQL Server 資料庫

遵循這些步驟之後,您的查詢看起來會像下圖中的查詢。

已啟用查詢折迭指標的範例查詢。 此查詢會連線到 SalesOrderHeader 數據表,並從最後五個 訂單中選取一些數據行,其 TotalDue 值超過 1000。

注意

本文使用簡化的範例來展示這項功能,但本文所述的概念適用於所有查詢。 建議您先了解查詢折疊,再閱讀查詢計劃。 若要深入了解查詢折疊,請移至 查詢折疊基本概念

1.檢閱查詢折迭指標

注意

閱讀本節之前,建議您先檢閱有關查詢折迭指標的文章

此程式中的第一個步驟是檢閱您的查詢,並密切關注查詢折疊指標。 目標是檢閱標示為未折疊的步驟。 然後,您可以看到對整體查詢進行變更是否可以讓這些轉換完全折疊。

[套用的步驟] 窗格中範例查詢的查詢折迭指標。

在此範例中,唯一無法折疊的步驟是 [保留底部數據列],這很容易透過 未折疊 的步驟指標來識別。 此步驟也是查詢的最後一個步驟。

現在的目標是檢閱此步驟,並瞭解要折回數據源的內容,以及無法折疊的內容。

2.選取查詢步驟以檢閱其查詢計劃

您已將 [保留底部數據列] 步驟識別為感興趣的步驟,因為它不會折回數據源。 以滑鼠右鍵按兩下步驟,然後選取 [ 檢視查詢計劃] 選項。 此動作會顯示新的對話框,其中包含所選步驟之查詢計劃的圖表。

[查詢計劃] 對話框,顯示查詢計劃的圖表檢視,其中包含以線條連接的節點。Power Query 會嘗試利用延遲評估和查詢折疊來優化查詢,如查詢折疊基本概念中所述。 此查詢計劃代表 M 查詢的優化轉譯至傳送至數據源的原生查詢。 它也包含Power Query Engine 所執行的任何轉換。 節點出現的順序會遵循查詢的順序,從您查詢的最後一個步驟或輸出開始,其表示於圖表最左邊,在此案例中為 Table.LastN 節點,代表 [保留的底部數據列 ] 步驟。

在對話框底部,有圖示可協助您放大或縮小查詢計劃檢視的列,以及其他按鈕可協助您管理檢視。 針對上一個影像, 此列的 [適合檢視 ] 選項是用來更了解節點。

查詢計劃對話框,節點會放大以取得較佳的檢視。

注意

查詢計劃代表優化的計劃。 當引擎評估查詢時,它會嘗試將所有運算元折疊成數據源。 在某些情況下,它甚至可能會執行一些內部重新排序的步驟,以最大化折疊。 請記住,此優化查詢計劃中剩餘的節點/運算子通常包含「折疊」數據源查詢,以及無法折疊且會在本機評估的任何運算符。

識別其他節點的折疊節點

您可以將此圖表中的節點識別為兩個群組:

  • 折疊節點:此節點可以是 Value.NativeQuery 或 「資料源」節點,例如 Sql.Database。 您也可以使用函式名稱下的標籤遠端來識別這些標籤
  • 非折疊節點:其他數據表運算子,例如 Table.SelectRows無法折疊的 、 Table.SelectColumns和其他函式。 您也可以使用標籤「完整掃描」和「串流」來識別這些標籤

下圖顯示紅色矩形內的折疊節點。 其餘節點無法折疊回數據源。 您必須檢閱其餘節點,因為目標是嘗試讓這些節點折回數據源。

對話框底部的查詢計劃檢視控件,其中已選取 [適合檢視] 選項。 您可以選取 某些節點底部的 [檢視詳細資料 ] 以顯示擴充資訊。 例如,節點的詳細數據 Value.NativeQuery 會顯示將傳送至數據源的原生查詢(在 SQL 中)。

查詢計劃中 Value.NativeQuery 節點的詳細數據檢視。 此處顯示的查詢可能與傳送至數據源的查詢可能完全相同,但這是很好的近似值。 在此情況下,它會告訴您要從 SalesOrderHeader 數據表查詢哪些數據行,然後如何使用 TotalDue 字段篩選該數據表,只取得該欄位值大於 1000 的數據列。 Power Query 引擎在本機計算其旁邊的 節點 Table.LastN,因為它無法折疊。

注意

運算子可能與查詢文本中使用的函式不完全相符。

檢閱非折疊節點,並考慮採取動作讓您的轉換折疊

您現在已判斷哪些節點無法折疊,且將在本機進行評估。 此案例只有 Table.LastN 節點,但在其他案例中,可能會有更多節點。

目標是將變更套用至查詢,以便折疊步驟。 您可能會實作的一些變更範圍從重新排列步驟到將替代邏輯套用至數據源更明確的查詢。 這並不表示所有查詢和所有作業都可以套用一些變更來折疊。 但最好透過試用和錯誤判斷您的查詢是否可以折疊回來。

由於數據源是 SQL Server 資料庫,如果目標是從數據表中擷取最後五個訂單,則最好是利用 SQL 中的 TOPORDER BY 子句。 由於 SQL 中沒有 BOTTOM 子句, Table.LastN 因此 PowerQuery 中的轉換無法轉譯為 SQL。 您可以移除步驟, Table.LastN 並將它取代為:

  • 數據表中 SalesOrderID 資料行的排序遞減步驟 ,因為此數據行會決定第一個順序,以及最後輸入的順序。
  • 選取資料表已排序後的前五個數據列 ,此轉換會完成與 [ 保留] 底端數據列Table.LastN) 相同。

這個替代方式相當於原始查詢。 雖然理論上這個替代方案似乎不錯,但您需要進行變更,以查看此替代專案是否會讓這個節點完全折迭回數據源。

3.實作查詢的變更

實作上一節所討論的替代方法:

  1. 關閉查詢計劃對話框,並返回 Power Query 編輯器。

  2. 拿掉 [ 保留底部數據列 ] 步驟。

  3. 遞減順序排序 SalesOrderID 資料行。

    使用自動篩選功能表,以遞減順序排序 SalesOrderID 資料行。

  4. 選取數據預覽檢視左上角的數據表圖示,然後選取讀取 [保留頂端數據列] 的選項。 在對話框中,傳遞數位 5 做為自變數,然後按 [確定]。

    使用數據表操作功能選取 [保留頂端數據列] 轉換,只保留前五個數據列。

實作變更之後,請再次檢查查詢折迭指標,並查看它是否提供折迭指標。

所有查詢折疊指標都是綠色的,並顯示其可折疊。最後一個數據表會提供相同的數據列,但順序不同。 現在是時候檢閱最後一個步驟的查詢計劃,也就是[ 保留最上層數據列]。 現在只有折疊的節點。 選取 [檢視詳細數據 ] 底下 Value.NativeQuery ,以確認哪個查詢正在傳送至資料庫。

對查詢進行變更之後,新的查詢計劃現在只會展示折疊的節點,而 Value.NativeQuery 會顯示評估查詢的完整 SQL 語句。

雖然本文建議套用什麼替代方案,但主要目標是讓您瞭解如何使用查詢計劃來調查查詢折疊。 本文也提供傳送至數據源的內容,以及將在本機執行哪些轉換的可見度。

您可以調整程式代碼,以查看它在查詢中的影響。 藉由使用查詢折迭指標,您也會更清楚哪些步驟會防止查詢折疊。