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).
| 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_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:
INSERT statement so that one statement inserts multiple records.The DBMS_HS_PASSTHROUGH Oracle PL/SQL package enables you to use passthrough SQL, where statements are sent directly to the non-Oracle system without being interpreted by the Oracle server.
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.
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle® is a registered trademark of Oracle® Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
