OracleLoader Slow Performance

mamelino · Post by **mamelino** » Tue 21 Aug 2018 15:51

Hello,

i'm migrating a web application from sql server to oracle and i am evaluating dotConnect for Oracle.
With sql server i used to load data from datasets into the database using SqlBulkCopy and the OracleLoader seemed the best alternative for Oracle but as far as i can see the OracleLoader is terribly slow.

Here is the code snipped that i'm using for the bulk insert, i've done some tests with different datasets and it takes around 10 minutes to load 1k records from a dataset (the same operation using SqlBulkCopy took 783ms)

Code: Select all

using (DataLayerUnmanaged DataLayerManager = new DataLayer.DataLayerUnmanaged())            
            {
                using (Devart.Data.Oracle.OracleLoader loader = new Devart.Data.Oracle.OracleLoader())
                {
                    loader.Connection = DataLayerManager.Connection;
                    loader.TableName = destinationTableName;
                    loader.BatchSize = dt.Rows.Count;
                    OracleGlobalization glob = OracleGlobalization.GetApplicationInfo();
                    glob.DateFormat = "DD/MM/YYYY";
                    OracleGlobalization.SetApplicationInfo(glob);.
                    loader.Connection.SetSessionInfo(glob);
                    loader.LoadTable(dt);
                }
            }

Am i doing something wrong?

Thank you

Pinturiccio · Post by **Pinturiccio** » Thu 23 Aug 2018 16:35

We are investigating the issue and will post here when we get any results.

Pinturiccio · Post by **Pinturiccio** » Mon 27 Aug 2018 12:40

Please provide the following information:
1. What connection mode are you using: OCI or Direct?
2. Oracle server version.
3. Oracle table DDL script.
4. Please also describe data that is stored in SQL Server so that we can reproduce such a use case. For example, if there is a varchar(max) column in SQL Server, how big text values are stored there.

mamelino · Post by **mamelino** » Mon 27 Aug 2018 13:18

Hello, thank you for your reply

1. What connection mode are you using: OCI or Direct?
>> Direct
2. Oracle server version.
>> 12c
3. Oracle table DDL script.

Code: Select all

CREATE TABLE TABLE1
(
  RECORD_ID                    NUMBER(10)       NOT NULL,
  MD_IF_FILE_FK                RAW(16)          NOT NULL,
  MD_IF_FILE_TYPE_FK           RAW(16),
  REPORTING_DT                 VARCHAR2(255 CHAR),
  EXTRACTION_TYPE              VARCHAR2(255 CHAR),
  RECORD_TYPE                  VARCHAR2(255 CHAR),
  LEGAL_ENTITY_CD              VARCHAR2(255 CHAR),
  CREDIT_CONTROL_AREA          VARCHAR2(255 CHAR),
  CUSTOMER_CD                  VARCHAR2(255 CHAR),
  CUSTOMER_VAT_CD              VARCHAR2(255 CHAR),
  ON_BOOK_LEGAL_ENTITY         VARCHAR2(255 CHAR),
  SHIPTO_COUNTRY_NM            VARCHAR2(255 CHAR),
  INVTO_ISO_COUNTRY_ALFA2_CD   VARCHAR2(255 CHAR),
  SHIPTO_ISO_COUNTRY_ALFA2_CD  VARCHAR2(255 CHAR),
  BRAND                        VARCHAR2(255 CHAR),
  BUSINESS                     VARCHAR2(255 CHAR),
  CURRENCY                     VARCHAR2(255 CHAR),
  CUSTOMER_CLS_CD              VARCHAR2(255 CHAR),
  CUSTOMER_TYPE_CD             VARCHAR2(255 CHAR),
  TRADING_PARTNER_CD           VARCHAR2(255 CHAR),
  CUSTOMER_NM                  VARCHAR2(255 CHAR),
  OUTSTANDING_AMT              VARCHAR2(255 CHAR),
  NOTES_RECEIVABLE_AMT         VARCHAR2(255 CHAR),
  DIRECT_DEBIT_AMT             VARCHAR2(255 CHAR),
  PASSIVE_ITEM_AMT             VARCHAR2(255 CHAR),
  SUSPENDED_AMT                VARCHAR2(255 CHAR),
  PASTDUE_AMT                  VARCHAR2(255 CHAR),
  PASTDUE_1_30_AMT             VARCHAR2(255 CHAR),
  PASTDUE_31_60_AMT            VARCHAR2(255 CHAR),
  PASTDUE_61_90_AMT            VARCHAR2(255 CHAR),
  PASTDUE_91_120_AMT           VARCHAR2(255 CHAR),
  PASTDUE_121_180_AMT          VARCHAR2(255 CHAR),
  PASTDUE_181_210_AMT          VARCHAR2(255 CHAR),
  PASTDUE_211_360_AMT          VARCHAR2(255 CHAR),
  PASTDUE_OVER_360_AMT         VARCHAR2(255 CHAR),
  UNALLOCATED_CREDIT_AMT       VARCHAR2(255 CHAR),
  LETTER_CREDIT_COVER_AMT      VARCHAR2(255 CHAR),
  INV_12M_AMT                  VARCHAR2(255 CHAR),
  INV_MTD_AMT                  VARCHAR2(255 CHAR),
  INV_YTD_AMT                  VARCHAR2(255 CHAR),
  PAY_12M_AMT                  VARCHAR2(255 CHAR),
  PAY_YTD_AMT                  VARCHAR2(255 CHAR),
  PAY_MTD_AMT                  VARCHAR2(255 CHAR),
  WRITEOFF_12M_AMT             VARCHAR2(255 CHAR),
  WRITEOFF_YTD_AMT             VARCHAR2(255 CHAR),
  WRITEOFF_MTD_AMT             VARCHAR2(255 CHAR),
  PASTDUE_XCR_1_30_AMT         VARCHAR2(255 CHAR),
  PASTDUE_XCR_31_60_AMT        VARCHAR2(255 CHAR),
  PASTDUE_XCR_61_90_AMT        VARCHAR2(255 CHAR),
  PASTDUE_XCR_91_120_AMT       VARCHAR2(255 CHAR),
  PASTDUE_XCR_121_180_AMT      VARCHAR2(255 CHAR),
  PASTDUE_XCR_181_210_AMT      VARCHAR2(255 CHAR),
  PASTDUE_XCR_211_360_AMT      VARCHAR2(255 CHAR),
  PASTDUE_XCR_OVER_360_AMT     VARCHAR2(255 CHAR),
  FUTUTREDUE_AMT               VARCHAR2(255 CHAR),
  FUTUREDUE_1_30_AMT           VARCHAR2(255 CHAR),
  FUTUREDUE_31_60_AMT          VARCHAR2(255 CHAR),
  FUTUREDUE_61_90_AMT          VARCHAR2(255 CHAR),
  FUTUREDUE_91_120_AMT         VARCHAR2(255 CHAR),
  FUTUREDUE_121_180_AMT        VARCHAR2(255 CHAR),
  FUTUREDUE_181_210_AMT        VARCHAR2(255 CHAR),
  FUTUREDUE_211_360_AMT        VARCHAR2(255 CHAR),
  FUTUREDUE_OVER_360_AMT       VARCHAR2(255 CHAR),
  FUTUREDUE_XCR_1_30_AMT       VARCHAR2(255 CHAR),
  FUTUREDUE_XCR_31_60_AMT      VARCHAR2(255 CHAR),
  FUTUREDUE_XCR_61_90_AMT      VARCHAR2(255 CHAR),
  FUTUREDUE_XCR_91_120_AMT     VARCHAR2(255 CHAR),
  FUTUREDUE_XCR_121_180_AMT    VARCHAR2(255 CHAR),
  FUTUREDUE_XCR_181_210_AMT    VARCHAR2(255 CHAR),
  FUTUREDUE_XCR_211_360_AMT    VARCHAR2(255 CHAR),
  FUTUREDUE_XCR_OVER_360_AMT   VARCHAR2(255 CHAR),
  BADDEBT_RESERVE_AMT          VARCHAR2(255 CHAR),
  PREPAY_AMT                   VARCHAR2(255 CHAR)
)

4. Please also describe data that is stored in SQL Server so that we can reproduce such a use case. For example, if there is a varchar(max) column in SQL Server, how big text values are stored there.
>>As you can see there are no big string, almost every field is loaded as Varchar2 255.
I also want you to be aware that in the meantime i've also done a test using the class OracleBulkCopy (ODAC) and it is as fast as SqlBulkCopy, here is the code snippet with OracleBulkCopy:

Code: Select all

               Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(conns);
            conn.Open();
            using (OracleBulkCopy loader = new OracleBulkCopy(conn))
            {
                loader.DestinationTableName = destinationTableName;
                loader.BulkCopyTimeout = 600;
                Oracle.DataAccess.Client.OracleGlobalization glob = Oracle.DataAccess.Client.OracleGlobalization.GetThreadInfo();
                glob.DateFormat = "DD/MM/YYYY";
                Oracle.DataAccess.Client.OracleGlobalization.SetThreadInfo(glob);
                loader.Connection.SetSessionInfo(glob);
                loader.BatchSize = dt.Rows.Count;              
                loader.WriteToServer(dt);
            }

Thank you

Pinturiccio · Post by **Pinturiccio** » Wed 29 Aug 2018 15:04

We have answered you via e-mail.

Devart Forums

OracleLoader Slow Performance

OracleLoader Slow Performance

Re: OracleLoader Slow Performance

Re: OracleLoader Slow Performance

Re: OracleLoader Slow Performance

Re: OracleLoader Slow Performance