Forum Discussion
Solved
incremental refresh from an on-premises SQL Server into Power BI takes longer
I have a quick question. I’m pulling data from an on-premises SQL Server into Power BI and have created two semantic models: one with a full data copy and another using incremental refresh. I’ve bee...
Hi Alaahady ,
Thank you for sharing the scheduled refresh details.
This behavior looks expected. The initial longer refresh was due to incremental refresh creating and evaluating all partitions. The subsequent scheduled refreshes are completing consistently, which confirms that only the incremental partitions are now being processed.
At this point, incremental refresh is working as designed. Please let us know if you notice any increase in refresh duration going forward.
Thank you.
Thank you Alaahady.
The analysis you shared is very useful because it confirms that the incremental refresh logic is correct (use of RangeStart and RangeEnd with query folding enabled) and that indexes exist on the relevant columns. However, there are additional factors that can explain why incremental refresh is still slower than a full refresh:
The analysis you shared is very useful because it confirms that the incremental refresh logic is correct (use of RangeStart and RangeEnd with query folding enabled) and that indexes exist on the relevant columns. However, there are additional factors that can explain why incremental refresh is still slower than a full refresh:
Additional Comments
- Number of Partitions Created
Incremental refresh creates multiple partitions (historical and incremental), and each partition generates a separate query to SQL Server.
Even with query folding, this means multiple executions rather than a single query as in a full refresh.
Recommendation: Adjust the partition granularity (monthly or quarterly) to reduce the number of queries. - Change Detection
When using rec_mod_dtz for change detection, Power BI issues an additional query to check modified rows.
If this column is not efficiently indexed or the table is very large, this check can be expensive.
Recommendation: Confirm that the index on rec_mod_dtz is effective and consider disabling change detection if it is not critical. - Indexes and Statistics
Although indexes exist on response_dtm_assoc_lcl and rec_mod_dtz, verify that:- Statistics are up to date.
- Indexes are selective; otherwise, SQL may perform scans.
For queries using ranges (BETWEEN RangeStart AND RangeEnd), date column indexes are essential, but fragmentation should also be checked.
- Query Folding vs Transformations
In incremental refresh, any additional transformation in Power Query that is not foldable can break optimisation.
In your code, only Table.TransformColumnTypes and Table.SelectRows are present, which are foldable, but confirm in the editor that the folding indicator is active up to the final step. - First Execution vs Subsequent Refreshes
The first execution of incremental refresh can be slower because it creates all partitions.
Subsequent executions should be faster as only incremental partitions are updated.
