Data Refresh Time Estimate for Incremental Refresh without Query Folding

[Pavan_123456789]

I wanted to share some insights, assuming we move forward with the incremental refresh for the current month:

Without incremental refresh, the entire dataset (2 crore records) can take up to 50 minutes to refresh.
With query folding and incremental refresh (focusing on 20 lakh records for the current month), the refresh time is significantly reduced to 5–8 minutes.

Given that we are considering implementing incremental refresh without query folding for the current month, could you please let me know approximately how much time the refresh will take under these conditions?

[lbendlin · Accepted solution]

Correct - query folding is not an absolute requirement if you only want to make sure your partitions stay under the 10GB limit. It is required to avoid running the query over the entire dataset and then filtering for the partitions. 

If your scenario does not allow query folding then the process will do exactly that - load all data and then throw away all the rows that don't match the partition boundaries. 

If query folding is allowed then the query itself is already limited to the partition boundaries and will thus (likely) be faster.

[Omid_Motamedise]

What was the time-consuming step?

[lbendlin]

50 minutes

[Pavan_123456789]

@lbendlin why it takes 50 min i am applying incremental refresh right ? I am just asking i dont have idea .

https://community.fabric.microsoft.com/t5/Power-Query/Incremental-Refresh-with-PostgreSQL-Is-query-f...  in this post you said 

Query folding is not an absolute requirement for incremental refresh.

[lbendlin · Accepted solution]

Correct - query folding is not an absolute requirement if you only want to make sure your partitions stay under the 10GB limit. It is required to avoid running the query over the entire dataset and then filtering for the partitions. 

If your scenario does not allow query folding then the process will do exactly that - load all data and then throw away all the rows that don't match the partition boundaries. 

If query folding is allowed then the query itself is already limited to the partition boundaries and will thus (likely) be faster.

[Pavan_123456789]

Thank you for the clarification. Since we are using a stored procedure, query folding is not possible in our scenario. Therefore, as you mentioned, the process will load the entire dataset and then filter it to match the partition boundaries, which will likely result in longer refresh times.

If you have any further suggestions on how to optimize this process or if there are alternative approaches we can consider, I would appreciate your insights.

[lbendlin · Accepted solution]

Since we are using a stored procedure, query folding is not possible in our scenario.

Not so fast.  Use Value.NativeQuery and set the EnableFolding attribute.