بهبود پرفرمانس محاسبات کوئری در پاور بی آی با کمک ()List.buffer

تابع ()List.buffer :

بافر چیست ؟

بافر داده منطقه ای از حافظه فیزیکی است که برای ذخیره موقت داده ها در حالی که از مکانی به مکان دیگر منتقل می شود ، استفاده می شود. به طور معمول ، داده ها در یک بافر ذخیره می شوند با این حال ، هنگام انتقال داده ها بین فرآیندهای درون رایانه ، ممکن است از یک بافر استفاده شود.  بافرها را می توان در یک مکان حافظه ثابت در سخت افزار – یا با استفاده از یک بافر داده مجازی در نرم افزار ، با اشاره به یک مکان در حافظه فیزیکی ، پیاده سازی کرد. در همه موارد ، داده های ذخیره شده در بافر داده روی یک محیط ذخیره سازی فیزیکی ذخیره می شوند. اکثر بافرها در نرم افزار اجرا می شوند که معمولاً از RAM سریعتر برای ذخیره داده های موقت استفاده می کنند ، به دلیل زمان دسترسی بسیار سریعتر در مقایسه با هارد دیسک ها. بافرها معمولاً زمانی استفاده می شوند که بین سرعت دریافت داده ها و سرعت پردازش آنها تفاوت وجود داشته باشد .

یک لیست در حافظه ذخیره می کند و آن را از تغییرات خارجی در طول محاسبات جدا می کند.

به این کوئری توجه کنید :

این اولین ۲۰۰۰ ردیف را از جدول FactInternetSales در پایگاه داده Adventure Works DW دریافت می کند ، اکثر ستون ها را حذف می کند و یک ستون سفارشی اضافه می کند که رتبه ردیف فعلی را بر اساس میزان فروش نشان می دهد.

let
    //Connect to SQL Server
    Source = Sql.Database("localhost", "adventure works dw"),
    //Get first 2000 rows from FactInternetSales
    dbo_FactInternetSales = Table.FirstN(
          Source{[Schema="dbo",Item="FactInternetSales"]}[Data], 
          ۲۰۰۰),
    //Remove unwanted columns
    RemoveColumns = Table.SelectColumns(
          dbo_FactInternetSales,
          {"SalesOrderLineNumber", "SalesOrderNumber","SalesAmount"}),
    //Get sorted list of values from SalesAmount column
    RankValues = List.Sort(RemoveColumns[SalesAmount], Order.Descending),
    //Calculate ranks
    AddRankColumn = Table.AddColumn(RemoveColumns , "Rank", 
          each List.PositionOf(RankValues,[SalesAmount])+1)
in
    AddRankColumn

در لپ تاپ من حدود ۳۵ ثانیه طول می کشد تا این پرس و جو را اجرا کنم – به نظر من ، با توجه به میزان داده های موجود در این جدول ، بسیار کند است.

با این حال ، با استفاده از تابع  () List.Buffer  در مرحله RankValues مانند زیر:

let
    //Connect to SQL Server
    Source = Sql.Database("localhost", "adventure works dw"),
    //Get first 2000 rows from FactInternetSales
    dbo_FactInternetSales = Table.FirstN(
          Source{[Schema="dbo",Item="FactInternetSales"]}[Data], 
          ۲۰۰۰),
    //Remove unwanted columns
    RemoveColumns = Table.SelectColumns(
          dbo_FactInternetSales,
          {"SalesOrderLineNumber", "SalesOrderNumber","SalesAmount"}),
    //Get sorted list of values from SalesAmount column
    //And buffer them!
    RankValues = List.Buffer(List.Sort(RemoveColumns[SalesAmount], Order.Descending)),
    //Calculate ranks
    AddRankColumn = Table.AddColumn(RemoveColumns , "Rank", 
          each List.PositionOf(RankValues,[SalesAmount])+1)
in
    AddRankColumn

کوئری را فقط در ۲ ثانیه اجرا می کند. تابع  () List.Buffer  لیست مرتب شده از مقادیر مورد استفاده برای محاسبه رتبه در حافظه را ذخیره می کند ، به این معنی که فقط یکبار محاسبه می شود. در کوئری اصلی به نظر می رسد که این مرحله و مراحل قبل از آن چندین بار محاسبه می شوند.

دلیل این امر این است که M هم کاربردی و هم تنبل است ، بنابراین مگر اینکه خروجی List را بافر کنیم. در واقع ما فقط یک کوئری ایجاد می کنیم که باید بارها و بارها مورد محاسبه قرار گیرد. اگر با آن آشنا هستید ، این عملکرد مشابه شمارش توابع در LINQ است.

تابع Table. Buffer () و Binary.Buffer () نیز وجود دارد و کارهای مشابه انجام می دهد.

چند نکته دیگر که قابل ذکر است:

این لزوماً روش بهینه برای محاسبه رتبه در Power Query نیست – این فقط یک مثال از نحوه استفاده از List.Buffer () است.
در اولین کوئری بالا ، query folding انجام نمی شود. اگر چنین بود احتمالاً عملکرد آن بهتر بود. از آنجا که استفاده از List.Buffer () به صراحت مانع از query folding می شود ، می تواند عملکرد را بدتر از آن کند زیرا در بسیاری از موارد بدین دلیل است.
۱۰۰٪ مطمئن هستم که با بارگذاری جدول در مدل داده Excel/Power Pivot و نوشتن محاسبه در DAX ، عملکرد بسیار بهتری برای محاسبه رتبه خواهید داشت. شما فقط باید محاسباتی مانند این را در Power Query انجام دهید ، در صورتی که برای سایر تغییرات در پرس و جو شما مورد نیاز باشد.