Speed Up JDBC Write from Databricks Notebook to MS SQL Server

[berserkersap]

Hello Everyone,

I have a use case where I need to write a delta table from DataBricks to a SQL Server Table using Pyspark/ python/ spark SQL .

The delta table I am writing contains around 3 million records and the SQL Server Table is neither partitioned nor indexed. It is taking around 8-10 min to write into the SQL Server Table no matter what compute I use (using default JDBC driver).

The code being used:

df.write.mode("overwrite").option("truncate", True ).jdbc(Url, "dbtable" , properties = properties )

I have executed this code on DataBricks runtime 11.3LTS using the compute E4ds v4 and E16ds v4 but in both the cases it took 8-10 min.

Can anyone please suggest a way to reduce this time ?

P.S. I have tried to increase the batch size but even that was not helping. If it was more than 20000 the process became even slower

Also, I cannot install anything on the cluster according to the client's requirement. But if this is absolutely necessary please let me know what to install and how to install.

I have tried using the driver "com.microsoft.sqlserver.jdbc.spark", however, it gave me an error 

java.lang.ClassNotFoundException: Failed to find data source: com.microsoft.sqlserver.jdbc.spark.

 Thank You

[NandiniN]

You can partition the DataFrame in PySpark before writing it to SQL Server. For example:

df.repartition(10).write.mode("overwrite").option("truncate", True).jdbc(Url, "dbtable", properties=properties)

[NandiniN]

https://docs.databricks.com/en/connect/external-systems/jdbc.html#control-parallelism-for-jdbc-queri...

When writing to databases using JDBC, Apache Spark uses the number of partitions in memory to control parallelism. You can repartition data before writing to control parallelism. Avoid high number of partitions on large clusters to avoid overwhelming your remote database. The following example demonstrates repartitioning to eight partitions before writing:

(employees_table.repartition(8)
  .write
  .format("jdbc")
  .option("url", "<jdbc-url>")
  .option("dbtable", "<new-table-name>")
  .option("user", "<username>")
  .option("password", "<password>")
  .save()
)

[NandiniN]

https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html

numPartitions

(none)

The maximum number of partitions that can be used for parallelism in table reading and writing. This also determines the maximum number of concurrent JDBC connections. If the number of partitions to write exceeds this limit, we decrease it to this limit by calling coalesce(numPartitions) before writing.

read/write

[VZLA]

@berserkersap have you had time to identify where's the bottleneck? e.g.: sequential writes, network latency/throughput, or maybe you have a connection pool in the target much lower than the number of connection threads in the source?