how to improve insert/loading performance
In our application, we try to insert records into Vertica using JDBC. there are about 40 columns in the table.
But the performance is really poor. It took 55 seconds to insert 1500 records. Are there any tips or workaround to improve the insert performance?
the following is the table definition and the insert sqls:
CREATE TABLE xsdb.dws.xxxxx (
REQUEST_ID Varchar(100),
SRC_LASTMODDATE Timestamp,
USER_DATA_01 Varchar(800),
USER_DATA_02 Varchar(800),
USER_DATA_03 Varchar(800),
USER_DATA_04 Varchar(800),
USER_DATA_05 Varchar(800),
USER_DATA_06 Varchar(800),
USER_DATA_07 Varchar(800),
USER_DATA_08 Varchar(800),
USER_DATA_09 Varchar(800),
USER_DATA_10 Varchar(800),
USER_DATA_11 Varchar(800),
USER_DATA_12 Varchar(800),
USER_DATA_13 Varchar(800),
USER_DATA_14 Varchar(800),
USER_DATA_15 Varchar(800),
USER_DATA_16 Varchar(800),
USER_DATA_17 Varchar(800),
USER_DATA_18 Varchar(800),
USER_DATA_19 Varchar(800),
USER_DATA_20 Varchar(800),
VISIBLE_USER_DATA_01 Varchar(800),
VISIBLE_USER_DATA_02 Varchar(800),
VISIBLE_USER_DATA_03 Varchar(800),
VISIBLE_USER_DATA_04 Varchar(800),
VISIBLE_USER_DATA_05 Varchar(800),
VISIBLE_USER_DATA_06 Varchar(800),
VISIBLE_USER_DATA_07 Varchar(800),
VISIBLE_USER_DATA_08 Varchar(800),
VISIBLE_USER_DATA_09 Varchar(800),
VISIBLE_USER_DATA_10 Varchar(800),
VISIBLE_USER_DATA_11 Varchar(800),
VISIBLE_USER_DATA_12 Varchar(800),
VISIBLE_USER_DATA_13 Varchar(800),
VISIBLE_USER_DATA_14 Varchar(800),
VISIBLE_USER_DATA_15 Varchar(800),
VISIBLE_USER_DATA_16 Varchar(800),
VISIBLE_USER_DATA_17 Varchar(800),
VISIBLE_USER_DATA_18 Varchar(800),
VISIBLE_USER_DATA_19 Varchar(800),
VISIBLE_USER_DATA_20 Varchar(800)
);
INSERT INTO dws.xxxxx VALUES('0','2005.02.01 12:00:01','dummy_1306','dummy_720','dummy_717','dummy_713','dummy_516','dummy_1209','dummy_388','dummy_250','dummy_1248','dummy_577','dummy_859','dummy_500','dummy_100','dummy_404','dummy_73','dummy_612','dummy_1096','dummy_1061','dummy_692','dummy_1439','dummy_536','dummy_1240','dummy_446','dummy_85','dummy_217','dummy_771','dummy_296','dummy_55','dummy_838','dummy_445','dummy_134','dummy_1497','dummy_957','dummy_1457','dummy_570','dummy_1060','dummy_1341','dummy_1423','dummy_888','dummy_140');
INSERT INTO dws.xxxxx VALUES('1','2005.02.01 12:00:02','dummy_57','dummy_1005','dummy_204','dummy_498','dummy_280','dummy_1271','dummy_368','dummy_1260','dummy_1029','dummy_460','dummy_1002','dummy_256','dummy_213','dummy_104','dummy_379','dummy_1426','dummy_1022','dummy_915','dummy_1495','dummy_964','dummy_770','dummy_1101','dummy_849','dummy_140','dummy_1095','dummy_85','dummy_1430','dummy_1013','dummy_816','dummy_907','dummy_286','dummy_412','dummy_1449','dummy_1451','dummy_484','dummy_305','dummy_780','dummy_168','dummy_1499','dummy_163');
INSERT INTO dws.xxxxx VALUES('2','2005.02.01 12:00:03','dummy_510','dummy_176','dummy_1310','dummy_404','dummy_65','dummy_906','dummy_152','dummy_925','dummy_1023','dummy_402','dummy_1366','dummy_108','dummy_450','dummy_1040','dummy_170','dummy_200','dummy_1349','dummy_749','dummy_1244','dummy_136','dummy_470','dummy_652','dummy_536','dummy_74','dummy_641','dummy_585','dummy_827','dummy_1116','dummy_1410','dummy_879','dummy_160','dummy_436','dummy_1298','dummy_435','dummy_170','dummy_365','dummy_470','dummy_560','dummy_80','dummy_295');