Easysoft Data Access

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

Article:
01025
Last Reviewed:
31st August 2011
Revision:
1

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 illustrate the performance differences associated with different methods of inserting data from Oracle® into SQL Server via the Oracle® Database Gateway for ODBC.

This article assumes that you have already set up and tested your Easysoft ODBC-SQL Server 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 these tasks:

Note Ensure that DG4ODBC/hsODBC, unixODBC and Easysoft ODBC-SQL Server Driver logging are all turned off, as these tracing mechanisms will all 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).

FileSettingValue
initsid.oraHS_FDS_TRACE_LEVELOFF
/etc/odbcinst.iniTraceNO
/etc/odbc.iniLoggingNO

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_username/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 our customers’ sites, we saw up to a 70-fold improvement when multiple inserts were replaced by the PL/SQL method shown earlier.

See Also
Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.