Fabric: Takes a relatively long time to insert a record into a table.

[ManishBundhun]

Hello,

It seems that update a record in a table takes much longer in Fabric. And we do several of such updates in our datawarehousing procedures. Does someone know why?

Running the following query (which is part of a bigger procedure) in Fabric takes 0.4 seconds. And 0.4 seconds is a lot more time for such a simple query. We are using an F8 capacity

update MDA_WH.mdahub.process
set ind_instance_running = 'Y'
,utc_record_last_updated = getutcdate()
 where 1=1
 and cod_process = @cod_process

This data was derived from the 'queryinsights.exec_requests_history' table and is shown below, next to a similar query that too 0.34 seconds to execute.

But a similar query takes 0.016 seconds in AzureSQL (20v Cores in AzureSQL db). So it takes more than 20 times in Fabric!

Thanking you in advance.

Gr,
Manish Bundhun

[ntimmerman]

See: Solved: Re: Slow Update Query on Dimension table with only... - Microsoft Fabric Community

Calling GETUTCDATE() in an update statement doesn't seem to perform well. If you store the current date in a variable first, it will perform much better.

[Anonymous]

Hi @ManishBundhun ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you

[suparnababu8]

Hi @ManishBundhun 

It dependes on several factors.

• Fabric environment warehouse might be allocating the resources differently than Azure SQL and also it's depends on the license you are using.
• Fabirc is just more than 1year tool that we can compare with Auzre since it's 20years of development efforts behind this. In laymon terms we can compare the 5 years old kid performance with 10 years  old kid.

Now days Micorosft people are imprving the performance of all services in Fabric environment. 

if you need more info pls visit below links. It might helps you.

Troubleshoot the Warehouse - Microsoft Fabric | Microsoft Learn

Warehouse performance guidelines - Microsoft Fabric | Microsoft Learn

Thank you 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

[ManishBundhun]

Hello,

Thank you for your response.
In Fabric we use have the F8 CU and in azuresql db, we have 20vcores.

If Fabric is this inefficient compared to AzureSQL, then it makes it much less attractive for datawarehousing, especially given when Fabric compute capacity is used per second.

Gr,
Manish

[Anonymous · Accepted solution]

Hi @ManishBundhun ,
Thank you @suparnababu8 for the helpful response!

Thanks for sharing your concern. The performance difference between Fabric (F8) and Azure SQL (20 vCores) mainly comes down to architecture.

• Fabric is built for analytics, while Azure SQL is optimized for fast transactional updates. Fabric dynamically allocates resources, which can add some overhead: less noticeable for large queries but more so for frequent updates.

• If updates are frequent, you might see better performance by batching them or handling them in Azure SQL before syncing to Fabric.
  Refer the links here: Batching ,copy-performance 
  
  

Hope this resoolved your query.If so, consider accepting it as solution to help others to find it qickly.

Regards,
Pallavi G.

[ManishBundhun]

On a slightly different note,
If a fabric query takes 0.401 allocated CPU time in seconds, is this considered as 0.401 in the capacity unit second total or is this rounded to some number?

[Anonymous]

Hi @ManishBundhun ,
Thank you for the follow-up! We would be happy to assist you.

In Microsoft Fabric, capacity is billed based on actual usage measured in Capacity Unit (CU) seconds, tracked with high precision.No rounding to whole seconds like 1 second.
For your query with 0.401 seconds of allocated CPU time on an F8 capacity, it wouldn’t simply be billed as 0.401 CU seconds. 

This per-second billing model is indeed an advantage of Fabric, offering granular and cost-effective usage compared to traditional hourly billing systems. To confirm the exact CU seconds for your query, you can check the Microsoft Fabric Capacity Metrics app, which logs precise consumption details.
Aslo please refer the documents below:
https://learn.microsoft.com/en-us/fabric/database/sql/usage-reporting 
https://learn.microsoft.com/en-us/fabric/enterprise/buy-subscription 

If this helps,mark it as 'accept as solution'

Thank you for being a member of Microsoft Fabric Community Forum!

Regards,
Pallavi.