incremental refresh from an on-premises SQL Server into Power BI takes longer

[Alaahady]

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 been monitoring performance for a couple of days, and I’ve noticed that the incremental refresh process is taking longer than the full copy—even though I’ve confirmed that query folding is enabled, I’m using navigational queries, and the queries include RangeStart and RangeEnd parameters. The data is filtered for the last two years based on the Survey_Date column, and I’ve configured incremental refresh to detect data changes using the rec_modify_date column. Do you know why this might be happening?

[Zanqueta]

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

1. 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.
2. 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

1. Index Key Columns
   • Ensure Survey_Date and rec_modify_date have proper indexes.
   • This dramatically reduces scan time for filtering and change detection.
2. 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.
3. 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.
4. 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.

1. Partition Management
   • Incremental refresh creates and processes partitions individually.
   • Each partition requires its own query execution and processing in the semantic model.
2. 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:

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

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 🌀.

[Alaahady]

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

[Zanqueta]

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.

[Alaahady]

Thank you @Zanqueta for promt respond

the query folder continue to the end

Incremental refresh setting is correct