KB484745: How to enable bulk load to improve bulk inserting performance against Azure Synapse Analytics

Quality Engineer, Principal • MicroStrategy

During our report execution, sometimes we need create temporary table to store intermediate results. If the data of temporary table is inserted by ‘insert into values’ query, we can enable parameterized query to improve bulk inserting performance. This works for Oracle, DB2, SQL Server, Teradata, Snowflake.

During our report execution, sometimes we need create temporary table to store intermediate results.
If the data of temporary table is inserted by ‘insert into values’ query, we can enable parameterized query to improve bulk inserting performance. This works for Oracle, DB2, SQL Server, Teradata, Snowflake.
Sample SQL:
create table #T5MU65K3CMQ000([CUSTOMER_ID] SMALLINT)；
insert into #T5MU65K3CMQ000 values (?)

However, for Azure Synapse Analytics，the performance of bulk inserting is slow no matter Parameterized query is on/off. We can enable the setting ‘Enable bulk load’ to improve the performance significantly. This setting works when parameterized query is on.
In-house test result:

PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout
PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout
PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout
PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout	PQ on without bulk loadPQ off without bulk loadPQ on with bulk loadPQ off with bulk loadinserting 300 rows into temp table50s28s1.7s30sinserting 1800 rows into temp table240s163s2.3s161sinserting 18000 rows into temp tableTimeout (>600 seconds)Timeout2.6sTimeout

Workarounds

How to enable the setting ‘Enable bulk load’?

For Windows ODBC DSN, we could enable this setting by following steps.

Open ODBC Data Source(64 bit), choose the DSN, select ‘Configure’.

Change to Tab ‘Bulk’， check the box ‘Enable Bulk Load’.

For Linux ODBC DSN,

Edit configure file ‘ODBC.ini’.
Add following string ‘EnableBulkLoad=1’. Sample:

[WH_T26.AzureSQLDataWarehouse]Description=Strategy ODBC Driver for SQL Server Wire ProtocolDriver=(driver path)/MYsqls64.soDatabase=HostName=PortNumber=1433DoubleToStringPrecision=17EncryptionMethod=1ValidateServerCertificate=0EnableQuotedIdentifiers=EnableBulkLoad=1

For ODBC DSNless connection,

We could add ‘EnableBulkLoad=1;’into Connection String to enable the setting. Sample:

DRIVER={Strategy ODBC Driver for SQL Server Wire Protocol}; hostname=; Port=1433; Database=; EncryptionMethod=1; ValidateServerCertificate=0;EnableQuotedIdentifiers=1; EnableBulkLoad=1;

For JDBC driver,

We could add ‘EnableBulkLoad=true;’ into Connection String to enable this setting. Sample:

JDBC;DRIVER={com.Strategy.jdbc.sqlserver.SQLServerDriver};URL={jdbc:Strategy:sqlserver://db_server_address:1433;DatabaseName=;User=;PASSWORD=;fetchTWFSasTime=TRUE; EnableBulkLoad=true;}

Comment

0 comments

Details

Knowledge Article

Published:

December 9, 2020

Last Updated:

January 20, 2021

[WH_T26.AzureSQLDataWarehouse]Description=Strategy ODBC Driver for SQL Server Wire ProtocolDriver=(driver path)/MYsqls64.soDatabase=HostName=PortNumber=1433DoubleToStringPrecision=17EncryptionMethod=1ValidateServerCertificate=0EnableQuotedIdentifiers=EnableBulkLoad=1

For ODBC DSNless connection,

We could add ‘EnableBulkLoad=1;’into Connection String to enable the setting. Sample:

DRIVER={Strategy ODBC Driver for SQL Server Wire Protocol}; hostname=; Port=1433; Database=; EncryptionMethod=1; ValidateServerCertificate=0;EnableQuotedIdentifiers=1; EnableBulkLoad=1;

For JDBC driver,

We could add ‘EnableBulkLoad=true;’ into Connection String to enable this setting. Sample: