Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

How do I improve performance when inserting data from Oracle into SQL Server?

Oracle Database Gateways allow Oracle client applications to access non-Oracle databases. The Oracle Database Gateway for ODBC (DG4ODBC) enables Oracle client applications to access databases for which an ODBC driver is available, such as Microsoft SQL Server. The Oracle Database Gateway for ODBC was previously known as HSODBC.

This article illustrates the performance implications associated with different methods of inserting data from Oracle into SQL Server through the Oracle Database Gateway for ODBC.

This article assumes that you have already set up and tested your Easysoft SQL Server ODBC driver data source. The article also assumes that you have set up your DG4ODBC or HSODBC link. The following articles provide instructions on how to do this:

Note Ensure that DG4ODBC or HSODBC, unixODBC, and SQL Server ODBC driver logging are all turned off, as these tracing mechanisms adversely affect performance. To turn logging off, check that the following settings are set to the values shown in the table (or not present in the relevant configuration files, as in all cases, logging is off by default).

File Setting Value
initsid.ora HS_FDS_TRACE_LEVEL OFF
/etc/odbcinst.ini Trace NO
/etc/odbc.ini Logging NO

To follow the examples in this article, you need access to a SQL Server table that you have permission to update.

Before inserting our test data, we created a link from Oracle to SQL Server named odbc. We then used SQL*Plus to create a test table in SQL Server.

SQL > create table insert_performance_test@odbc (sid integer, nm varchar(255), primary key (sid));

The simplest way to insert data is to use an INSERT statement similar to:

insert into insert_performance_test@odbc values (1, 'This is data insert test 1');

To insert more rows, you would execute further INSERT statements. For example:

insert into insert_performance_test@odbc values (2, 'This is data insert test 2');

This method can be very time consuming if you are inserting a large amount of data because the INSERT statement needs to be parsed each time a row is inserted. When testing insert performance, we used SQL*Plus to execute a file containing 10000 INSERT statements:

$ sqlplus my_oracle_user_name/my_oracle_password@odbc < sql_data.txt > sql_output.txt

On our machines, this test took 50 seconds to insert 10000 rows.

To reduce the time taken to insert our data, we then:

For example:

DECLARE c NUMBER; n NUMBER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@odbc;
  DBMS_HS_PASSTHROUGH.PARSE@odbc(c,'insert into insert_performance_test values
    ( 1, ''This is data insert test 1''),
    ( 2, ''This is data insert test 2''),
    ( 3, ''This is data insert test 3'')');
  n := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@odbc (c);
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@odbc(c);
END;
/

These modifications reduced the time taken to insert our test data from 50 seconds to under one second. In other tests done both in-house and at customer sites, we got up to a 70-fold improvement when multiple inserts were replaced by the PL/SQL method shown earlier.

Further information