How to improve performance of OraLoader.

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ngonjp
Posts: 1
Joined: Sat 19 Jan 2019 07:27

How to improve performance of OraLoader.

Post by ngonjp » Sat 19 Jan 2019 07:48

Hi, I'm a just newbie, so plesase help me.

I'm using ORADC 9.6.22 with Oracle12c + Delphi XE7 on android.
My goal is copy records from a SQLite table to a Oracle table with the same columns with the source table as fast as possible.
I tried OraLoader and found it not so fast as expected.

Code: Select all

procedure TDMOraSQLiteBridge.mp_CopyFromSQLiteQueryToOra(
    const P_SRC_SQLITE_QRY    : TLiteQuery;
    const P_DEST_TABLE_NAME   : String;
    const P_DEST_ORA_SESSION  : TOraSession);
var
    WL_OraLoader  : TOraLoader;
begin
    P_SRC_SQLITE_QRY.Last;
    P_SRC_SQLITE_QRY.First;
    WL_OraLoader :=  TOraLoader.Create(nil);
    WL_OraLoader.Connection :=  P_DEST_ORA_SESSION;
    WL_OraLoader.LoadMode   :=  lmDML;
    WL_OraLoader.TableName  :=  P_DEST_TABLE_NAME;
    WL_OraLoader.LoadFromDataSet(P_SRC_SQLITE_QRY);
end;
The table has more than 300 columns.
Are there any ideas to improve the performance?

Thanks in advance.

Code: Select all

CREATE TABLE IFW_MOBF_KENSHIN_DATA ( 
    ID                              NUMBER(38)      NOT NULL,
    SUISEN_ID                       NUMBER(14),
    CYCLE_NO                        NUMBER(3),
    MACHI                           NUMBER(4),
    SATSU                           NUMBER(4),
    PAGE                            NUMBER(4),
    EDA                             NUMBER(2),
    KENSHININ_CD                    NUMBER(4),
    MSG                             NVARCHAR2(1000),
    JUSYO                           NVARCHAR2(40),
    KATAGAKI                        NVARCHAR2(40),
    SHIMEI_KANA                     NVARCHAR2(100),
    SHIMEI_GAIJI_FLG                NUMBER(1),
    SHIMEI_KNJ                      NVARCHAR2(40),
    TEL_KBN                         NUMBER(1),
    TEL1                            NVARCHAR2(15),
    SUISEN_NO                       NUMBER(8),
    METER_NO                        NVARCHAR2(8),
    METER_KETA                      NUMBER(1),
    KOKEI                           NUMBER(3),
    KOKEI_OLD                       NUMBER(3),
    KEISAN_KOKEI                    NUMBER(3),
    METER_SHIYORYO_UMU              NUMBER(1),
    TORIKAE_YMD                     NUMBER(8),
    METER_ICHI_KAMI                 NUMBER(2),
    METER_ICHI_NAKA                 NUMBER(2),
    METER_ICHI_SHIMO                NUMBER(1),
    METER_ICHI_BIKO                 NVARCHAR2(40),
    JOSUI_YOTO                      NUMBER(2),
    JOSUI_YOTO_OLD                  NUMBER(2),
    JOSUI_YOTO_HENKO_YMD            NUMBER(8),
    GESUI_YOTO                      NUMBER(2),
    JUSUISO_KBN                     NUMBER(1),
    HEISENJI_SHISHIN                NUMBER(8),
    TORIHAZUSHI_SHISHIN1            NUMBER(8),
    TORITSUKE_SHISHIN1              NUMBER(8),
    NYUTAI_KBN                      NUMBER(1),
    KAIHEI_KBN                      NUMBER(2),
    CHOSYU_KBN                      NUMBER(1),
    SEIKYU_JIKI_KBN                 NUMBER(2),
    OSHIRASE_KBN1                   NUMBER(1),
    OSHIRASE_KBN3                   NUMBER(1),
    SHIYO_KBN                       NUMBER(2),
    JOSUI_KBN                       NUMBER(2),
    GESUI_KBN                       NUMBER(2),
    CHIIKI_CD                       NUMBER(2),
    KENSHINTSUKI_KBN                NUMBER(1),
    TESURYO_KBN                     NUMBER(1),
    SEISAN_HOHO                     NUMBER(1),
    KYUTEI_SHISHIN                  NUMBER(8),
    KYUTEI_LITRE                    NUMBER(3),
    YOYAKU_KBN                      NUMBER(1),
    YOYAKU_YMD                      NUMBER(8),
    YOYAKU_TIME_FROM                NVARCHAR2(4),
    YOYAKU_TIME_TO                  NVARCHAR2(4),
    YOYAKUJI_BIKO                   NVARCHAR2(130),
    OYAKO_KBN                       NUMBER(2),
    OYA_SUISEN_NO                   NUMBER(14),
    KENMANJI_SUIRYO                 NUMBER(8),
    NINTEI_NINZU                    NUMBER(4),
    NINTEI_SUIRYO                   NUMBER(8),
    KOSU                            NUMBER(4),
    JOSUI_MENJO_CD                  NUMBER(1),
    GESUI_MENJO_CD                  NUMBER(1),
    HORYU_KBN                       NUMBER(1),
    TAINO_KBN                       NUMBER(1),
    JOSUI_KAISHI_YMD                NUMBER(8),
    GESUI_KAISHI_YMD                NUMBER(8),
    KYUSUI_TEISHI_KAIJO_YMD         NUMBER(8),
    KYUSUI_TEISHI_YMD               NUMBER(8),
    SHIRASE_KAISHI_YMD              NUMBER(6),
    TAISYO_TSUKI                    NUMBER(6),
    OSHIRASE_KBN2                   NUMBER(1),
    RYO_CHOTEI_YM                   NUMBER(6),
    RYO_HIKIOTOSHI_YMD              NUMBER(8),
    RYO_JOSUI_SUIRYO                NUMBER(8),
    RYO_JOSUI_KINGAKU               NUMBER(10),
    RYO_JOSUI_ZEI                   NUMBER(10),
    RYO_GESUI_SUIRYO                NUMBER(8),
    RYO_GESUI_KINGAKU               NUMBER(10),
    RYO_GESUI_ZEI                   NUMBER(10),
    RYO_NOHAI_SUIRYO                NUMBER(8),
    RYO_NOHAI_KINGAKU               NUMBER(10),
    RYO_NOHAI_ZEI                   NUMBER(10),
    RYO_TESURYO                     NUMBER(10),
    RYO_GOKEI_KINGAKU               NUMBER(10),
    RYO_JIKAI_HIKIOTOSHI_YMD        NUMBER(8),
    RYO_TAISYO_YM                   NUMBER(6),
    RYO_CHOTEI_KBN                  NUMBER(1),
    RYO_NYUKIN_RENBAN               NUMBER(6),
    RYO_KESHIKOMI_YMD               NUMBER(8),
    RYO_KIKAN_CD                    NUMBER(1),
    KOZA_SYUBETSU                   NUMBER(1),
    KOZA_NO                         NUMBER(7),
    KOZA_MEIGININ                   NVARCHAR2(30),
    HONTEN_MEI                      NVARCHAR2(15),
    SHITEN_MEI                      NVARCHAR2(20),
    SHIYO_TSUKISU                   NUMBER(2),
    YOBIDASHI_NO                    NUMBER(8),
    CHANNEL_NO                      NUMBER(2),
    ICHI_HENKO_FLG                  NUMBER(1),
    ICHI_HENKO1                     NUMBER(1),
    ICHI_HENKO2                     NUMBER(1),
    ICHI_HENKO3                     NUMBER(1),
    KENSHINZUMI_FLG                 NUMBER(1),
    KENSHIN_YMD                     NUMBER(8),
    KENSHINSHA_NO                   NUMBER(4),
    KENSHIN_KONKAI_SHISHIN          NUMBER(8),
    KENSHINJUN_NO                   NUMBER(3),
    KENSHIN_KAISHI_JI               NVARCHAR2(2),
    KENSHIN_KAISHI_FUN              NVARCHAR2(2),
    KENSHIN_KAISHI_BYO              NVARCHAR2(2),
    KENSHIN_SYURYO_JI               NVARCHAR2(2),
    KENSHIN_SYURYO_FUN              NVARCHAR2(2),
    KENSHIN_SYURYO_BYO              NVARCHAR2(2),
    SHIYO_NISSU_JOSUI               NUMBER(3),
    SHIYO_NISSU_GESUI               NUMBER(3),
    KENSHIN_JOSUI_SUIRYO            NUMBER(8),
    KENSHIN_JOSUI_KIKAN             NUMBER(2),
    KENSHIN_JOSUI_CHOTEIGAKU        NUMBER(10),
    KENSHIN_JOSUI_ZEI               NUMBER(10),
    KENSHIN_GESUI_SUIRYO            NUMBER(8),
    KENSHIN_NINTEI_SUIRYO           NUMBER(8),
    KENSHIN_GESUI_KIKAN             NUMBER(2),
    KENSHIN_GESUI_CHOTEIGAKU        NUMBER(10),
    KENSHIN_GESUI_ZEI               NUMBER(10),
    KENSHIN_NOHAI_SUIRYO            NUMBER(8),
    KENSHIN_NOHAI_KIKAN             NUMBER(2),
    KENSHIN_NOHAI_CHOTEIGAKU        NUMBER(10),
    KENSHIN_NOHAI_ZEI               NUMBER(10),
    KENSHIN_KAIHEI_TESURYO          NUMBER(10),
    KENSHIN_GOKEI                   NUMBER(10),
    NINTEI_CODE                     NUMBER(2),
    RYO_HAKKO_FLG                   NUMBER(1),
    RYO_HAKKO_YMD                   NUMBER(8),
    RYO_HAKKO_TIME                  NVARCHAR2(4),
    RYO_HAKKO_KAISU                 NUMBER(2),
    KONKAI_LITRE_SHISHIN            NUMBER(3),
    MEMO                            NUMBER(1),
    ICHIJI_HORYU_FLG                NUMBER(1),
    SHIYORYO_IJO1                   NUMBER(1),
    ERRCD1                          NUMBER(1),
    ERRCD2                          NUMBER(2),
    ERRCD3                          NUMBER(1),
    ERRCD4                          NUMBER(1),
    ERRCD5                          NUMBER(2),
    ROSUI_FLG                       NUMBER(1),
    INSATSU_FLG                     NUMBER(1),
    METER_IKKAITEN                  NUMBER(1),
    CHUI_RIYU                       NUMBER(1),
    METER_KETASU                    NUMBER(1),
    TEL2                            NVARCHAR2(13),
    INSATSU_KAISU                   NUMBER(1),
    NINTEI_OSUIRYO_FLG              NUMBER(1),
    SHIYORYO_IJO2                   NUMBER(1),
    GAIJI_HANTEI_FLG                NUMBER(2),
    NEW_METER_NO                    NVARCHAR2(8),
    NEW_METER_KETASU                NUMBER(1),
    TORITSUKE_SHISHIN2              NUMBER(8),
    TORIHAZUSHI_SHISHIN2            NUMBER(8),
    KAISHI_YMD                      NUMBER(8),
    SYURYO_YMD                      NUMBER(8),
    SOFU_JUSYO                      NVARCHAR2(40),
    SOFU_KATAGAKI                   NVARCHAR2(40),
    SOFU_SHIMEI_KANA                NVARCHAR2(100),
    SOFU_SHIMEI_GAIJI_FLG           NUMBER(1),
    SOFU_SHIMEI_KNJ                 NVARCHAR2(40),
    SOFU_YUBIN                      NVARCHAR2(8),
    TEIREI_HAKKO_FLG                NUMBER(1),
    TEIREI_HAKKO_YMD                NUMBER(8),
    TEIREI_NOKI_YMD                 NUMBER(8),
    TEIREI_HAKKO_MAISU              NUMBER(2),
    GENRYO_RITSU                    NUMBER(3),
    YOSUIRYO                        NUMBER(7),
    KYODO_SHIYOSU                   NUMBER(3),
    TEKKYO_CD                       NUMBER(2),
    METER_SYURUI_CD                 NUMBER(1),
    JOSUI_GENMEN_HAISHI_YMD         NUMBER(8),
    GESUI_GENMEN_HAISHI_YMD         NUMBER(8),
    GENMEN_JOSUI_SEIKYU             NUMBER(10),
    GENMEN_GESUI_SEIKYU             NUMBER(10),
    GENMEN_JOSUI_SUIRYO             NUMBER(8),
    GENMEN_GESUI_GOKEIRYO           NUMBER(8),
    KOKEI_HENKO_YMD                 NUMBER(8),
    GESUI_YOTO_HENKO_YMD            NUMBER(8),
    GESUI_YOTO_OLD                  NUMBER(2),
    KATAGAKI_KANA                   NVARCHAR2(20),
    SOFU_KATAGAKI_KANA              NVARCHAR2(20),
    KENMAN_OSHIRASE_FLG             NUMBER(1),
    KENMAN_KOKAN_YM                 NUMBER(6),
    JOSUI_KOJITEN_CD                NUMBER(3),
    JOSUI_KOJITEN_MEI               NVARCHAR2(15),
    JOSUI_KOJITEN_TEL               NVARCHAR2(15),
    GESUI_KOJITEN_CD                NUMBER(3),
    GESUI_KOJITEN_MEI               NVARCHAR2(15),
    GESUI_KOJITEN_TEL               NVARCHAR2(15),
    RYOSYU_CHOTEI_YMD_FROM          NUMBER(8),
    RYOSYU_CHOTEI_YMD_TO            NUMBER(8),
    JOSUI_SYUNO_FLG                 NUMBER(1),
    GESUI_SYUNO_FLG                 NUMBER(1),
    RYOSYU_SHIYO_KBN                NUMBER(2),
    RYOSYU_JOSUI_KBN                NUMBER(2),
    RYOSYU_GESUI_KBN                NUMBER(2),
    RYOSYU_CHIIKI_CD                NUMBER(2),
    HEISEN_KBN                      NUMBER(2),
    CHIZU_SATSU                     NVARCHAR2(1),
    CHIZU_PAGE                      NVARCHAR2(3),
    CHIZU_X_ZAHYO                   NVARCHAR2(2),
    CHIZU_Y_ZAHYO                   NVARCHAR2(2),
    NOTSU_HAKKO_FLG                 NUMBER(1),
    HAKKO_NO                        NUMBER(10),
    DEL_COUNT                       NUMBER(2),
    DEL_SHISHIN                     NUMBER(8),
    DEL_SHISHIN_L                   NUMBER(3),
    RYO_DEL_YMD                     NUMBER(8),
    RYO_DEL_TIME                    NUMBER(4),
    ESCAPE_ERR_FLG1                 NUMBER(1),
    ESCAPE_ERR_FLG2                 NUMBER(1),
    DATE_ERR                        NUMBER(1),
    KIJUNNAI_ROSUI_FLG              NUMBER(1),
    METER_SYURUI_CD_HT              NUMBER(1),
    KYAKU_KENSHIN_FLG               NUMBER(1),
    CHOTEI_ERR                      NVARCHAR2(5),
    PEND_CHILD                      NUMBER(1),
    CHOSA_FLG1                      NUMBER(1),
    CHOSA_FLG2                      NUMBER(2),
    CHOSA_FLG3                      NUMBER(1),
    CHOSA_FLG4                      NUMBER(1),
    SYUKINZUMI_FLG                  NUMBER(1),
    RYOKAI_FLG                      NUMBER(1),
    SAIHOMON_FLG                    NUMBER(1),
    KENSHIN_FUNO_FLG                NUMBER(1),
    KENSHIN_FUNO_YMD                NUMBER(8),
    RYO_HAKKO_NO                    NUMBER(13),
    SEIKYU_HAKKO_NO                 NUMBER(13),
    IDO_SUIRYO                      NUMBER(8),
    NOT_INSATSU_FLG                 NUMBER(1),
    MUSEN_KENSHIN_FLG               NUMBER(1),
    MUSEN_KENSHIN_ALARM             NVARCHAR2(5),
    CAMERA_FLG                      NUMBER(1),
    TORIKOMI_DATE                   DATE,
    SAIKENSHIN_YMD                  NUMBER(8),
    SAIKENSHIN_KAISU                NUMBER(2),
    SAIKENSHIN_KENSHININ_CD         NUMBER(4),
    SAIKENSHIN_SHISHIN              NUMBER(8),
    SAIKENSHIN_SHISHIN_L            NUMBER(3),
    SAIKENSHIN_SHIYO_NISSU_JOSUI    NUMBER(3),
    SAIKENSHIN_SHIYO_NISSU_GESUI    NUMBER(3),
    SAIKENSHIN_JOSUI_SUIRYO         NUMBER(8),
    SAIKENSHIN_JOSUI_KIKAN          NUMBER(2),
    SAIKENSHIN_JOSUI_CHOTEIGAKU     NUMBER(10),
    SAIKENSHIN_JOSUI_ZEI            NUMBER(10),
    SAIKENSHIN_GESUI_SUIRYO         NUMBER(8),
    SAIKENSHIN_NINTEI_SUIRYO        NUMBER(8),
    SAIKENSHIN_GESUI_KIKAN          NUMBER(2),
    SAIKENSHIN_GESUI_CHOTEIGAKU     NUMBER(10),
    SAIKENSHIN_GESUI_ZEI            NUMBER(10),
    SAIKENSHIN_NOHAI_SUIRYO         NUMBER(8),
    SAIKENSHIN_NOHAI_KIKAN          NUMBER(2),
    SAIKENSHIN_NOHAI_CHOTEIGAKU     NUMBER(10),
    SAIKENSHIN_NOHAI_ZEI            NUMBER(10),
    SAIKENSHIN_KAIHEI_TESURYO       NUMBER(10),
    SAIKENSHIN_GOKEI                NUMBER(10),
    SAIKENSHIN_NINTEI_CODE          NUMBER(2),
    SAIKENSHIN_CHOSA_CD             NUMBER(1),
    SAIKENSHIN_CHOSA_RIYU_CD        NUMBER(2),
    SAIKENSHIN_CHOSA_TAIO_KBN       NUMBER(1),
    SAIKENSHIN_SAIKEN_CD            NUMBER(1),
    SAIKENSHIN_SAIKEN_RIYU_CD       NUMBER(2),
    SAIKENSHIN_ROSUI_UMU            NUMBER(1),
    SAIKENSHIN_PILOT_KAITEN         NUMBER(1),
    SAIKENSHIN_ZAITAKU_KAKUNIN      NUMBER(1),
    SAIKENSHIN_TENKYO_KAKUNIN       NUMBER(1),
    YAKUSOKU_YMD                    NUMBER(8),
    LAST_IDOSYA_CD                  NUMBER(4),
    LAST_UPDATE_DATE                DATE,
    SAIKENSHIN_MEMO                 NVARCHAR2(200),
    MEMO_UPDATE_DATE                DATE,
    RYOSUIKI_SHISHIN                NUMBER(8),
    RYOSUIKI_SHISHIN_L              NUMBER(3),
    CREDIT_RENKETSU_ID              NVARCHAR2(20),
    CREDIT_HOJO_NO                  NVARCHAR2(20),
    KENSHIN_HEAD_ID                 NUMBER(38),
    LINE_NO                         NUMBER(6),
    MOB_SAGYO_MEISAI_ID             NUMBER(38),
    FILE_ID                         NUMBER(38),
    PILOT_KAITEN                    NUMBER(1),
    PILOT_DETAIL                    NUMBER(2),
    IMPUT_KOKEI                     NUMBER(3),
    RYO_METERRYO_KINGAKU            NUMBER(10),
    RYO_METERRYO_ZEI                NUMBER(10),
    KENSHIN_METERRYO_CHOTEIGAKU     NUMBER(10),
    KENSHIN_METERRYO_ZEI            NUMBER(10),
    SAIKENSHIN_METERRYO_CHOTEIGAKU  NUMBER(10),
    SAIKENSHIN_METERRYO_ZEI         NUMBER(10),
    ZENKI_MINO_FLG                  NUMBER(1),
    JOTAI_KBN                       NUMBER(1),
    INU_KBN                         NUMBER(1),
    KO_METER_CNT                    NUMBER(4),
    TAISYO_KI                       NVARCHAR2(10),
    TAISYO_GETSUBUN                 NVARCHAR2(10),
    RYO_TAISYO_KI                   NVARCHAR2(10),
    RYO_TAISYO_GETSUBUN             NVARCHAR2(10),
    RYO_JOSUI_YOTO                  NUMBER(2),
    RYO_GESUI_YOTO                  NUMBER(2),
    RYO_METER_KOKEI                 NUMBER(3),
    RYO_KOSU                        NUMBER(4),
    GYOTAI_CD                       NUMBER(3),
    MIMAMORI_TAISYO_FLG             NUMBER(1),
    MIMAMORI_KEKKA_KBN              NUMBER(2),
    SAIKAISEN_FLG                   NUMBER(1), 
    CONSTRAINT IFW_MOBF_KENSHIN_DATA_PK PRIMARY KEY(ID) 
    CONSTRAINT IFW_MOBF_KENSHIN_DAT_COL1_FK01 FOREIGN KEY(KENSHIN_HEAD_ID) REFERENCES IFW_MOBF_KENSHIN_HEAD (ID) , 
    CONSTRAINT IFW_MOBF_KENSHIN_DAT_COL1_FK02 FOREIGN KEY(MOB_SAGYO_MEISAI_ID) REFERENCES IFW_MOB_SAGYO_MEISAI (ID) ON DELETE SET NULL , 
    CONSTRAINT IFW_MOBF_KENSHIN_DAT_COL1_FK03 FOREIGN KEY(FILE_ID) REFERENCES IFW_MOB_FILE (ID) 
) 

MaximG
Devart Team
Posts: 1245
Joined: Mon 06 Jul 2015 11:34

Re: How to improve performance of OraLoader.

Post by MaximG » Tue 22 Jan 2019 15:38

Please specify the speed of record insert in the Oracle table you managed to gain as well as the performance measure you consider acceptable. Please also provide us with the following information on the data source:

- The number of rows that the SQLite table contains;
- Is the table linked to any visual component during the data loading?
- Do you use the UniDirectional property when working with the table? (https://www.devart.com/litedac/docs/dev ... tional.htm)

Post Reply