INSERT statement running slow

Dragana Kubura 1 Reputation point
2022-11-10T02:05:26.823+00:00

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.

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 33,176 Reputation points MVP
    2022-11-10T03:28:06.207+00:00

    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.


  2. Dragana Kubura 1 Reputation point
    2022-11-21T18:24:26.203+00:00

    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.

    0 comments No comments