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 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).
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
$ 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:
INSERTstatement so that one statement inserts multiple records.
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.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.