Poor ODBC insert performance in Warehouse

[cpshort7186]

I'm trying to insert records into a Warehouse table with Alteryx and still extreme performance issues.  Data output is just a few thousand rows.  Job runs in 9 sec without trying to write to Fabric Warehouse, and takes 35min plus to run loading to warehouse.  Sometimes it just fails.  I'm connecting to the warehouse through the SQL endpoint via ODBC.  

[v-shamiliv]

Hi @cpshort7186 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-shamiliv]

Hi @cpshort7186 
Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for your understanding and assistance.

[v-shamiliv · Accepted solution]

Hi @cpshort7186 
Thank you for reaching out microsoft fabric community forum.

• While there isn’t a direct "enable bulk loading" checkbox in Alteryx, you can achieve bulk loading by using the MS SQL Server Bulk connection type with the ODBC Driver 18 for SQL Server. This method bypasses row-by-row inserts and uses bulk operations, which can significantly improve performance.
  
  
• Fabric Warehouse has been reported to perform better with smaller batch sizes. Try with committing data in batches of 1,000 rows (or fewer) to reduce transactional overhead.
  
  Instead of writing directly to Fabric Warehouse, try the following:
  •  Write your data to a Lakehouse in Parquet format (this is highly performant for ingestion). Create a Shortcut in the Fabric Warehouse to query the data from the Lakehouse.
  • This approach leverages the strengths of Lakehouse for ingestion and Warehouse for querying.
    
    

If Alteryx continues to underperform for your use case, consider evaluating other ETL tools that may offer better integration with Fabric Warehouse. Tools like Azure Data Factory or Synapse Pipelines are designed to work seamlessly with Microsoft’s data platforms.

If the issue persists, we recommend raising a support ticket for further assistance. To create a support ticket for Fabric and Power BI, please refer to the steps outlined in the following guide:

How to create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community.
Thank you.

[cpshort7186]

Thanks, but I don't see an option for "MS SQL Server Bulk connection type" witht he ODBC Driver 18.  Do you have any examples you could point me toward? 

I updated the commit batch size from 10,000 to 500 and re-ran the job.  It still took ~25min to run (as opposed to 9 seconds when ODBC insert steps are turned off).  I guess you'd call that "better" than 35min, but still not acceptable performance.

I understand that I could output to files into lakehouse, but that is not really the point of this question/issue.  I chose to use a warehouse as my Gold layer specifically to support using external tools via SQL endpoint when needed.  It is much faster in some cases to use a tool like Alteryx for development than to write in a notebook, and I would like to reduce the number of steps needed.  

[nilendraFabric]

Hello @cpshort7186 

Fabric’s Warehouse currently lacks indexing support and has transactional limitations (e.g., slow commits with small batch sizes)

People have reported 1000 per batch commit, non official doc. But have found a thread in community. 

https://community.fabric.microsoft.com/t5/Dataflow/Azure-Fabric-warehouse-performance/m-p/3652346

i would suggest Write data to a Lakehouse (Parquet files) and create a Shortcut in the Warehouse for querying

Worth trying this

Enable bulk loading in Alteryx’s Output Tool. This bypasses row-by-row inserts and uses bulk operations.
• Use the `MS SQL Server Bulk` connection type with the ODBC Driver 18 for SQL Server

https://community.fabric.microsoft.com/t5/Data-Warehouse/Using-Alteryx-with-Fabric/m-p/4168227

if this is helpful please accept the answer and give kudos

[cpshort7186]

First linked thread confirms same issue I'm seeing, but doesn't seem to provide a solution. 

Second linked thread is one I created.  I don't see an option to "enable bulk loading" in alteryx or in the ODBC configuration.  

How can anyone be using this product seriously if it does not support performant loading from external ETL tools?