Table Buffer Power Query, Power Query M, BufferMode Eager, BufferMode Delayed, List Buffer, Table StopFolding, Query Folding, Lazy Evaluation Power Query, Materialization Power Query, Buffering در Power Query, بهینه سازی Power Query, Power BI Refresh Performance, Power Query Performance, Power BI ETL

فهرست مطالب

در Power Query، بسیاری از مشکلات Performance یا رفتارهای غیرمنتظره را نمی‌توان فقط با نگاه کردن به ترتیب ظاهری مراحل Query توضیح داد. کدی که در پنجره Power Query Editor می‌بینیم، مجموعه‌ای از Expressionها در زبان M است و موتور Power Query هنگام ارزیابی آن‌ها تصمیم می‌گیرد چه بخش‌هایی واقعاً موردنیاز هستند و چه بخش‌هایی می‌توانند به منبع داده منتقل شوند.

یکی از مفاهیم مهم در این میان، Lazy Evaluation یا ارزیابی تنبل است. در زبان M، بسیاری از Expressionهای مربوط به List، Record، Table و ساختار let زمانی ارزیابی می‌شوند که مقدار آن‌ها واقعاً موردنیاز باشد. بنابراین ترتیب نوشته شدن مراحل Query الزاماً به این معنی نیست که هر مرحله دقیقاً همان لحظه و به‌صورت مستقل اجرا می‌شود. Microsoft نیز Lazy Evaluation را یکی از بخش‌های مهم مدل ارزیابی M و فرآیند بهینه‌سازی Query معرفی می‌کند.

در چنین شرایطی، گاهی لازم است نتیجه یک Table در نقطه مشخصی از Query در حافظه قرار بگیرد و ارزیابی آن در همان نقطه انجام شود. اینجاست که Table.Buffer اهمیت پیدا می‌کند.

Table.Buffer جدولی را در حافظه Buffer می‌کند و آن را در طول ارزیابی از تغییرات خارجی جدا می‌سازد. این تابع می‌تواند در برخی سناریوها به جلوگیری از ارزیابی‌های تکراری یا تثبیت داده‌ای که در طول ارزیابی ممکن است تغییر کند کمک کند، اما نباید آن را یک ابزار عمومی برای افزایش سرعت Query در نظر گرفت. حتی Microsoft صراحتاً اشاره می‌کند که Buffering ممکن است Query را کندتر کند، زیرا خواندن داده و نگهداری آن در حافظه هزینه دارد و Buffering جلوی Query Folding مراحل بعدی را می‌گیرد.

به همین دلیل، استفاده صحیح از Table.Buffer بیشتر از اینکه به حفظ کردن Syntax آن وابسته باشد، به درک درست از زمان ارزیابی داده، Materialization، Query Folding و هزینه حافظه وابسته است.

در این مقاله بررسی می‌کنیم Table.Buffer دقیقاً چیست، Lazy Evaluation چه نقشی در رفتار Power Query دارد، Buffering چه چیزی را تغییر می‌دهد، چه تفاوتی با List.Buffer و Table.StopFolding دارد، BufferMode چگونه کار می‌کند، چرا قرار دادن Buffer در نقطه اشتباه می‌تواند Performance را بدتر کند، چه زمانی استفاده از آن منطقی است و در پروژه‌های سازمانی Power BI چه Best Practiceهایی باید رعایت شوند.

Lazy Evaluation در Power Query چیست؟

برای درک Table.Buffer ابتدا باید با مدل ارزیابی زبان M آشنا شویم.

در بسیاری از زبان‌های برنامه‌نویسی، وقتی یک دستور یا Expression نوشته می‌شود، انتظار داریم آن Expression مطابق ترتیب اجرای برنامه محاسبه شود. اما در Power Query M چنین برداشتی همیشه درست نیست.

زبان M از Lazy Evaluation برای List، Record، Table و Expressionهای let استفاده می‌کند. یعنی مقدار این ساختارها می‌تواند زمانی محاسبه شود که واقعاً به آن نیاز باشد.

برای مثال:

let
    Source = Excel.Workbook (
        File.Contents ( "C:\Data\Sales.xlsx" )
    ),
    FilteredRows = Table.SelectRows (
        Source,
        each [Amount] > 1000
    ),
    SortedRows = Table.Sort (
        FilteredRows,
        { "Amount", Order.Descending }
    )
in
    SortedRows

در ظاهر سه مرحله اصلی داریم:

Source
   ↓
FilteredRows
   ↓
SortedRows

اما این نمودار را نباید با یک برنامه خطی کلاسیک اشتباه گرفت.

موتور M ابتدا Query را ارزیابی می‌کند تا مشخص شود خروجی نهایی به چه مقادیری نیاز دارد. سپس بخش‌های مورد نیاز را محاسبه می‌کند و در منابعی که از Query Folding پشتیبانی می‌کنند، تلاش می‌کند عملیات قابل انتقال را به منبع داده Push کند.

بنابراین:

کد M
   ↓
تحلیل Expressionها
   ↓
تشخیص داده موردنیاز
   ↓
بررسی قابلیت Query Folding
   ↓
ارسال عملیات قابل انتقال به منبع
   ↓
پردازش باقی‌مانده در Power Query Engine

این مدل یکی از دلایل مهمی است که Power Query می‌تواند Queryهای پیچیده را به شکل متفاوتی از ترتیب ظاهری کد اجرا کند. Microsoft نیز توضیح می‌دهد که Lazy Evaluation در کنار Query Folding در فرآیند بهینه‌سازی Query نقش دارد.

آیا Lazy Evaluation یعنی Power Query همیشه Query را چند بار اجرا می‌کند؟

خیر، این یکی از مهم‌ترین سوءبرداشت‌ها درباره Lazy Evaluation است.

Lazy Evaluation به این معنی نیست که Power Query هر مرحله را چند بار اجرا می‌کند. مفهوم اصلی این است که ارزیابی زمانی انجام می‌شود که مقدار موردنظر لازم باشد.

ممکن است در یک Query خاص، نتیجه یک Expression چند بار موردنیاز قرار بگیرد و بسته به شرایط ارزیابی، منبع یا Expression مربوطه دوباره مورد استفاده قرار گیرد. اما نباید از این موضوع نتیجه گرفت که هر Reference الزاماً باعث یک خواندن مجدد از منبع می‌شود.

این تفاوت برای درک Table.Buffer بسیار مهم است.

پس بهتر است این دو مفهوم را از هم جدا کنیم:

Lazy Evaluation
=
ارزیابی زمانی که مقدار موردنیاز است

Table.Buffer
=
Buffer کردن نتیجه Table در یک نقطه مشخص از ارزیابی

بنابراین Table.Buffer را نباید به‌عنوان «خاموش‌کننده Lazy Evaluation در کل Query» در نظر گرفت.

Table.Buffer چیست؟

Syntax فعلی تابع Table.Buffer به شکل زیر است:

Table.Buffer (
    table as table,
    optional options as nullable record
) as table

این تابع یک Table را دریافت می‌کند و آن را در حافظه Buffer می‌کند. طبق مستندات Microsoft، Buffer کردن باعث می‌شود Table در طول ارزیابی از تغییرات خارجی جدا شود. Buffering نیز Shallow است، یعنی مقادیر Scalar سلول‌ها ارزیابی می‌شوند، اما مقادیر غیر Scalar مانند Record، List و Table به‌صورت عمیق Buffer نمی‌شوند.

یک نمونه ساده:

let
    Source = Excel.Workbook (
        File.Contents ( "C:\Data\Sales.xlsx" )
    ),
    BufferedSource = Table.Buffer ( Source )
in
    BufferedSource

در این Query، نتیجه Source در نقطه‌ای که Table.Buffer اعمال می‌شود، Buffer می‌شود. از این نقطه به بعد، عملیات Downstream روی Table Buffer شده انجام می‌شوند.

این موضوع با یک مفهوم مهم همراه است:

Table.Buffer داده را Materialize می‌کند، نه اینکه صرفاً نام مرحله را تغییر دهد.

Materialization در Table.Buffer یعنی چه؟

Materialization را می‌توان ساده این‌طور توضیح داد:

فرض کنید قبل از Buffer، Query شما یک دستور برای به‌دست‌آوردن داده است.

Source
  ↓
Transformations
  ↓
Result

تا زمانی که داده موردنیاز نباشد، بخش‌هایی از این زنجیره می‌توانند Lazy باقی بمانند.

وقتی Table.Buffer وارد می‌شود:

Source
  ↓
Transformations
  ↓
Table.Buffer
  ↓
Table موجود در حافظه
  ↓
Transformations بعدی

در این نقطه، نتیجه Table برای مصرف بعدی در حافظه Buffer می‌شود.

به همین دلیل، Table.Buffer را می‌توان یک مرز Materialization در Query دانست.

Table.Buffer چه چیزی را تضمین می‌کند؟

نباید Table.Buffer را با چند مفهوم مختلف اشتباه گرفت.

Table.Buffer:

  • Table را در حافظه Buffer می‌کند.
  • ارزیابی مقادیر Scalar را در Buffering مجبور می‌کند.
  • Table را در طول ارزیابی از تغییرات خارجی جدا می‌کند.
  • Query Folding مراحل بعدی را متوقف می‌کند.
  • می‌تواند در بعضی سناریوها از ارزیابی دوباره یک داده Buffer شده جلوگیری کند.

اما:

  • تضمین نمی‌کند Query همیشه سریع‌تر شود.
  • تضمین نمی‌کند کل Query فقط یک بار منبع را بخواند.
  • به‌تنهایی ابزار مدیریت ترتیب منطقی داده نیست.
  • جایگزین طراحی صحیح Query نیست.
  • جایگزین Query Folding نیست.
  • جایگزین Index مناسب در SQL Server نیست.

Microsoft نیز صراحتاً هشدار می‌دهد که استفاده از Table.Buffer ممکن است Query را سریع‌تر یا کندتر کند.

Buffering سطحی است، نه Deep Buffering

یکی از نکات فنی مهمی که معمولاً در مقالات ساده درباره Table.Buffer نادیده گرفته می‌شود، Shallow Buffering است.

فرض کنید Table شما شامل چنین ساختاری باشد:

CustomerID
CustomerName
Address
Orders

و ستون Orders شامل یک Table یا ساختار غیر Scalar باشد.

Table.Buffer الزاماً تمام ساختارهای داخلی را تا عمیق‌ترین سطح در حافظه Materialize نمی‌کند.

طبق مستندات Microsoft، Buffering روی مقادیر Scalar اعمال می‌شود، اما مقادیر غیر Scalar مانند Record، List و Table به‌صورت عمیق Buffer نمی‌شوند.

این موضوع در Queryهای پیچیده‌ای که شامل Nested Table یا Record هستند اهمیت زیادی دارد.

یک مثال ساده از Table.Buffer

فرض کنیم جدول فروش زیر را داریم:

MonthNumber    MonthlySales
1              120000000
2              145000000
3               98000000
4              160000000

Query:

let
    Source = MonthlySales,
    BufferedSales = Table.Buffer ( Source ),
    SortedSales = Table.Sort (
        BufferedSales,
        { "MonthNumber", Order.Ascending }
    )
in
    SortedSales

در اینجا Table قبل از Sort Buffer شده است.

اما آیا این کار بهتر است؟

لزوماً خیر.

اگر Source از SQL Server آمده باشد و Sort قابلیت Folding داشته باشد، قرار دادن Table.Buffer قبل از Sort می‌تواند باعث شود Sort دیگر به SQL Server منتقل نشود. بنابراین در چنین شرایطی Buffer کردن زودهنگام می‌تواند حتی Performance را بدتر کند.

محل قرارگیری Table.Buffer اهمیت زیادی دارد

فرض کنیم منبع SQL Server داریم:

let
    Source = Sql.Database (
        "ServerName",
        "SalesDB"
    ),
    Sales = Source{[
        Schema = "dbo",
        Item = "FactSales"
    ]}[Data],
    FilteredSales = Table.SelectRows (
        Sales,
        each [OrderDate] >= #date ( 2025, 1, 1 )
    ),
    BufferedSales = Table.Buffer (
        FilteredSales
    )
in
    BufferedSales

در این ساختار، Filter قبل از Buffer قرار گرفته است.

اگر این Filter توسط منبع قابل Fold شدن باشد، Power Query می‌تواند تلاش کند آن را به SQL Server منتقل کند.

در نتیجه:

SQL Server
   ↓
Filter روی داده
   ↓
نتیجه کوچک‌تر
   ↓
Power Query
   ↓
Table.Buffer
   ↓
حافظه

این معمولاً از نظر معماری منطقی‌تر از Buffer کردن جدول خام است.

الگوی نامناسب برای Table.Buffer

این Query را در نظر بگیرید:

let
    Source = Sql.Database (
        "ServerName",
        "SalesDB"
    ),
    Sales = Source{[
        Schema = "dbo",
        Item = "FactSales"
    ]}[Data],
    BufferedSales = Table.Buffer ( Sales ),
    FilteredSales = Table.SelectRows (
        BufferedSales,
        each [OrderDate] >= #date ( 2025, 1, 1 )
    )
in
    FilteredSales

اینجا ابتدا Table Buffer می‌شود و سپس Filter اعمال می‌شود. اگر FactSales شامل ده‌ها میلیون ردیف باشد، Buffer کردن جدول کامل می‌تواند هزینه زیادی داشته باشد. در عین حال، Buffer کردن می‌تواند Folding مراحل بعدی را متوقف کند.

بنابراین ممکن است به جای اینکه SQL Server فقط رکوردهای موردنیاز را برگرداند، حجم بسیار بیشتری از داده ابتدا وارد Power Query شود.

Query Folding چیست؟

Query Folding فرآیندی است که در آن Power Query تلاش می‌کند عملیات قابل انتقال Query را به منبع داده منتقل کند.

برای مثال، اگر این Query را داشته باشیم:

let
    Source = Sql.Database (
        "ServerName",
        "SalesDB"
    ),
    Sales = Source{[
        Schema = "dbo",
        Item = "FactSales"
    ]}[Data],
    FilteredSales = Table.SelectRows (
        Sales,
        each [Amount] > 1000000
    )
in
    FilteredSales

ممکن است Power Query بتواند این منطق را به منبع داده منتقل کند.

در یک منبع رابطه‌ای مانند SQL Server، نتیجه می‌تواند از نظر مفهومی شبیه این باشد:

SELECT *
FROM dbo.FactSales
WHERE Amount > 1000000;

در این حالت، SQL Server بخشی از کار را انجام می‌دهد و Power Query مجبور نیست تمام داده خام را دریافت کند.

Microsoft توضیح می‌دهد که هدف Query Folding انتقال هرچه بیشتر عملیات قابل اجرا به منبع داده است.

Table.Buffer چه اثری بر Query Folding دارد؟

این بخش یکی از مهم‌ترین نکات مقاله است.

اگر Buffer را اینجا قرار دهیم:

Source
   ↓
Filter
   ↓
Table.Buffer
   ↓
Transform

عملیات قبل از Buffer ممکن است همچنان قابل Fold باشند.

اما عملیات بعد از Buffer دیگر نمی‌توانند مانند قبل روی منبع اصلی Fold شوند.

به زبان ساده:

قبل از Buffer
    ↓
امکان Folding

Table.Buffer
    ↓
مرز Materialization

بعد از Buffer
    ↓
پردازش روی داده Buffer شده

Microsoft نیز اعلام می‌کند که Buffering جلوی Downstream Folding را می‌گیرد. به همین دلیل، محل قرار دادن Table.Buffer یکی از مهم‌ترین تصمیم‌ها در طراحی Query است.

چرا Buffer کردن قبل از Filter معمولاً انتخاب خوبی نیست؟

فرض کنید جدول شما 50 میلیون ردیف دارد اما فقط 500 هزار ردیف موردنیاز است.

اگر بنویسیم:

Buffered = Table.Buffer ( Source ),
Filtered = Table.SelectRows (
    Buffered,
    each [OrderDate] >= #date ( 2025, 1, 1 )
)

ممکن است ابتدا حجم بسیار بزرگی از داده وارد حافظه شود.

اما اگر بنویسیم:

Filtered = Table.SelectRows (
    Source,
    each [OrderDate] >= #date ( 2025, 1, 1 )
),
Buffered = Table.Buffer ( Filtered )

در صورت Fold شدن Filter، می‌توانیم ابتدا حجم داده را در منبع کاهش دهیم و سپس نتیجه کوچک‌تر را Buffer کنیم.

این الگو معمولاً منطقی‌تر است:

Source
   ↓
Filter قابل Fold
   ↓
Select Columns قابل Fold
   ↓
Join یا Transform قابل Fold
   ↓
Table.Buffer
   ↓
Transformهای غیرقابل Fold

البته اینکه هر مرحله واقعاً Fold شود، به منبع داده و نوع Transformation بستگی دارد.

آیا Table.Buffer همیشه Performance را بهتر می‌کند؟

خیر، این شاید مهم‌ترین جمله کل مقاله باشد:

Table.Buffer یک Performance Booster عمومی نیست.

گاهی استفاده از آن باعث بهبود Performance می‌شود.

گاهی هیچ تفاوت محسوسی ایجاد نمی‌کند.

و گاهی Performance را به شکل محسوسی بدتر می‌کند.

دلیل آن ساده است.

برای Buffer کردن یک Table، داده باید خوانده و در حافظه نگهداری شود.

اگر Table بزرگ باشد، هزینه این کار می‌تواند قابل توجه باشد.

Microsoft نیز صراحتاً اشاره می‌کند که Buffering به دلیل هزینه خواندن داده و ذخیره آن در حافظه می‌تواند Query را کندتر کند.

بنابراین نباید چنین قاعده‌ای ساخت:

Query کند است
      ↓
Table.Buffer اضافه کن
      ↓
Query سریع می‌شود

این رویکرد اشتباه است.

رویکرد درست این است:

Query کند است
      ↓
علت کندی را پیدا کن
      ↓
Query Folding؟
      ↓
حجم داده؟
      ↓
Transformation؟
      ↓
محاسبه تکراری؟
      ↓
منبع ناپایدار؟
      ↓
آیا Buffer واقعاً مسئله را حل می‌کند؟
      ↓
تست با و بدون Buffer

Table.Buffer و جلوگیری از ارزیابی مجدد

یکی از سناریوهایی که می‌تواند استفاده از Buffer را توجیه کند، زمانی است که یک Table در چند محاسبه مصرف می‌شود و Materialize کردن نتیجه آن می‌تواند از ارزیابی مجدد جلوگیری کند.

فرض کنیم:

let
    Source = Csv.Document (
        File.Contents (
            "C:\Data\Sales.csv"
        ),
        [Delimiter = ",", Encoding = 65001]
    ),
    Sales = Table.PromoteHeaders (
        Source
    ),
    FilteredSales = Table.SelectRows (
        Sales,
        each [Amount] > 1000
    ),
    BufferedSales = Table.Buffer (
        FilteredSales
    ),
    Result = [
        RowCount = Table.RowCount (
            BufferedSales
        ),
        FirstRows = Table.FirstN (
            BufferedSales,
            100
        )
    ]
in
    Result

در اینجا BufferedSales در دو بخش استفاده شده است.

Buffer کردن می‌تواند کمک کند این نتیجه به‌عنوان یک Table Materialized در اختیار مصرف‌کننده‌های بعدی قرار گیرد.

اما نکته بسیار مهم این است که این رفتار را نباید به شکل یک تضمین عمومی برای «یک بار اجرا شدن همه چیز» تفسیر کرد.

Table.Buffer یک ابزار برای کنترل نقطه Materialization است، نه یک دستور Cache عمومی برای کل Power Query Engine.

چرا مثال RowCount و LastRow می‌تواند گمراه‌کننده باشد؟

گاهی برای توضیح Buffer این Query نوشته می‌شود:

let
    Source = SomeSource,
    RowCount = Table.RowCount ( Source ),
    LastRow = Table.Last ( Source )
in
    RowCount

اما این مثال مشکل دارد.

چرا؟

چون خروجی نهایی فقط RowCount است.

در نتیجه LastRow ممکن است اصلاً برای خروجی نهایی موردنیاز نباشد و ارزیابی نشود.

برای نشان دادن یک سناریوی واقعی باید هر دو مقدار در خروجی مصرف شوند:

let
    Source = SomeSource,
    RowCount = Table.RowCount (
        Source
    ),
    LastRow = Table.Last (
        Source
    ),
    Result = [
        RowCount = RowCount,
        LastRow = LastRow
    ]
in
    Result

و اگر هدف بررسی رفتار Buffer باشد:

let
    Source = SomeSource,
    BufferedSource = Table.Buffer (
        Source
    ),
    RowCount = Table.RowCount (
        BufferedSource
    ),
    LastRow = Table.Last (
        BufferedSource
    ),
    Result = [
        RowCount = RowCount,
        LastRow = LastRow
    ]
in
    Result

این مثال از نظر آموزشی دقیق‌تر است، چون هر دو مصرف واقعاً در خروجی نهایی حضور دارند.

Table.Buffer و تغییرات خارجی داده

یکی از کاربردهای مهم Buffer مربوط به منابعی است که در طول فرآیند ارزیابی ممکن است تغییر کنند.

فرض کنید داده از یک فایل CSV یا منبع دیگری خوانده می‌شود و یک فرآیند خارجی هم‌زمان در حال تغییر آن است.

در چنین شرایطی، اگر داده در زمان‌های مختلف از منبع دوباره ارزیابی شود، ممکن است بخش‌های مختلف Query با وضعیت یکسانی از داده مواجه نباشند.

Buffer کردن Table در یک نقطه مشخص می‌تواند داده را در طول آن ارزیابی از تغییرات خارجی جدا کند.

از این نظر، مفهوم مهم Table.Buffer این است:

منبع خارجی
     ↓
خواندن داده
     ↓
Table.Buffer
     ↓
Snapshot در حافظه
     ↓
ادامه Query

پس در چنین سناریویی، مزیت اصلی Buffer الزاماً Performance نیست.

مزیت اصلی می‌تواند ثبات داده در طول ارزیابی باشد.

آیا Table.Buffer یک Snapshot دائمی ایجاد می‌کند؟

خیر، این نکته را باید بسیار واضح بیان کرد.

Table.Buffer یک Snapshot دائمی روی منبع داده ایجاد نمی‌کند.

اگر Power BI بعداً Refresh دیگری انجام دهد، Query دوباره ارزیابی می‌شود و داده دوباره از منبع دریافت می‌شود.

بنابراین:

Refresh شماره 1
    ↓
Source
    ↓
Buffer
    ↓
Result

Refresh شماره 2
    ↓
Source دوباره ارزیابی می‌شود
    ↓
Buffer جدید
    ↓
Result جدید

Table.Buffer جایگزین Cache دائمی، Data Warehouse یا Snapshot Table در پایگاه داده نیست.

Table.Buffer و Sort

یکی از برداشت‌های اشتباه این است که:

Table.Buffer

باعث می‌شود ترتیب Table تضمین شود.

این برداشت دقیق نیست.

اگر نیاز دارید Table بر اساس یک ستون مرتب شود، باید Sort را به‌صورت صریح انجام دهید:

SortedTable = Table.Sort (
    Source,
    {
        { "TransactionDate", Order.Ascending },
        { "TransactionID", Order.Ascending }
    }
)

سپس در صورت وجود دلیل واقعی برای Materialization می‌توان نتیجه را Buffer کرد:

BufferedSortedTable = Table.Buffer (
    SortedTable
)

در این حالت، نقش‌ها متفاوت هستند:

Table.Sort
=
تعیین ترتیب

Table.Buffer
=
Materialize کردن نتیجه در یک نقطه مشخص

بنابراین بهتر است Table.Buffer را ابزار تضمین ترتیب معرفی نکنیم.

استفاده از Table.Buffer پس از Sort

فرض کنید می‌خواهیم یک Index بر اساس ترتیب مشخصی از تراکنش‌ها ایجاد کنیم:

let
    Source = Transactions,
    SortedTransactions = Table.Sort (
        Source,
        {
            { "TransactionDate", Order.Ascending },
            { "TransactionID", Order.Ascending }
        }
    ),
    BufferedTransactions = Table.Buffer (
        SortedTransactions
    ),
    IndexedTransactions = Table.AddIndexColumn (
        BufferedTransactions,
        "RowIndex",
        1,
        1
    )
in
    IndexedTransactions

در اینجا Buffer نقش «تثبیت نتیجه Materialized شده» را دارد.

اما باید توجه داشت که خود Table.Buffer جایگزین Sort نیست.

Tie Breaker در Sort چرا اهمیت دارد؟

اگر فقط این را بنویسیم:

Table.Sort (
    Source,
    {
        { "TransactionDate", Order.Ascending }
    }
)

و چند تراکنش تاریخ یکسان داشته باشند، اگر ترتیب بین آن‌ها برای محاسبه بعدی اهمیت داشته باشد، بهتر است ستون دیگری که ترتیب را مشخص می‌کند نیز اضافه شود:

Table.Sort (
    Source,
    {
        { "TransactionDate", Order.Ascending },
        { "TransactionID", Order.Ascending }
    }
)

این موضوع مخصوصاً زمانی اهمیت دارد که بعد از Sort از Index، عملیات ترتیبی یا محاسبات وابسته به موقعیت ردیف استفاده شود.

BufferMode چیست؟

در نسخه فعلی تابع Table.Buffer، علاوه بر Table می‌توان یک options نیز ارائه کرد:

Table.Buffer (
    table,
    optional options
)

یکی از گزینه‌های مهم آن BufferMode است. Microsoft دو حالت را برای آن معرفی می‌کند:

BufferMode.Eager
BufferMode.Delayed

این قابلیت در مستندات فعلی Power Query M وجود دارد.

BufferMode.Eager چیست؟

در حالت:

BufferMode.Eager

کل مقدار بلافاصله در حافظه Buffer می‌شود.

مثال:

let
    Source = SomeTable,
    BufferedTable = Table.Buffer (
        Source,
        [ BufferMode = BufferMode.Eager ]
    )
in
    BufferedTable

مفهوم ساده آن:

Table.Buffer
      ↓
Eager
      ↓
کل مقدار فوراً Buffer می‌شود
      ↓
ادامه Query

Microsoft توضیح می‌دهد که در حالت Eager، کل مقدار قبل از ادامه ارزیابی در حافظه Buffer می‌شود.

BufferMode.Delayed چیست؟

در حالت:

BufferMode.Delayed

نوع مقدار بلافاصله محاسبه می‌شود، اما محتوای آن تا زمانی که داده واقعاً موردنیاز نباشد Buffer نمی‌شود. هنگامی که داده موردنیاز قرار گرفت، مقدار به‌صورت کامل Buffer می‌شود.

مثال:

let
    Source = SomeTable,
    BufferedTable = Table.Buffer (
        Source,
        [ BufferMode = BufferMode.Delayed ]
    )
in
    BufferedTable

به‌صورت مفهومی:

Table.Buffer
      ↓
Delayed
      ↓
شناخت نوع مقدار
      ↓
داده هنوز Buffer نشده
      ↓
اولین نیاز واقعی به داده
      ↓
Buffer شدن مقدار

تفاوت Eager و Delayed در یک نگاه

حالت رفتار
BufferMode.Eager Buffer کردن کل مقدار بلافاصله انجام می‌شود
BufferMode.Delayed Buffer کردن محتوا تا زمان نیاز واقعی به داده به تعویق می‌افتد

انتخاب بین این دو حالت باید بر اساس رفتار واقعی Query و نیاز به Materialization انجام شود و نباید صرفاً با هدف «سریع‌تر شدن Query» یکی از آن‌ها انتخاب شود.

آیا BufferMode.Eager همیشه بهتر است؟

خیر.

Eager به این معنی نیست که Performance بهتر خواهد شد.

اگر Table بسیار بزرگ باشد، Buffer کردن فوری آن می‌تواند هزینه حافظه و زمان اولیه را افزایش دهد.

بنابراین استفاده از Eager باید زمانی انجام شود که رفتار موردنظر واقعاً به Buffer شدن فوری داده نیاز داشته باشد.

آیا BufferMode.Delayed یعنی Buffer اصلاً انجام نمی‌شود؟

خیر.

Delayed به معنی «هرگز Buffer نکن» نیست.

در این حالت، Buffer کردن محتوا تا زمانی که داده موردنیاز قرار بگیرد به تعویق می‌افتد.

پس:

Eager
=
Buffering فوری

Delayed
=
Buffering در زمان نیاز

Table.Buffer و مصرف حافظه

یکی از مهم‌ترین هزینه‌های Buffering، حافظه است.

فرض کنید Fact Table شما:

30,000,000 rows

دارد.

اگر کل Table را Buffer کنید:

BufferedFact = Table.Buffer (
    FactTable
)

باید انتظار داشته باشید که هزینه حافظه افزایش پیدا کند. این موضوع در Power BI Desktop یک مسئله است. در Power BI Service نیز Refresh به منابع زیرساختی مورد استفاده برای اجرای Refresh وابسته است.

در محیط سازمانی که از On-premises Data Gateway استفاده می‌شود، مصرف حافظه Query می‌تواند به یک عامل مهم در پایداری Refresh تبدیل شود.

بنابراین Buffer کردن جدول بزرگ بدون دلیل مشخص می‌تواند ریسک ایجاد کند.

الگوی پرریسک

let
    Source = Sql.Database (
        "ServerName",
        "SalesDB"
    ),
    FactSales = Source{[
        Schema = "dbo",
        Item = "FactSales"
    ]}[Data],
    BufferedFactSales = Table.Buffer (
        FactSales
    )
in
    BufferedFactSales

اگر FactSales ده‌ها میلیون ردیف داشته باشد، این کار ممکن است حجم زیادی از داده را وارد حافظه کند.

الگوی منطقی‌تر

let
    Source = Sql.Database (
        "ServerName",
        "SalesDB"
    ),
    FactSales = Source{[
        Schema = "dbo",
        Item = "FactSales"
    ]}[Data],
    FilteredSales = Table.SelectRows (
        FactSales,
        each [OrderDate] >= #date ( 2025, 1, 1 )
    ),
    SelectedColumns = Table.SelectColumns (
        FilteredSales,
        {
            "OrderID",
            "CustomerID",
            "OrderDate",
            "Amount"
        }
    ),
    BufferedSales = Table.Buffer (
        SelectedColumns
    )
in
    BufferedSales

در این نسخه ابتدا تلاش می‌کنیم حجم داده را کاهش دهیم و سپس Buffer کنیم. البته Fold شدن هر مرحله باید با توجه به منبع و Query واقعی بررسی شود.

Table.Buffer و Table.SelectColumns

یک نکته مهم در Queryهای حجیم این است که فقط تعداد ردیف‌ها مهم نیست. تعداد ستون‌ها نیز روی حجم داده اثر دارد.

فرض کنید Table شامل 80 ستون است ولی Query فقط به 10 ستون نیاز دارد.

در چنین شرایطی:

Table.SelectColumns (
    Source,
    {
        "OrderID",
        "CustomerID",
        "OrderDate",
        "Amount"
    }
)

قبل از Buffer کردن می‌تواند حجم داده‌ای را که قرار است در حافظه نگهداری شود کاهش دهد.

بنابراین یک الگوی مناسب می‌تواند این باشد:

Source
   ↓
Filter Rows
   ↓
Select Columns
   ↓
Foldable Transformations
   ↓
Table.Buffer
   ↓
Non-foldable Transformations

Table.Buffer و منابع SQL Server

در پروژه‌های سازمانی که Power BI به SQL Server متصل است، باید نسبت به استفاده از Table.Buffer محتاط‌تر بود.

SQL Server برای انجام عملیات‌هایی مانند:

  • Filter
  • Join
  • Aggregation
  • Projection
  • Sort در شرایط مناسب

توان پردازشی و بهینه‌ساز Query دارد.

اگر Query Folding فعال باشد، بخشی از این پردازش می‌تواند در SQL Server انجام شود.

اگر Buffer را خیلی زود وارد Query کنیم، ممکن است داده قبل از انجام این عملیات به Power Query منتقل شود.

در نتیجه ممکن است:

SQL Server
   ↓
داده زیاد
   ↓
Power Query
   ↓
Buffer
   ↓
Filter

جایگزین شود با:

SQL Server
   ↓
Filter
   ↓
داده کمتر
   ↓
Power Query
   ↓
Buffer

نسخه دوم در بسیاری از سناریوها منطقی‌تر است.

Table.Buffer و منابع File-Based

در منابع فایل‌محور مانند CSV و بعضی فایل‌های Excel، Query Folding معمولاً مانند SQL Server مطرح نیست. در این شرایط، کاربرد Buffer ممکن است بیشتر به مسئله Materialization، ثبات داده در طول ارزیابی یا جلوگیری از مصرف تکراری یک نتیجه مربوط شود.

با این حال، این موضوع به معنی آن نیست که Buffer کردن هر فایل بزرگی کار درستی است.

اگر فایل 5 گیگابایتی باشد، Buffer کردن آن در حافظه همچنان می‌تواند یک تصمیم بسیار پرهزینه باشد.

Table.Buffer در منابع Web

منابع Web نیز می‌توانند رفتار متفاوتی نسبت به منابع رابطه‌ای داشته باشند. اگر Query چند بار به یک API یا منبع Web وابسته باشد، Buffer کردن نتیجه در نقطه مناسب ممکن است در برخی سناریوها مفید باشد. اما اینجا نیز باید به حجم داده، تعداد درخواست‌ها، محدودیت API و ساختار Query توجه کرد.

Buffer نباید به‌عنوان جایگزین طراحی صحیح فراخوانی API در نظر گرفته شود.

Table.Buffer و داده‌ای که در طول ارزیابی تغییر می‌کند

یکی از سناریوهای مهم استفاده از Buffer، منابعی هستند که ممکن است بین دو ارزیابی وضعیت متفاوتی داشته باشند.

فرض کنید یک فایل CSV در حال به‌روزرسانی است.

اگر نتیجه آن در یک نقطه مشخص Buffer شود:

BufferedSource = Table.Buffer (
    Source
)

مراحل بعدی روی همان Table Buffer شده کار می‌کنند.

این رفتار می‌تواند باعث شود مراحل Downstream در طول همان ارزیابی با یک نسخه ثابت از داده کار کنند.

به همین دلیل، یکی از کاربردهای واقعی Table.Buffer می‌تواند ایزوله کردن داده از تغییرات خارجی در طول ارزیابی باشد. این همان مفهومی است که Microsoft نیز در تعریف رسمی تابع روی آن تأکید دارد.

Table.Buffer و محاسبات چندمرحله‌ای

فرض کنیم یک Table پس از چند Transformation آماده شده است:

let
    Source = SomeSource,
    Cleaned = CleanSource (
        Source
    ),
    Filtered = Table.SelectRows (
        Cleaned,
        each [Amount] > 1000
    ),
    Buffered = Table.Buffer (
        Filtered
    ),
    Result = [
        Count = Table.RowCount (
            Buffered
        ),
        MaxAmount = List.Max (
            Buffered[Amount]
        )
    ]
in
    Result

در اینجا یک Table Buffer شده داریم که چند مصرف‌کننده دارد.

این الگو در برخی سناریوها می‌تواند منطقی باشد.

اما باز هم باید توجه کرد که:

وجود چند Reference به یک مرحله به‌تنهایی دلیل کافی برای افزودن Table.Buffer نیست.

ابتدا باید Performance واقعی Query بررسی شود.

Table.Buffer و List.Buffer چه تفاوتی دارند؟

Table.Buffer و List.Buffer از نظر مفهوم کلی مشابه هستند، اما نوع داده متفاوتی را Buffer می‌کنند.

Table.Buffer برای Table است:

Table.Buffer (
    SomeTable
)

و List.Buffer برای List:

List.Buffer (
    SomeList
)

برای مثال:

let
    Source = { 1 .. 1000000 },
    BufferedList = List.Buffer (
        Source
    ),
    Doubled = List.Transform (
        BufferedList,
        each _ * 2
    )
in
    Doubled

اگر داده‌ای که چند بار مصرف می‌شود یک List ساده است، List.Buffer ابزار متناسب‌تری است.

اگر داده ساختار جدولی دارد، Table.Buffer انتخاب طبیعی‌تری است.

چه زمانی List.Buffer انتخاب بهتری است؟

فرض کنید یک List از شناسه‌ها داریم:

CustomerIDs =
{
    1001,
    1002,
    1003,
    1004
}

اگر این List چند بار در Query استفاده شود، می‌توان در صورت وجود دلیل واقعی برای Materialization آن را Buffer کرد:

BufferedCustomerIDs =
    List.Buffer (
        CustomerIDs
    )

این کار باعث نمی‌شود List به Table تبدیل شود.

همچنان یک List است، اما نتیجه آن Buffer شده است.

تفاوت Table.Buffer با Table.StopFolding

این دو تابع نباید با یکدیگر اشتباه گرفته شوند.

Table.Buffer داده را Buffer می‌کند.

Table.StopFolding برای جلوگیری از ادامه Query Folding استفاده می‌شود، بدون اینکه هدف آن Buffer کردن Table باشد.

این تفاوت بسیار مهم است.

اگر فقط می‌خواهید Folding را در یک نقطه متوقف کنید، اما واقعاً نیاز ندارید Table در حافظه Buffer شود، Table.StopFolding می‌تواند گزینه مناسب‌تری باشد.

Microsoft نیز دقیقاً به همین تفاوت اشاره می‌کند و پیشنهاد می‌دهد اگر داده نیازی به Buffer شدن ندارد و هدف فقط جلوگیری از Downstream Folding است، از Table.StopFolding استفاده شود.

مثال Table.StopFolding

let
    Source = Sql.Database (
        "ServerName",
        "SalesDB"
    ),
    Sales = Source{[
        Schema = "dbo",
        Item = "Sales"
    ]}[Data],
    StopFolding = Table.StopFolding (
        Sales
    ),
    Filtered = Table.SelectRows (
        StopFolding,
        each [Amount] > 1000
    )
in
    Filtered

در اینجا هدف ما Buffer کردن Table نیست.

هدف این است که Folding بعد از نقطه مشخص‌شده متوقف شود.

این دو مفهوم را نباید یکی دانست:

Table.Buffer
=
Buffer + جلوگیری از Downstream Folding

Table.StopFolding
=
جلوگیری از Folding بدون هدف Buffer کردن داده

چه زمانی Table.Buffer انتخاب بهتری است؟

اگر واقعاً به Materialization نیاز دارید، Table.Buffer می‌تواند انتخاب مناسبی باشد.

برای مثال:

  • نیاز به جداسازی داده از تغییرات خارجی در طول ارزیابی
  • نیاز به Materialize کردن نتیجه یک Transformation در نقطه مشخص
  • استفاده چندباره از یک Table محاسبه‌شده در سناریویی که Buffer کردن آن واقعاً مزیت ایجاد می‌کند.
  • سناریوهای خاصی که بررسی عملی نشان داده Buffer باعث کاهش هزینه ارزیابی می‌شود.

اما اگر فقط می‌خواهید Folding متوقف شود، ابتدا Table.StopFolding را بررسی کنید.

آیا Table.Buffer برای Cache کردن داده است؟

بهتر است از واژه Cache با احتیاط استفاده کنیم.

Table.Buffer یک Cache دائمی ایجاد نمی‌کند.

برای مثال اگر Query در Power BI Refresh شود:

Refresh 1
    ↓
Source
    ↓
Table.Buffer
    ↓
Result

در Refresh بعدی:

Refresh 2
    ↓
Source
    ↓
Table.Buffer
    ↓
Result

داده دوباره در فرآیند Refresh ارزیابی می‌شود.

بنابراین Table.Buffer را نباید با Cache دائمی، Storage Engine یا Data Warehouse اشتباه گرفت.

Table.Buffer و Refresh در Power BI

در Power BI، Queryهای Power Query در فرآیند Refresh اجرا می‌شوند.

اگر Table بزرگی Buffer شود، مصرف حافظه آن Query می‌تواند افزایش پیدا کند.

این مسئله در محیط‌های سازمانی اهمیت بیشتری دارد، زیرا ممکن است چند Query یا چند Refresh هم‌زمان روی یک زیرساخت اجرا شوند.

بنابراین استفاده از Buffer باید با توجه به:

  • حجم Table
  • تعداد ستون‌ها
  • تعداد ردیف‌ها
  • تعداد Queryهای هم‌زمان
  • منابع سیستم
  • Gateway
  • زمان Refresh
  • رفتار Query Folding

ارزیابی شود.

Table.Buffer و Gateway

فرض کنید Query روی سیستم توسعه‌دهنده در Power BI Desktop اجرا می‌شود و سیستم 32 گیگابایت RAM دارد.

ممکن است همین Query در محیط سازمانی از طریق Gateway روی سیستمی با منابع محدودتر اجرا شود.

اگر Query تعداد زیادی Table بزرگ را Buffer کند، احتمال فشار حافظه بیشتر می‌شود.

بنابراین تست محلی به‌تنهایی کافی نیست.

در پروژه‌های سازمانی، Performance باید در محیط واقعی Refresh نیز بررسی شود.

Table.Buffer و Query Plan

در Power Query، بررسی Query Plan می‌تواند برای درک مسیر اجرای Query کمک‌کننده باشد.

Microsoft قابلیت Query Plan را برای مشاهده بخش‌هایی از Query که به منبع ترجمه شده‌اند و Transformationهایی که توسط Power Query Engine انجام می‌شوند ارائه کرده است.

بنابراین اگر درباره تأثیر Buffer روی یک Query پیچیده مطمئن نیستید، فقط به ظاهر مراحل اکتفا نکنید.

Query را با و بدون Buffer بررسی کنید.

روش درست تست Table.Buffer

فرض کنید Query فعلی:

let
    Source = SomeSource,
    Filtered = Table.SelectRows (
        Source,
        each [Amount] > 1000
    )
in
    Filtered

نسخه دوم:

let
    Source = SomeSource,
    Filtered = Table.SelectRows (
        Source,
        each [Amount] > 1000
    ),
    Buffered = Table.Buffer (
        Filtered
    )
in
    Buffered

باید این دو نسخه را روی داده واقعی مقایسه کنید.

مواردی که باید بررسی شوند:

Refresh Time
Memory Consumption
Query Folding
Data Volume
CPU
Gateway Resource Usage

اگر نسخه Buffer شده سریع‌تر نیست، صرفاً به دلیل وجود Table.Buffer نباید آن را نگه داشت.

Table.Buffer و Sort در Queryهای حجیم

یکی از الگوهای رایج این است:

Sorted =
    Table.Sort (
        Source,
        {
            { "Date", Order.Ascending }
        }
    ),

Buffered =
    Table.Buffer (
        Sorted
    )

این الگو می‌تواند در سناریوهایی که نتیجه Sort باید در نقطه مشخصی Materialize شود مفید باشد.

اما اگر Sort قابل Fold شدن باشد و Buffer قبل از آن قرار بگیرد:

Buffered =
    Table.Buffer (
        Source
    ),

Sorted =
    Table.Sort (
        Buffered,
        {
            { "Date", Order.Ascending }
        }
    )

ممکن است Sort دیگر در منبع انجام نشود.

پس تفاوت محل Buffer بسیار مهم است.

Table.Buffer و Index

فرض کنید بعد از Sort می‌خواهیم Index ایجاد کنیم:

let
    Source = Transactions,

    SortedTransactions =
        Table.Sort (
            Source,
            {
                { "TransactionDate", Order.Ascending },
                { "TransactionID", Order.Ascending }
            }
        ),

    BufferedTransactions =
        Table.Buffer (
            SortedTransactions
        ),

    IndexedTransactions =
        Table.AddIndexColumn (
            BufferedTransactions,
            "RowIndex",
            1,
            1
        )
in
    IndexedTransactions

در این مثال:

Sort
=
تعیین ترتیب

Buffer
=
Materialization

Index
=
شماره‌گذاری بر اساس نتیجه موجود

هر تابع مسئول یک وظیفه مشخص است.

اشتباه رایج: استفاده از Buffer برای حل مشکل Query Folding

اگر Query کند است چون Filter به SQL Server Fold نمی‌شود، اولین واکنش نباید اضافه کردن Table.Buffer باشد.

در چنین شرایطی ابتدا باید علت عدم Folding مشخص شود.

برای مثال ممکن است یک Transformation خاص باعث شکستن Folding شده باشد.

قرار دادن Buffer بعد از آن، مشکل اصلی را حل نمی‌کند.

حتی ممکن است بافر کردن داده حجم انتقال و مصرف حافظه را بیشتر کند.

اشتباه رایج: Buffer کردن بلافاصله بعد از Source

این الگو:

Source = ...,
BufferedSource = Table.Buffer (
    Source
)

همیشه اشتباه نیست.

اما اگر Source یک Table بسیار بزرگ SQL Server باشد، باید قبل از آن بررسی شود که آیا Filter، Select Columns یا Transformationهای قابل Fold وجود دارند که بهتر است قبل از Buffer اجرا شوند.

قاعده بهتر این است:

هر جا می‌خواهید Buffer کنید، ابتدا از خودتان بپرسید آیا داده قبل از Buffer می‌تواند کوچک‌تر شود؟

اگر پاسخ مثبت است، آن کاهش داده را تا حد امکان قبل از Buffer انجام دهید.

اشتباه رایج: تصور اینکه Buffer همیشه از اجرای دوباره جلوگیری می‌کند

این جمله را نباید به‌صورت مطلق استفاده کرد:

Table.Buffer باعث می‌شود Query فقط یک بار اجرا شود.

این عبارت دقیق نیست.

Buffer نتیجه Table را در یک نقطه مشخص Materialize می‌کند، اما رفتار کلی Query Engine، Source Connector، Folding و ساختار Query همچنان اهمیت دارد.

پس بهتر است بگوییم:

Table.Buffer می‌تواند در سناریوهای خاص از ارزیابی مجدد داده Buffer شده جلوگیری کند، اما نباید آن را یک Cache عمومی یا تضمین یک‌بار اجرا شدن کل Query در نظر گرفت.

اشتباه رایج: استفاده از Table.Buffer برای همه Queryهای کند

اگر Query کند است، دلایل مختلفی ممکن است وجود داشته باشد:

حجم زیاد داده
عدم Query Folding
Transformationهای پرهزینه
Join نامناسب
تعداد ستون‌های زیاد
منبع داده کند
فایل بزرگ
فراخوانی زیاد API
مصرف بالای حافظه
محاسبات پیچیده

Table.Buffer فقط یکی از ابزارهای ممکن است.

در بسیاری از این موارد، Buffer حتی ممکن است وضعیت را بدتر کند.

اشتباه رایج: Buffer کردن قبل از اعمال Filter

این یکی از مهم‌ترین Anti-Patternها است:

Buffered =
    Table.Buffer (
        Source
    ),

Filtered =
    Table.SelectRows (
        Buffered,
        each [Status] = "Active"
    )

اگر Source میلیون‌ها ردیف داشته باشد ولی فقط چند درصد آن موردنیاز باشد، Buffer کردن کل Source تصمیم پرهزینه‌ای است.

الگوی مناسب‌تر:

Filtered =
    Table.SelectRows (
        Source,
        each [Status] = "Active"
    ),

Buffered =
    Table.Buffer (
        Filtered
    )

البته به شرط اینکه Filter در منبع قابل اجرا باشد و با توجه به نیاز واقعی Query، Buffer کردن FilteredTable توجیه داشته باشد.

اشتباه رایج: استفاده از Buffer برای حفظ ترتیب

این جمله را فراموش کنید:

Table.Buffer ترتیب Table را تضمین می‌کند.

نقش Buffer تعیین ترتیب نیست.

برای ترتیب باید Sort داشته باشید:

Sorted =
    Table.Sort (
        Source,
        {
            { "Date", Order.Ascending }
        }
    )

اگر Materialization نیز لازم بود:

Buffered =
    Table.Buffer (
        Sorted
    )

اشتباه رایج: تصور اینکه Table.Buffer حافظه کمی مصرف می‌کند.

Buffer کردن یک Table بزرگ می‌تواند هزینه حافظه قابل توجهی داشته باشد.

به همین دلیل، قبل از Buffer باید این سؤال‌ها پرسیده شوند:

چند ردیف داریم؟
چند ستون داریم؟
نوع ستون‌ها چیست؟
چقدر داده واقعاً لازم است؟
آیا Filter قبل از Buffer ممکن است؟
آیا Select Columns قبل از Buffer ممکن است؟
آیا Folding قبل از Buffer حفظ می‌شود؟

Best Practice برای Table.Buffer

در پروژه‌های سازمانی، Table.Buffer باید هدف مشخصی داشته باشد.

اگر هدف فقط این است که Query سریع‌تر شود، ابتدا باید مشخص شود مشکل واقعی چیست.

اگر هدف Materialization است، محل Buffer اهمیت دارد.

اگر هدف جلوگیری از تغییر داده در طول ارزیابی است، Buffer می‌تواند منطقی باشد.

اگر هدف فقط متوقف کردن Folding است، Table.StopFolding را نیز بررسی کنید.

اگر داده بزرگ است، قبل از Buffer تا حد امکان Rows و Columns غیرضروری را حذف کنید.

اگر منبع SQL Server است، ابتدا Query Folding را بررسی کنید.

اگر Query از Gateway اجرا می‌شود، مصرف حافظه را در محیط واقعی بررسی کنید.

اگر Buffer اضافه کردید، Query را قبل و بعد از آن مقایسه کنید.

الگوی پیشنهادی برای Queryهای سازمانی

یک الگوی عمومی مناسب می‌تواند چنین ساختاری داشته باشد:

Source
   ↓
Filterهای قابل Fold
   ↓
Select Columns قابل Fold
   ↓
Join و Transformهای قابل Fold
   ↓
کاهش حجم داده
   ↓
Table.Buffer در صورت وجود دلیل واقعی
   ↓
Transformهای غیرقابل Fold
   ↓
خروجی نهایی

این نمودار یک قانون مطلق نیست.

بسته به منبع و هدف Query ممکن است محل Buffer متفاوت باشد.

اما به‌عنوان یک چارچوب ذهنی، بسیار بهتر از این رویکرد است:

Source
   ↓
Table.Buffer
   ↓
همه چیز

چگونه بفهمیم Table.Buffer ارزش دارد؟

به جای حدس زدن، آزمایش کنید.

نسخه بدون Buffer را اجرا کنید.

زمان Refresh را ثبت کنید.

مصرف حافظه را بررسی کنید.

Folding را بررسی کنید.

سپس Buffer را اضافه کنید.

دوباره همان Query را با همان داده اجرا کنید.

نتایج را مقایسه کنید.

برای مثال:

معیار بدون Buffer با Buffer
Refresh Time
Memory
Data Read
CPU
Gateway Usage
Query Folding

این روش بسیار معتبرتر از این فرض است که چون Table.Buffer داده را در حافظه قرار می‌دهد، پس حتماً Query سریع‌تر می‌شود.

چه زمانی Table.Buffer را حذف کنیم؟

اگر Buffer هیچ مزیت قابل اندازه‌گیری ایجاد نمی‌کند، حذف آن می‌تواند تصمیم منطقی باشد.

اگر Buffer باعث افزایش شدید Memory شده است، حذف آن را بررسی کنید.

اگر Buffer Folding مهمی را متوقف کرده است، محل آن را بررسی کنید.

اگر Table بسیار بزرگ است و دلیل مشخصی برای Buffer وجود ندارد، حذف آن معمولاً باید یکی از گزینه‌های بررسی باشد.

اگر فقط هدف توقف Folding بوده است، Table.StopFolding را بررسی کنید.

Table.Buffer در یک مثال کامل

فرض کنید جدول فروش SQL Server داریم:

FactSales

OrderID
CustomerID
OrderDate
Amount
Status

هدف ما این است که:

  • فقط فروش‌های سال 2025 را بخوانیم
  • فقط ستون‌های موردنیاز را نگه داریم
  • سپس نتیجه را در نقطه مشخصی Buffer کنیم
  • و چند محاسبه روی نتیجه انجام دهیم

Query:

let
    Source =
        Sql.Database (
            "SQLSERVER01",
            "SalesDB"
        ),

    FactSales =
        Source{
            [
                Schema = "dbo",
                Item = "FactSales"
            ]
        }[Data],

    FilteredSales =
        Table.SelectRows (
            FactSales,
            each
                [OrderDate] >= #date ( 2025, 1, 1 )
                and
                [OrderDate] < #date ( 2026, 1, 1 )
        ),

    SelectedColumns =
        Table.SelectColumns (
            FilteredSales,
            {
                "OrderID",
                "CustomerID",
                "OrderDate",
                "Amount"
            }
        ),

    BufferedSales =
        Table.Buffer (
            SelectedColumns
        ),

    Result =
        [
            RowCount =
                Table.RowCount (
                    BufferedSales
                ),

            TotalSales =
                List.Sum (
                    BufferedSales[Amount]
                )
        ]
in
    Result

در این Query، Buffer بعد از کاهش حجم داده قرار گرفته است.

اگر Filter و Select Columns قابل Folding باشند، می‌توانند قبل از Buffer در SQL Server اجرا شوند.

سپس نتیجه کوچک‌تر Buffer می‌شود.

این الگو از نظر طراحی بسیار منطقی‌تر از Buffer کردن کل Fact Table در همان ابتدای Query است.

آیا این Query حتماً سریع‌تر است؟

خیر.

این نکته را عمداً دوباره تأکید می‌کنیم.

ممکن است نسخه بدون Buffer سریع‌تر باشد.

ممکن است نسخه Buffer شده سریع‌تر باشد.

ممکن است اختلاف ناچیز باشد.

تنها راه معتبر، تست Query روی داده واقعی و بررسی رفتار واقعی Refresh است.

Table.Buffer و Performance را چگونه تحلیل کنیم؟

اگر هدف Performance است، ابتدا این موارد را بررسی کنید:

1. آیا Query Folding فعال است؟
2. چه مقدار داده از منبع خوانده می‌شود؟
3. آیا Columns غیرضروری حذف شده‌اند؟
4. آیا Rows غیرضروری حذف شده‌اند؟
5. آیا Transformation پرهزینه‌ای وجود دارد؟
6. آیا Join مناسب طراحی شده است؟
7. آیا منبع داده Performance مناسبی دارد؟
8. آیا Buffer واقعاً مشکل را هدف گرفته است؟
9. مصرف Memory بعد از Buffer چقدر است؟
10. زمان Refresh قبل و بعد از Buffer چقدر است؟

اگر پاسخ این سؤالات مشخص نباشد، افزودن Table.Buffer بیشتر شبیه حدس زدن است تا بهینه‌سازی.

Table.Buffer در معماری ETL سازمانی

در پروژه‌های Enterprise، Power Query معمولاً بخشی از یک زنجیره بزرگ‌تر است:

Source Systems
      ↓
SQL Server / API / Files
      ↓
Power Query
      ↓
Data Model
      ↓
Power BI
      ↓
Reports

در چنین معماری‌ای، هر تصمیم در Power Query می‌تواند روی منابع پایین‌دست و بالادست اثر بگذارد.

اگر Buffer باعث شود داده بیشتری از SQL Server دریافت شود، فشار روی Network افزایش پیدا می‌کند.

اگر Buffer حجم زیادی از داده را در حافظه نگه دارد، فشار روی Refresh افزایش پیدا می‌کند.

اگر Buffer Folding را متوقف کند، ممکن است بخشی از پردازشی که قبلاً در SQL Server انجام می‌شد به Power Query منتقل شود.

پس Buffer کردن یک تصمیم محلی و بی‌اثر نیست.

Table.Buffer و SQL Server

در محیط‌هایی که منبع SQL Server است، معمولاً بهتر است عملیات قابل Fold را تا جای ممکن به SQL Server بسپاریم.

برای مثال:

SQL Server
   ↓
WHERE
   ↓
JOIN
   ↓
SELECT Columns
   ↓
نتیجه کوچک‌تر
   ↓
Power Query
   ↓
Buffer در صورت نیاز

این معماری معمولاً منطقی‌تر از:

SQL Server
   ↓
کل جدول
   ↓
Power Query
   ↓
Buffer
   ↓
WHERE

است.

این موضوع به‌خصوص برای Fact Tableهای بزرگ اهمیت دارد.

Table.Buffer و Data Warehouse

اگر سازمان Data Warehouse مناسبی دارد، نباید از Table.Buffer برای جبران طراحی ضعیف لایه Data Warehouse استفاده کرد.

اگر SQL Server می‌تواند داده را با:

  • Filtering
  • Aggregation
  • Join
  • Index
  • Partitioning
  • View
  • Materialized Strategy

به شکل مناسب آماده کند، بهتر است مشکل در همان لایه تا حد امکان حل شود.

Table.Buffer یک ابزار در لایه Power Query است، نه جایگزین معماری صحیح Data Warehouse.

Table.Buffer و Streaming

Power Query بسته به نوع Transformation و منبع ممکن است داده را به شکل Stream یا Buffer پردازش کند. Microsoft نیز در مستندات Query Evaluation اشاره می‌کند که بسته به Transformation و منبع داده، Power Query تعیین می‌کند داده چگونه دریافت و پردازش شود.

بنابراین نباید فرض کنیم که بدون Table.Buffer هیچ داده‌ای در حافظه قرار نمی‌گیرد.

Power Query خودش می‌تواند برای اجرای برخی عملیات نیازمند Buffering باشد.

تفاوت Table.Buffer این است که شما به‌صورت صریح یک نقطه Buffer در Query تعریف می‌کنید.

Table.Buffer و تفاوت Buffer داخلی با Buffer صریح

ممکن است موتور Power Query برای انجام یک Transformation خودش به Buffer نیاز داشته باشد.

این موضوع با نوشتن:

Table.Buffer ( Source )

متفاوت است.

در حالت اول، موتور بر اساس نیاز اجرای Query تصمیم می‌گیرد.

در حالت دوم، شما به‌صورت صریح Table را Buffer می‌کنید.

به همین دلیل، اضافه کردن Table.Buffer بدون دلیل می‌تواند فقط یک هزینه اضافه ایجاد کند.

Best Practice نهایی برای استفاده از Table.Buffer

در پروژه‌های واقعی Power BI و Power Query، این قواعد را به‌عنوان نقطه شروع در نظر بگیرید:

اول دلیل استفاده را مشخص کنید.

اگر نمی‌توانید توضیح دهید چرا Buffer لازم است، احتمالاً هنوز دلیل کافی برای اضافه کردن آن ندارید.

قبل از Buffer داده را کوچک کنید.

تا حد امکان Filter و Select Columns قابل Fold را پیش از Buffer انجام دهید.

Query Folding را حفظ کنید.

در منابعی مانند SQL Server، Buffer زودهنگام می‌تواند Folding مراحل بعدی را متوقف کند.

Buffer را با Performance Booster اشتباه نگیرید.

ممکن است سریع‌تر شود و ممکن است کندتر شود. تست واقعی تعیین‌کننده است.

برای فقط متوقف کردن Folding از Table.StopFolding استفاده کنید.

اگر به Materialization نیاز ندارید، Buffer کردن ممکن است هزینه غیرضروری داشته باشد. Microsoft نیز همین تفاوت را در مستندات Table.Buffer مطرح کرده است. حجم داده را جدی بگیرید.

Buffer روی میلیون‌ها یا ده‌ها میلیون ردیف می‌تواند Memory زیادی مصرف کند.

در Gateway تست کنید.

رفتار Power BI Desktop لزوماً نشان‌دهنده رفتار محیط Refresh سازمانی نیست.

برای ترتیب داده فقط به Buffer تکیه نکنید.

اگر ترتیب مهم است، Table.Sort را صریحاً تعریف کنید.

Tie Breaker را در Sort در نظر بگیرید.

اگر چند ردیف مقدار یکسان دارند و ترتیب بین آن‌ها اهمیت دارد، یک ستون مناسب برای تعیین ترتیب یکتا اضافه کنید.

از Buffer به‌عنوان Cache دائمی استفاده نکنید.

Table.Buffer فقط در طول ارزیابی Query کاربرد دارد و جایگزین Cache دائمی یا Storage Layer نیست.

قبل و بعد از Buffer تست کنید.

زمان Refresh، Memory، حجم داده و Folding را مقایسه کنید.

نتیجه‌گیری

Table.Buffer یکی از توابع مهم Power Query M است، اما در عین حال یکی از توابعی است که بیشترین سوءبرداشت درباره آن وجود دارد. این تابع را نباید صرفاً یک ابزار برای سریع‌تر کردن Query دانست.

Table.Buffer یک Table را در حافظه Buffer می‌کند و در طول ارزیابی آن را از تغییرات خارجی جدا می‌سازد. Buffering باعث Materialization داده در نقطه مشخصی از Query می‌شود و مراحل بعدی را از ادامه Query Folding به منبع اصلی بازمی‌دارد.

این رفتار در بعضی سناریوها بسیار مفید است.

برای مثال، زمانی که نتیجه یک Transformation چند بار مصرف می‌شود یا زمانی که لازم است داده در طول یک ارزیابی مشخص از تغییرات خارجی جدا شود، Buffer می‌تواند انتخاب مناسبی باشد.

اما همین ویژگی می‌تواند در Queryهای بزرگ مشکل‌ساز شود.

وقتی یک Table بزرگ Buffer می‌شود، داده باید در حافظه نگهداری شود. علاوه بر این، Buffer می‌تواند Folding مراحل بعدی را متوقف کند. بنابراین اگر جدول بزرگی از SQL Server دریافت می‌کنید و قبل از اعمال Filter آن را Buffer می‌کنید، ممکن است به جای بهبود Performance، حجم انتقال داده، مصرف Memory و زمان Refresh افزایش پیدا کند.

به همین دلیل، یکی از مهم‌ترین اصول استفاده از Table.Buffer این است که Buffer را در نقطه درست قرار دهید.

در بسیاری از سناریوها بهتر است ابتدا:

Filter
   ↓
Select Columns
   ↓
Transformهای قابل Fold
   ↓
کاهش حجم داده
   ↓
Table.Buffer
   ↓
Transformهای بعدی

انجام شود.

همچنین باید تفاوت Table.Buffer و Table.StopFolding را در نظر گرفت. اگر فقط می‌خواهید Folding را متوقف کنید و واقعاً به Materialization داده در حافظه نیاز ندارید، Table.StopFolding گزینه مناسب‌تری است.

در نسخه‌های فعلی Power Query M، Table.Buffer همچنین گزینه BufferMode را در اختیار قرار می‌دهد که می‌تواند BufferMode.Eager یا BufferMode.Delayed باشد. در حالت Eager، Buffer کردن مقدار به‌صورت فوری انجام می‌شود و در حالت Delayed، Buffer کردن محتوا تا زمان نیاز واقعی به داده به تعویق می‌افتد.

در نهایت، مهم‌ترین نکته این است که Table.Buffer نباید به‌صورت کورکورانه وارد Query شود.

اگر Query کند است، ابتدا باید علت کندی مشخص شود.

اگر مشکل Query Folding است، علت Folding را بررسی کنید.

اگر مشکل حجم داده است، Rows و Columns غیرضروری را حذف کنید.

اگر مشکل منبع داده است، منبع را بررسی کنید.

اگر مشکل محاسبه تکراری یک نتیجه مشخص است، Buffer را به‌صورت هدفمند آزمایش کنید.

اگر مشکل تغییر داده در طول ارزیابی است، Buffer می‌تواند گزینه مناسبی باشد.

و اگر فقط می‌خواهید Folding را در یک نقطه متوقف کنید، Table.StopFolding را بررسی کنید.

بنابراین بهترین تعریف عملی برای Table.Buffer این است:

Table.Buffer ابزاری برای Buffer و Materialize کردن یک Table در نقطه مشخصی از ارزیابی Power Query است، نه یک دکمه جادویی برای افزایش Performance.

در پروژه‌های سازمانی، ارزش واقعی این تابع زمانی مشخص می‌شود که با شناخت درست از Lazy Evaluation، Query Folding، مصرف حافظه و معماری منبع داده مورد استفاده قرار گیرد.

سوالات متداول (FAQ)

1. Table.Buffer در Power Query چیست؟

Table.Buffer یک Table را در حافظه Buffer می‌کند و آن را در طول ارزیابی از تغییرات خارجی جدا می‌سازد. این تابع همچنین باعث می‌شود عملیات بعد از آن دیگر نتوانند مانند قبل روی منبع اصلی Fold شوند.

2. آیا Table.Buffer همیشه باعث افزایش Performance می‌شود؟

خیر، Table.Buffer ممکن است Performance را بهتر، بدون تغییر یا حتی بدتر کند. Buffer کردن نیازمند خواندن داده و نگهداری آن در حافظه است و همچنین می‌تواند Query Folding مراحل بعدی را متوقف کند.

3. آیا Table.Buffer باعث می‌شود Power Query منبع را فقط یک بار بخواند؟

نباید چنین تضمینی برای آن در نظر گرفت. هدف اصلی Table.Buffer ایجاد یک Table Buffer شده در نقطه مشخصی از ارزیابی است. رفتار کلی Query همچنان به Lazy Evaluation، منبع داده، Folding و ساختار Query وابسته است.

4. آیا Table.Buffer همان Cache است؟

خیر، Table.Buffer یک Cache دائمی ایجاد نمی‌کند. داده در جریان ارزیابی Query Buffer می‌شود و در Refresh بعدی Query دوباره ارزیابی خواهد شد.

5. آیا Table.Buffer Query Folding را متوقف می‌کند؟

Table.Buffer باعث جلوگیری از Downstream Folding می‌شود. به همین دلیل بهتر است Transformationهایی که امکان Fold شدن دارند، در صورت امکان پیش از Buffer انجام شوند.

6. آیا Table.Buffer ترتیب ردیف‌ها را تضمین می‌کند؟

خیر، Table.Buffer ابزار تعیین ترتیب نیست. اگر ترتیب مشخصی لازم است، باید از Table.Sort استفاده شود. Buffer می‌تواند پس از Sort برای Materialize کردن نتیجه استفاده شود، اما خودش جایگزین Sort نیست.

7. تفاوت Table.Buffer و Table.StopFolding چیست؟

Table.Buffer علاوه بر جلوگیری از ادامه Folding، Table را Buffer می‌کند. Table.StopFolding برای متوقف کردن Folding استفاده می‌شود، بدون اینکه هدف آن Buffer کردن داده در حافظه باشد. اگر فقط می‌خواهید Folding متوقف شود، Table.StopFolding می‌تواند انتخاب مناسب‌تری باشد.

8. تفاوت Table.Buffer و List.Buffer چیست؟

Table.Buffer برای Table و List.Buffer برای List استفاده می‌شود. اگر داده موردنظر ساختار جدولی دارد، Table.Buffer و اگر یک مجموعه مقادیر به شکل List دارید، List.Buffer تابع متناسب با آن نوع داده است.

9. BufferMode.Eager چیست؟

BufferMode.Eager باعث می‌شود مقدار به‌صورت فوری در حافظه Buffer شود. این حالت زمانی کاربرد دارد که Buffer شدن فوری داده موردنیاز باشد.

10. BufferMode.Delayed چیست؟

در BufferMode.Delayed، نوع مقدار محاسبه می‌شود اما محتوای آن تا زمانی که داده موردنیاز نباشد Buffer نمی‌شود. پس از نیاز واقعی، مقدار به‌صورت کامل Buffer خواهد شد.

11. آیا Table.Buffer روی جدول‌های بزرگ مناسب است؟

به‌صورت عمومی نمی‌توان چنین توصیه‌ای کرد. Buffer کردن Table بزرگ می‌تواند مصرف Memory را افزایش دهد و در عین حال Folding مراحل بعدی را متوقف کند. اگر جدول بزرگ است، ابتدا باید امکان کاهش Rows و Columns و حفظ Folding بررسی شود.

12. آیا بهتر است Table.Buffer را بلافاصله بعد از Source قرار دهیم؟

معمولاً نباید این کار را به‌عنوان یک قانون عمومی انجام داد. اگر Filter یا Select Columns قابل Fold وجود دارد، بهتر است ابتدا آن‌ها بررسی و در صورت امکان اجرا شوند و سپس در صورت وجود دلیل واقعی، نتیجه کوچک‌تر Buffer شود.

13. آیا Table.Buffer برای SQL Server مناسب است؟

می‌تواند مناسب باشد، اما فقط در سناریوی مشخص. در اتصال Power Query به SQL Server باید ابتدا Query Folding و حجم داده بررسی شود. Buffer کردن زودهنگام یک جدول بزرگ می‌تواند باعث انتقال داده بیشتر به Power Query و افزایش مصرف حافظه شود.

14. چگونه بفهمیم Table.Buffer برای Query ما مفید است؟

Query را با و بدون Buffer روی داده واقعی اجرا کنید و زمان Refresh، مصرف حافظه، حجم داده منتقل‌شده و وضعیت Query Folding را مقایسه کنید. تصمیم نهایی باید بر اساس نتیجه واقعی باشد، نه صرفاً بر اساس وجود Table.Buffer در Query.

15. آیا Table.Buffer می‌تواند از تغییر داده منبع در طول ارزیابی جلوگیری کند؟

Buffer کردن Table می‌تواند آن را در طول ارزیابی از تغییرات خارجی جدا کند. این یکی از کاربردهای واقعی Buffering است، به‌خصوص زمانی که منبع ممکن است در طول فرآیند ارزیابی تغییر کند.

مشاوره تخصصی Power Query و بهینه‌سازی Power BI با لاندا

طراحی صحیح Query در Power BI فقط به نوشتن چند Transformation در Power Query Editor محدود نمی‌شود. شناخت Lazy Evaluation، Query Folding، Materialization، Buffering و رفتار منبع داده می‌تواند تأثیر مستقیمی بر زمان Refresh، مصرف منابع و پایداری راهکارهای تحلیلی سازمان داشته باشد.

اگر Queryهای Power Query شما در زمان Refresh کند هستند، Query Folding از بین می‌رود، مصرف حافظه افزایش پیدا می‌کند یا در انتخاب بین روش‌هایی مانند Table.Buffer و Table.StopFolding ابهام دارید، تیم توسعه فناوری اطلاعات لاندا می‌تواند در تحلیل Query و طراحی راهکار مناسب SQL Server و Power BI در کنار سازمان شما باشد.

از بررسی ساختار Power Query و Query Folding تا تحلیل Performance، بهینه‌سازی فرآیند ETL، طراحی مدل داده و بهبود فرآیند Refresh، هدف لاندا ایجاد راهکارهایی است که در محیط واقعی سازمان پایدار، قابل توسعه و قابل اندازه‌گیری باشند.

برای بررسی نیازهای پروژه و دریافت مشاوره تخصصی Power BI و Power Query، با  لاندا تماس ✆ بگیرید.

روزبه امیرعصامی

Power Query M formula language

Power BIآینده هوش تجاریتحلیل و مصورسازی داده‌هاداشبورد BIهوش تجاریهوش تجاری (BI)هوش تجاری Power BI

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

No comment

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *