KB487397: Improve Databricks Performance Using the UseNativeQuery JDBC Option

Introduction

The

UseNativeQuery

parameter controls how SQL queries are processed by the driver. The default value is

2

. When you set it to

1

, the driver passes SQL queries to the database without transformation.

Steps

Use the parameter in the following methods:

• Add

  UseNativeQuery=1; 

  to the JDBC URL
• When creating or editing the data source, add

  UseNativeQuery=1; 

  to Additional Connection String Parameters. For more information, see Create and Edit Data Sources.

Advantages

Universal Benchmark Performance Improvement

UseNativeQuery=1 produces a 15-28% improvement in throughput across 1x and 10x using the current out-of-the-box setting.

• Throughput Comparison: UseNativeQuery=1 outperforms UseNativeQuery=2 in 1x and 10x load scenarios, with higher throughput and better scalability.
• Response Time Comparison: UseNativeQuery=1 demonstrates lower response times across all concurrency levels compared to UseNativeQuery=2.
• Concurrency Efficiency: UseNativeQuery=1 handles higher thread counts more efficiently, with minimal degradation in throughput.

See the following graph on throughput for each parameter:

Reduce SQL Syntax Error Due to SQL Transformation

By enabling UseNativeQuery, the system directly sends the original SQL to the database without transforming or rewriting it. This parameter bypasses potential query syntax errors introduced by automated transformations, ensuring that complex or database-specific SQL statements execute as intended without compatibility issues. It is especially beneficial when you work with advanced queries that depend on native database functions or syntax.

Limitations

This parameter can lead to certain behaviors and limitations, especially concerning queries with parameters and bulk operations. 

Bulk Insert Performance

The

UseNativeQuery=1

parameter allows the driver to pass each insert operation directly to the database without batching them into a "INSERT INTO ... VALUES" statement. This can lead to performance issues, as each insert is processed individually.
Setting

EnableNativeParameterizedQuery=0

while using

UseNativeQuery=1

can resolve this issue. It ensures that bulk insert operations are preserved even when

UseNativeQuery=1

is enabled.

Multiple Statements Are Not Allowed When Enabling Parameter Queries

Executing multiple statements in a single transaction using parameterized queries can be challenging when UseNativeQuery=1 is set. The driver passes each statement directly to the database without transformation, which may not support multiple statements in a single execution context, especially when parameters are involved.

Parameters Count Limit for Parameter Queries

The maximum number of parameters that can be used in a query is 256 parameters.