Performance comparison of mysql batch operation

  • 1, When operating the database, using the prepared statement will greatly improve the performance advantages of operating the database as follows:

1). Readability and maintainability of the code. (use placeholders to represent parameters?)

2) can maximize performance (precompile), MySQL does not support performance optimization of PreparedStatement

3) ensure safety

  • 2, Precompiled and efficient embodiment (database server sent by sql)

Security Analysis > syntax analysis > syntax compilation > select execution > return result set (result)

Precompiled pool: first, judge whether the sql statement sent to DBMS exists in the precompiled pool. If it exists, select the execution steps. If it does not exist, execute it according to the above procedure, and store the sql in the precompiled pool (Note: Oracle supports precompiling, mysql does not support it)

3, mysql example

  

// Batch not used
	//InnoDB:15369ms
	//MyISAM:8464ms
	@Test
	public void testSaveByStatement() throws Exception {
		
		Connection conn = JdbcUtil.getConnection();
		Statement st = conn.createStatement();
		long begin = System.currentTimeMillis();
		for (int j = 1; j < 5000; j++) {
			String sql = "insert into t_student (name,age) values('tom',"+ j+")";
			st.executeUpdate(sql);
		}
		long end = System.currentTimeMillis();
		System.out.println(end - begin);
		JdbcUtil.realse(null, st, conn);
	}
	
	// Complete with batch
	//InnoDB:14641ms
	//MyISAM:7464ms
	@Test
	public void testBatchSaveByStatement() throws Exception {
		
		Connection conn = JdbcUtil.getConnection();
		Statement st = conn.createStatement();
		long begin = System.currentTimeMillis();
		for (int j = 1; j < 5000; j++) {
			String sql = "insert into t_student(name,age) values('tom',"+ j+")";
			st.addBatch(sql);  // Add to batch
			if(j % 200 == 0){
				st.executeBatch(); // implement
				st.clearBatch();  // Clear batch
			}
		}
		long end = System.currentTimeMillis();
		System.out.println(end - begin);
		JdbcUtil.realse(null, st, conn);
	}
	
	// Batch not used
	//InnoDB:13630
	//MyISAM:12323
	@Test
	public void testSaveByPrepareStatement() throws Exception {
		String sql = "insert into t_student(name,age) values('tom',?)";
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =  conn.prepareStatement(sql);
		long begin = System.currentTimeMillis();
		for (int j = 1; j < 5000; j++) {
			ps.setInt(1, j);
			ps.executeUpdate();
			ps.clearParameters();
		}
		long end = System.currentTimeMillis();
		System.out.println(end - begin);
		JdbcUtil.realse(null, ps, conn);
	}
	
	// Complete with batch
	//InnoDB:11118
	//MyISAM:4546
	@Test
	public void testBatchSaveByByPrepareStatement() throws Exception {

		String sql = "insert into t_student(name,age) values('tom',?)";
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =  conn.prepareStatement(sql);
		long begin = System.currentTimeMillis();
		for (int j = 1; j < 5000; j++) {
			ps.setInt(1, j);
			ps.addBatch();
			if(j % 200 == 0){
				ps.executeBatch();
				ps.clearBatch();
			}
		}
		long end = System.currentTimeMillis();
		System.out.println(end - begin);
		JdbcUtil.realse(null, ps, conn);
	}

 

Tags: SQL Database MySQL Oracle

Posted on Fri, 31 Jan 2020 14:07:15 -0500 by leebo