2

I am working on one migration script using Java. I want to migrate data from SQL Server to Mysql. I am using the below code to migrate 1 table but the performance is very slow. It is taking approx 30 minutes to migrate 100 000 records.

private void pushIntoMySqlServer_EmailSubscription(ResultSet rs) {
    try {
        log.info("Updating Data into MYsql DB For Table:  ich_emailsubscription");
        PreparedStatement pst = mySqlCon.prepareStatement(
                "INSERT INTO ich_emailsubscription(SubscriptionId, SubscriptionTime, Email, SubscriberName, Suburb, "
                        + " State, PostCode, url, device) " + "VALUES(?,?,?,?,?,?,?,?,?)"
                        + "ON DUPLICATE KEY UPDATE " + "SubscriptionTime = VALUES(SubscriptionTime),"
                        + "Email = VALUES(Email)," + "SubscriberName = VALUES(SubscriberName),"
                        + "Suburb = VALUES(Suburb)," + "State = VALUES(State)," + "PostCode = VALUES(PostCode),"
                        + "url = VALUES(url)," + "device = VALUES(device)");
        int i = 1;
        int records = 0;
        while (rs.next()) {
            pst.setInt(1, rs.getInt(1));
            pst.setDate(2, rs.getDate(2));
            pst.setString(3, rs.getString(3));
            pst.setString(4, rs.getString(4));
            pst.setString(5, rs.getString(5));
            pst.setString(6, rs.getString(6));
            pst.setString(7, rs.getString(7));
            pst.setString(8, rs.getString(8));
            pst.setString(9, rs.getString(9));
            pst.addBatch();
            if (i % 10000 == 0) {
                pst.clearParameters();
                records += pst.executeBatch().length;
            }
            i++;
        }
        records += pst.executeBatch().length;
        log.info("Data Updated For Table :  ich_emailsubscription : " + records);
        saveLastFetchTime(600, records, 0);
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println(e);
    }
}
James Z
  • 12,209
  • 10
  • 24
  • 44
learner_me
  • 91
  • 1
  • 10

0 Answers0