Re: How to optimize queries to run on fabric wareh...

AnmolGan81 · ‎08-07-2025

I have various stored procedures with custom logic built in them like SCD2,SCD1, temptables data inserts into physical tables various update insert statments for wide column tables and huge transcation tables, same stored procedures run fine on Synapse, but lags behin in fabric warehouse, we have f16 capacity so it cant be a capacity constraint. What want is how to optimize these stored procedures, so that it should run similar to how it is running is synspase.

If the stored procedure for a fact table taking 2-3mins to successfuly execute and process data in synpase, then how can we make it execute in similar time that is now taking 10-20mins and failing sometimes and succedding in retry and we are wasiting around 40-50mins in 1 sp in re-tries.

Is there something similar or some documentations that can help us out here? I have read about warehouse tables and all but it does not help much here, and we cannot break those tables to do much of the workload in lakehouse rather warehouse, since we are migrating from synapse to fabric so whole framework is built, and we cannot really modify them, can we do something to optimize these queries without changing the whole framework?

V-yubandi-msft

Hi @AnmolGan81 ,

I hope everything is resolved on your side. If you need any additional information or assistance, please let me know. I'm happy to help.

Thank you,

Yugandhar.

V-yubandi-msft

Hi @AnmolGan81 ,

Could you confirm if your issue is resolved, or if you need any additional details.

Thank you.

V-yubandi-msft · ‎08-08-2025

Hi @AnmolGan81 ,

Thank you for sharing your experience @spaceman127 the documentation link you included is very helpful. The Fabric Warehouse performance guidelines provide useful recommendations on table types, CTAS, indexing, and distribution, which are great starting points.

Additionally, you might find the Fabric Metrics App valuable for monitoring capacity usage, query activity, and memory issues. If you’re experiencing retries even with F16 capacity, this tool can help identify resource constraints.

1. You could also explore Fabric Usage Analytics Monitoring to detect patterns like retry storms, skewed queries, or lengthy operations that may impact performance.

2. For stored procedures with SCD logic, try replacing heavy MERGE statements with CTAS or INSERT/UPDATE patterns when possible, as they generally perform better in Fabric.

3. Reviewing query execution plans can also uncover slow joins, table scans, or shuffles that might be causing delays. Even small changes based on these insights can improve performance.

Thanks for response @spaceman127 .

I hope this helps.

Regards,

Yugandhar.