Slow Update Query on Dimension table with only 10 rows

[ntimmerman]

Hi Community,

Can anyone explain to me why this very simple update query is taking 30s on average?

I only have 10 rows in that table so I don't understand why this query takes so extremely long:

UPDATE DimLocation SET [Last Updated] = GETUTCDATE(), [Last Update (Local Time)] = DateADD(hour, [Time Zone Offset],GETUTCDATE())

Any help would be greatly appreciated

Nico

[v-dineshya]

Hi @ntimmerman ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Microsoft Fabric Data Warehouse can be slow due to the underlying distributed architecture and transactional mechanics.

Please refer below possible chances.

1. Distributed Query Processing Overhead:

Even for a small table with 10 rows, Fabric treats it as part of a distributed system. Data movement between control node and compute nodes, and the whole distributed transaction engine kicks in even if unnecessary.

2. Columnstore vs. Rowstore:

If DimLocation is stored as a columnstore table (default for Fabric DW), then updates are internally converted to a delete + insert, which is very inefficient for small updates. Updates on columnstore tables are not optimized for row-level changes. This can lead to high latency, even if you're updating just 10 rows.

3. Transaction Logging and Versioning:

Updates trigger versioning and logging operations across distributed partitions. Fabric tries to maintain ACID properties, and that involves a fair amount of coordination overhead.

4. Statistics/Indexing Not Relevant:

Even if you have indexes or updated statistics, they don't significantly help UPDATE performance on columnstore tables.

Please follow below steps to fix the issue:

Option 1: Convert to HEAP or CLUSTERED INDEX (Rowstore).

If the table has few rows and gets frequent updates. Then point your ETL/report to the new table.

Option 2: Use CTAS Instead of UPDATE

Use a Create Table As Select (CTAS) or MERGE instead of update:

Option 3: Use MERGE if appropriate

Sometimes, MERGE can be faster depending on internal optimization, though it's still columnstore-unfriendly:

Note: Avoid UPDATE statements on columnstore tables unless absolutely necessary. For frequently updated small dimension tables, prefer rowstore (heap or clustered index) and replicated distribution. Use CTAS + swap approach for better performance.

Please refer Microsoft official articles and community threads.

Troubleshoot the Warehouse - Microsoft Fabric | Microsoft Learn

Performance Guidelines - Microsoft Fabric | Microsoft Learn

Monitor Your Running and Completed Queries Using Query Activity - Microsoft Fabric | Microsoft Learn

Solved: Re: Performance issue with running long queries on... - Microsoft Fabric Community

Solved: Fabric: Takes a relatively long time to insert a r... - Microsoft Fabric Community

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[ntimmerman · Accepted solution]

Hi @v-dineshya ,

Thank you for your prompt response and all the background information.

In the end, I conducted further experimentation, and it appears that the GETUTCDATE() function call in the update statement significantly slows it down.

So, I've now introduced a variable to store the current UTC date, and then I'm updating all records based on that variable instead. This makes the query a lot faster