在本系列的第 18 篇文章中莫秆，我詳細(xì)講解了從 MS Access 數(shù)獲取數(shù)據(jù)器瘪，通過 PQ 完成進(jìn)出存查詢的過程昧辽。在示例中应又， stock_movement_details 查詢大約 28000+ 行运怖，計(jì)算出基于月份的進(jìn)出存大致耗時(shí) 20 秒左右孕锄。使用 Excel 實(shí)現(xiàn)這樣的輸出報(bào)表有一定難度迹冤，從這個(gè)角度來說 PQ 是一個(gè)巨大的飛躍入挣。但 28000 條的數(shù)據(jù)耗時(shí) 20 秒嚣州，性能就比較低了鲫售，這引起了我的好奇。經(jīng)過一番思考和探索该肴，發(fā)現(xiàn)了一些可以提高性能的做法情竹。

查看 PQ 查詢消耗的時(shí)間可以這樣做，在 Excel 結(jié)果輸出表中沙庐，右鍵選擇菜單的【刷新】鲤妥，或者在右邊【查詢&連接】面板中點(diǎn)擊【刷新】按鈕佳吞，啟動(dòng)數(shù)據(jù)刷新。如果數(shù)據(jù)計(jì)算和上載耗時(shí)比較長(zhǎng)棉安，在Excel 狀態(tài)欄出現(xiàn) “正在后臺(tái)執(zhí)行查詢...” 的提示底扳。點(diǎn)擊這個(gè)提示，可以中斷刷新贡耽，或者觀察查詢的耗時(shí)衷模。不過這個(gè)界面設(shè)計(jì)的不夠友好，如果查詢耗時(shí)比較短蒲赂，就很難調(diào)出對(duì)話框查看; 另外執(zhí)行完畢后阱冶，計(jì)時(shí)器也不停止，只能用眼睛觀察究竟用了多長(zhǎng)時(shí)間滥嘴。

image

• 數(shù)據(jù)從數(shù)據(jù)源加載到 Power Query
• Power Query 處理數(shù)據(jù) (transformations)
• 數(shù)據(jù)上載到 Excel 工作表

當(dāng)然這個(gè)過程消耗的時(shí)間還有很多外在因素木蹬，比如從數(shù)據(jù)庫(kù)加載數(shù)據(jù)受網(wǎng)絡(luò)的影響，從本地加載數(shù)據(jù)也受內(nèi)存大小和硬盤讀寫速度的影響若皱。如何確定這個(gè)耗時(shí)是誰的責(zé)任呢镊叁？經(jīng)過搜索，也沒有發(fā)現(xiàn)很好的方法走触。網(wǎng)上有介紹 PQ 的 Query Folding -- 簡(jiǎn)單的說晦譬，就是 Power Query 與關(guān)系型數(shù)據(jù)庫(kù)、OData 等數(shù)據(jù)源連接的時(shí)候互广，會(huì)考慮將一些數(shù)據(jù)處理 (transformation) 傳回?cái)?shù)據(jù)源進(jìn)行處理敛腌，從而提高速度。能執(zhí)行 Query Folding 的數(shù)據(jù)源包括：

• Relational sources (SQL Server, Oracle, …). They support most Power Query functionality.
• OData sources (such as a SharePoint list for example and the Azure Marketplace)
• Active Directory
• Exchange
• HDFS, Folder.Files and Folder.Contents (for basic operations on paths)

SQL Server 數(shù)據(jù)庫(kù)的 SQL Server Managment Studio 提供了 SQL Server Profiler 工具惫皱，所以為了比較和觀察像樊，我立即將數(shù)據(jù)源切換到 SQL Server，執(zhí)行相同的計(jì)算后逸吵，發(fā)現(xiàn)在 MS Access 中原來需要 20 秒的操作縮短到 2 秒左右凶硅。這說明 Query Folding 在提高新能方面確實(shí)起了作用，有 SQL Server 參與計(jì)算的功勞扫皱。根據(jù)文章的介紹，我也近距離觀察了 Power Query query folding 的一些細(xì)節(jié)捷绑。folding 在英文中主要是彎曲韩脑、折疊的意思，也有 mix an ingredient with another ingredient 的意思粹污，所以我將其翻譯為混合查詢段多，不一定正確。

在查詢編輯器中壮吩，選擇右邊步驟进苍，右鍵菜單有查看本機(jī)查詢菜單項(xiàng)加缘，如果菜單為灰色，表示本步驟是 Power Query 做的處理觉啊，如果不是灰色拣宏，表示該步驟由 PQ 送回?cái)?shù)據(jù)源（比如數(shù)據(jù)庫(kù)）進(jìn)行處理。比如杠人，我們選擇 ExpandedCols 這一步驟勋乾，查看本機(jī)查詢：

image

可以看到這一步驟的操作實(shí)際上是一個(gè) SQL 語句：

image

下面我們進(jìn)入 SQL Server Management Studio 來觀察這一過程。通過菜單 【工具】- 【SQL Server Profiler】打開 一個(gè)新的 Profiler：

image

image

沒有運(yùn)行之前忌锯，Profiler 的界面如下：

image

刷新之后，Profiler 界面如下：

image

在這個(gè)界面可以觀察完整過程领炫，比如 SQL Server 執(zhí)行了哪些 SQL 語句偶垮，每一步驟消耗了多長(zhǎng)時(shí)間。

不使用 Query Folding

• 如果在查詢中使用了 Table.Buffer 函數(shù)對(duì)查詢表進(jìn)行緩存帝洪，則不會(huì)啟動(dòng) query folding 功能似舵，可以對(duì)數(shù)據(jù)源調(diào)用 Table.Buffer 函數(shù)，然后在 SQL Server Profiler 中測(cè)試看看兩種方法的 sql 語句有什么不同葱峡。
• 如果在查詢中使用了自定義的 SQL 語句砚哗，則不會(huì)啟動(dòng) query folding 功能

其他還有一些不會(huì)啟動(dòng) query folding 的場(chǎng)景，個(gè)人覺得沒有必要刻意去記砰奕。有興趣的話請(qǐng)參考我在本文的參考部分列出的文章蛛芥，里面有具體說明。

一些觀察的結(jié)論

• SQL Server 數(shù)據(jù)庫(kù)啟用 query folding 能提高性能军援，因?yàn)?SQL Server 作為專門的數(shù)據(jù)庫(kù)仅淑，在服務(wù)器端運(yùn)行，肯定比客戶端的 Power Query 有更高性能
• MS Access 數(shù)據(jù)庫(kù)如果對(duì)數(shù)據(jù)源調(diào)用 Table.Buffer胸哥，反而性能下降得非常厲害涯竟，不知道什么原因。在工作表刷新數(shù)據(jù)的過程中，Windows 任務(wù)管理器顯示有兩個(gè)與 Power Query 相關(guān)的進(jìn)程庐船，但耗用內(nèi)存不大银酬，也沒有在計(jì)算的過程中占用更大的內(nèi)存。
• 連接 csv 文件中的數(shù)據(jù)筐钟，PQ 處理的耗時(shí)也沒有比 MS Access 更慢揩瞪，說明 PQ 本身的處理性能還是可以的。
• 按網(wǎng)上的說法盗棵，在循環(huán)中（比如 List.Generate 函數(shù)）使用 Table.Buffer 能提高性能壮韭，未測(cè)試。

示例數(shù)據(jù)

github

參考

Query Folding in Power Query to Improve Performance