{"id":6626,"date":"2018-02-05T13:49:51","date_gmt":"2018-02-05T18:49:51","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=6626"},"modified":"2018-02-05T16:13:40","modified_gmt":"2018-02-05T21:13:40","slug":"improving-batch-inserts-into-mysql","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2018\/02\/05\/improving-batch-inserts-into-mysql\/","title":{"rendered":"Improving batch inserts into MySQL"},"content":{"rendered":"<p>We had a custom class to copy data from Oracle to MySQL at AWS.  It was sending each statement individually, even though we batched them.<\/p>\n<pre>\r\nimport java.sql.*;\r\n \r\npublic class test {\r\n \r\n  public static void main(String args[]) throws Exception {\r\n    Class.forName(\"com.mysql.jdbc.Driver\");\r\n    Class.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n    Connection connOra = DriverManager.getConnection(\"jdbc:oracle:thin:dbuser\/******@host:1521\/servicename\");\r\n    Connection conn = DriverManager.getConnection(\"jdbc:mysql:\/\/host.amazonaws.com\/dbname?user=dbuser&password=*********\");\r\n    Statement stm = connOra.createStatement();\r\n    stm.setFetchSize(1000);\r\n    ResultSet rst = connOra.createStatement().executeQuery(\"select * from tab\");\r\n    PreparedStatement pst = conn.prepareStatement(\"insert into tab col1,col2,col3,col4) values (?,?,?,?)\");\r\n    ResultSetMetaData rsmd = rst.getMetaData();\r\n    int j = 0;\r\n    while (rst.next()) {\r\n      for (int i = 1; i <= rsmd.getColumnCount(); i++) {\r\n        if (rsmd.getColumnTypeName(i).equals(\"DATE\"))\r\n          pst.setDate(i,rst.getDate(i));\r\n        if (rsmd.getColumnTypeName(i).equals(\"TIMESTAMP\"))\r\n          pst.setTimestamp(i,rst.getTimestamp(i));\r\n        if (rsmd.getColumnTypeName(i).equals(\"NUMBER\"))\r\n          pst.setLong(i,rst.getLong(i));\r\n        if (rsmd.getColumnTypeName(i).equals(\"VARCHAR2\"))\r\n          pst.setString(i,rst.getString(i));\r\n      }\r\n      pst.addBatch();\r\n      if (++j % 1000 == 0) {\r\n        System.out.println(\"Starting executeBatch at \" + new java.util.Date());\r\n        pst.executeBatch();\r\n        System.out.println(\"Completed executeBatch at \" + new java.util.Date());\r\n      }\r\n    }\r\n    pst.executeBatch();\r\n  }\r\n}\r\n<\/pre>\n<p>As soon as we added rewriteBatchedStatements=true to the JDBC URL for MySQL, the result was a 100 fold reduction in runtime.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We had a custom class to copy data from Oracle to MySQL at AWS. It was sending each statement individually, even though we batched them. import java.sql.*; public class test { public static void main(String args[]) throws Exception { Class.forName(&#8220;com.mysql.jdbc.Driver&#8221;);&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2018\/02\/05\/improving-batch-inserts-into-mysql\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,77,23],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6626"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=6626"}],"version-history":[{"count":3,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6626\/revisions"}],"predecessor-version":[{"id":6630,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6626\/revisions\/6630"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=6626"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=6626"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=6626"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}