MYSQL BULK INSERT OPTIMIZATION &&CHINESE GARBLED CODE

because the reading efficiency of type longtext in mysql is not charming,I have to turn to another solution ,which is split the coordinates data and store them in slave table.but there’s another problem ,each coordinate store as one data,there will be ten million data,here’s the code before optimization…

    conn = new DBConnection().getConn();
    pst = conn.prepareStatement(stringBuffer.toString());
    pst.setTimestamp(++paramCount, timestamp);
    pst.addBatch();
    int[] result = pst.executeBatch();

each loop there will be 2MB data,which is nearly 50 thousand data, to be inserted,the speed is not charming either…nearly 6 minute…it’s painful waiting for the file to be parsed.

then I looked up on Internet,there’s lot of way to improve…
1.TokuDB I found this first,but it’s not very suitable for me.though the insertion will be faster,but it seems means the compromising on read speed.
http://blog.jobbole.com/109104/
2.then I found these blog,it’s very helpful…
first there’s some varibles of mysql you need to setup,here’s what I set:
innodb_flush_log_at_trx_commit =0
bulk_insert_buffer_size = 128M
Max_allowed_packet = 64M
if my.ini does’t contain these varibles ,just add them in the end will be ok.
after setting and restarted the mysql service.
you need change your code…

StringBuilder sb = new StringBuilder();
    sb.append(SQL_REPLACE_MAP_INFO_CB);
    for (int i = 0; i < coordinates.size(); i++) {
        try {
            Map<String, Object> coordinate = coordinates.get(i);
            sb.append("('" + coordinate.get("fk_id").toString() + "',"
                    + Integer.parseInt(coordinate.get("seq").toString()) + ","
                    + BigDecimal.valueOf(Double.parseDouble(coordinate.get("lat").toString())) + ","
                    + BigDecimal.valueOf(Double.parseDouble(coordinate.get("lng").toString())) + ","
                    + BigDecimal.valueOf(Double.parseDouble(coordinate.get("zh").toString())) + ","
                    + BigDecimal.valueOf(Double.parseDouble(coordinate.get("relativeDis").toString())) + ",'"
                    + timestamp + "')");
            if (i != coordinates.size() - 1) {
                sb.append(",");
            }
        } catch (Exception e) {
            e.printStackTrace();
            continue;
        }
    }
    pst = conn.prepareStatement(sb.toString());
    int result = pst.executeUpdate();

the difference is using insert into table () values (),(),(),(),()…()
instead of insert into table () values (?,?,?).
after optimization,same 2MB data ,I only need few seconds!!!
https://blog.csdn.net/u013488847/article/details/53819976
https://blog.csdn.net/u011277123/article/details/61914773

then I ran into chinese grabled code problem…then I checked code…

		@RequestMapping(value = "/mapAnalyse.json", produces = "text/html;charset=UTF-8")
		InputStreamReader reader = new InputStreamReader(multipartFile.getInputStream(), "UTF-8");

then I realized there’s one thing I haven’t check,that is the data resource…
when I get the request from jsp.I need to make sure jsp was encoded in UTF-8,in the same way,when I read data from a file,I need to make sure that the file was coded in UTF-8…then I found the file I uploaded is coded in ANSI…after I saved it in UTF-8 ,the problem solved…