How to improve performance of OraLoader.
Posted: 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.
The table has more than 300 columns.
Are there any ideas to improve the performance?
Thanks in advance.
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;
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)
)