INSERT statement running slow

Question

We use AZURE SQL MI for our data mart where we move data from the data lake. For that step, we use Spark to run SQL code to extract data from data lake and insert into staging database in SQL server. Extract part takes just over 2min but the insert into SQL server table (which is just a dump of data, no transformation included) takes 30 min.
This is the insert table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE CUBE_STAGE_DQ ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
There is a clustered index on the table, but even when we try to remove index, there is no improvement in the insert time.
JDBC driver version is mssql-jdbc-9.2.0.jre8.
All setings on SQL server are pretty much default for managed instance.

Answer 1

Will you be willing to try in-memory OLTP and columnstore indexes to improve date ingestion performance on your Azure SQL Managed Instance? This article will give the instructions you need.

Answer 2

Hi Dan, we actually use newer version of the connector https://learn.microsoft.com/en-us/sql/connect/spark/connector?view=sql-server-ver16
JDBC-9.2.0.jre8
Is there anything specific we need to verify in terms of configuration that might help with performance?
Thank you for your help and suggestions.