title: How to improve MySQL performance Insert / update on duplicate for below Table ,Statement and JDBC code
body: <p>Here is my Table creation text:</p>
  
  <pre><code>CREATE TABLE `DailyReporting` (
      `ID` bigint(20) NOT NULL AUTO_INCREMENT,
      `QT_ST_CD` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `CNSM_MT_CD` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `CHANNEL` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `ORGN_QT_DT_ET` date NULL DEFAULT NULL,
      `PRTNR_CD` BIGINT(15) DEFAULT NULL,
      `SALE_DT` date DEFAULT NULL,
      `QT_START` int(5) DEFAULT NULL,
      `NP6` int(5) DEFAULT NULL,
      `APPS` int(5) DEFAULT NULL,
      `CreatedDate` timestamp DEFAULT CURRENT_TIMESTAMP,
      `LastUpdatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`ID`),
      UNIQUE KEY `uk_dailyReporting` (`ORGN_QT_DT_ET`,`PRTNR_CD`)
  )ENGINE=InnoDB AUTO_INCREMENT=23203 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  </code></pre>
  
  <p>My Insert / update statment is like as follows:</p>
  
  <pre><code>sql = "INSERT INTO "+tableName+
                      " (QT_ST_CD, CNSM_MT_CD,CHANNEL,ORGN_QT_DT_ET,PRTNR_CD,SALE_DT,QT_START,NP6,APPS) VALUES (?, ?, ?,?,?,?,?,?,?)"+
                      " ON DUPLICATE KEY UPDATE QT_ST_CD=VALUES(QT_ST_CD),CNSM_MT_CD=VALUES(CNSM_MT_CD),CHANNEL=VALUES(CHANNEL),SALE_DT=VALUES(SALE_DT),QT_START=VALUES(QT_START),NP6=VALUES(NP6),APPS=VALUES(APPS)";
              preparedStatement=connection.prepareStatement(sql);
              for (int i = 0; i &lt; dailyReports.size(); i++) {
                  DailyReportVO dailRprt = dailyReports.get(i);
  //              if (counter &lt; batchSize) {
                      preparedStatement.setString(1, dailRprt.getQT_ST_CD());
                      preparedStatement.setString(2, dailRprt.getCNSM_MT_CD());
                      preparedStatement.setString(3, dailRprt.getCHANNEL());
                      java.util.Date utilDate = dailRprt.getORGN_QT_DT_ET();
                      java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
                      preparedStatement.setDate(4, sqlDate);
                      preparedStatement.setLong(5, dailRprt.getPRTNR_CD());
                      java.util.Date utilDate2 = dailRprt.getSALE_DT();
                      if (utilDate2 != null) {
                          java.sql.Date sqlDate2 = new java.sql.Date(utilDate2.getTime());
                          preparedStatement.setDate(6, (Date) sqlDate2);
                      } else {
                          preparedStatement.setDate(6, null);
                      }
                      preparedStatement.setInt(7, dailRprt.getQT_START());
                      preparedStatement.setInt(8, dailRprt.getNP6());
                      preparedStatement.setInt(9, dailRprt.getAPPS());
  //                  preparedStatement.addBatch();
                      boolean result=preparedStatement.execute();
                      counter++;
                      totalcount++;
  
              }
  </code></pre>
  
  <p>With current MySQL insert/update performance it is taking 20 mins for 6000 records. Where as I need to import more than 150k records per day.</p>
  
  <p>Please share your opinion on my existing code or on statments.</p>