Forum Discussion
incremental refresh from an on-premises SQL Server into Power BI takes longer
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.
9 Replies
Hi Alaahady
It can seem counterintuitive when incremental refresh takes longer than a full refresh, but there are several technical reasons why this happens, even when query folding and parameters are correctly configured.✅ Why incremental refresh can be slower
- Multiple Queries Instead of One
- A full refresh runs a single query to pull all data.
- Incremental refresh runs multiple queries:
- One for the historical partition (usually unchanged).
- One or more for incremental partitions (recent data).
- One for the detect data changes logic (based on rec_modify_date).
- This means more round trips to SQL Server, which increases total time.
- Change Detection Overhead
- When you enable “Detect data changes”, Power BI issues an extra query to check which rows have changed.
- If rec_modify_date is not indexed, this scan can be expensive.
How to Improve Performance
- Index Key Columns
- Ensure Survey_Date and rec_modify_date have proper indexes.
- This dramatically reduces scan time for filtering and change detection.
- Reduce Number of Partitions
- If you configured incremental refresh with very small partitions (e.g., daily), consider using monthly or quarterly partitions.
- Fewer partitions = fewer queries.
- Disable Change Detection if Not Critical
- If your data source guarantees immutability for historical data, you can disable “Detect data changes” to avoid extra queries.
- Use Native Queries
- If possible, create a parameterised SQL view in SQL Server that handles the filtering logic (RangeStart, RangeEnd) efficiently.
✅ Best Practice
- For large datasets, incremental refresh is still better long-term because only recent partitions are refreshed daily.
- The initial refresh or refresh after structural changes will always take longer because all partitions are processed.
- Partition Management
- Incremental refresh creates and processes partitions individually.
- Each partition requires its own query execution and processing in the semantic model.
- Query Folding Does Not Eliminate Multiple Executions
- Folding ensures queries are pushed to SQL Server, but does not reduce the number of queries executed.
Official Reference:If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
Thank you Zanqueta for your promt response
Here is the incremental query
let
Source = Sql.Database("omarcvmissql13", "PBI_Gateway"),
dbo_fact_survey_detail = Source{[Schema="dbo",Item="fact_survey_detail"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_fact_survey_detail,{{"rec_mod_dtz", type datetime}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [response_dtm_assoc_lcl] >= RangeStart and [response_dtm_assoc_lcl] <= RangeEnd)
in
#"Filtered Rows"and this is the full copy , I've made them identical
let
Source = Sql.Database("omarcvmissql13", "PBI_Gateway"),
dbo_fact_survey_detail = Source{[Schema="dbo",Item="fact_survey_detail"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_fact_survey_detail,{{"rec_mod_dtz", type datetime}})
in
#"Changed Type"and here is the index for the survey_column and modify column
IndexName IndexType ColumnName IsPrimaryKey idx_fact_survey_detail_rec_mod_dtz NONCLUSTERED rec_mod_dtz 0 idx_fact_survey_detail_response_dtm_assoc_lcl NONCLUSTERED response_dtm_assoc_lcl 0 PK_fact_survey_detail_new CLUSTERED id 1
- 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: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.
Hi Alaahady ,
Thank you for sharing the additional screenshots, and I appreciate Zanqueta for earlier explanation .
Since you’ve confirmed that query folding continues to the final step and that your incremental refresh policy is correctly configured, the setup itself looks correct. In cases like this, longer incremental refresh times are typically driven by engine behavior rather than configuration issues.
Incremental refresh must process multiple partitions and run an additional query for change detection, which often results in longer total execution time compared to a single full refresh query even when folding and indexing are in place. Factors such as partition granularity, change-detection overhead, and SQL Server index/statistics efficiency can further influence performance.
If possible, please share the duration of a subsequent refresh cycle (not the initial run). This will help us confirm whether the refresh stabilizes once only the incremental partitions are being processed. Let us know, and we’ll be happy to assist further.
Thank you.
Hi Anonymous
Thank you for your help, here is the subsequestint scheduled refresh
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.
Hi Alaahady ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
