Fwd: HSQL/MySQL query performance

--- HSQLDB ---
[Server@1855af5]: [Thread[main,5,main]]: setSilent(true)
2013-12-18 11:08:00,528 [main] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - Checkpoint start
2013-12-18 11:08:00,528 [main] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - checkpointClose start
2013-12-18 11:08:00,541 [main] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - checkpointClose end
2013-12-18 11:08:00,542 [main] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - Checkpoint end - txts: 1
[Server@1855af5]: Initiating startup sequence...
[Server@1855af5]: Server socket opened successfully in 8 ms.
[Server@1855af5]: Database [index=0, id=0, db=file:db/testdb2, alias=name] opened sucessfully in 0 ms.
[Server@1855af5]: Startup sequence completed in 10 ms.
[Server@1855af5]: 2013-12-18 11:08:00.564 HSQLDB server 2.3.0 is online on port 1234
[Server@1855af5]: To close normally, connect and execute SHUTDOWN SQL
[Server@1855af5]: From command line, use [Ctrl]+[C] to abort abruptly
Init: 718
GetConnection: 130
Drop: 1
Create: 1
2013-12-18 11:08:01,268 [HSQLDB Connection @27391d] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - dataFileCache open start
2013-12-18 11:08:01,284 [HSQLDB Connection @27391d] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - dataFileCache open end
Insert: 965
Select #1: 674
Select #2: 576
Select #3: 583
Select #4: 572
Select #5: 600
Select #6: 570
Select #7: 603
Select #8: 566
Select #9: 583
Select #10: 585
2013-12-18 11:08:07,691 [main] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - dataFileCache commit start
2013-12-18 11:08:07,773 [main] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - dataFileCache commit end
2013-12-18 11:08:07,786 [main] INFO  hsqldb.db.HSQLDB43052D21E7.ENGINE - Database closed
[Server@1855af5]: Initiating shutdown sequence...
[Server@1855af5]: Shutdown sequence completed in 0 ms.
[Server@1855af5]: 2013-12-18 11:08:07.888 SHUTDOWN : System.exit() was not called
Close: 221
--- MySQL  ---
Init: 1
GetConnection: 172
Drop: 8
Create: 3
Insert: 3669
Select #1: 183
Select #2: 194
Select #3: 200
Select #4: 195
Select #5: 204
Select #6: 195
Select #7: 253
Select #8: 195
Select #9: 195
Select #10: 197
Close: 1

package dbtest;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.hsqldb.Database;
import org.hsqldb.DatabaseManager;
import org.hsqldb.DatabaseURL;
import org.hsqldb.Server;
import org.hsqldb.jdbcDriver;
import org.hsqldb.persist.HsqlDatabaseProperties;
import org.hsqldb.persist.HsqlProperties;

public class DBPerformanceTester {
	
	private static Server server;
	
	private static String user = "user";
	private static String password = "user";
	
	private static Connection connection;
	private static Statement statement;
	
	private static String[] data;
	
	private static void prepareRandomData(int count) {
		data = new String[count];
		StringBuilder builder = new StringBuilder();
		for (int i = 0; i < count; i++) {
			builder.setLength(0);
			for (int c = 0; c < 255; c++) {
				char ch = (char) ('A' + (Math.random()*26));
				builder.append(ch);
			}
			data[i] = builder.toString();
		}
	}
	
	private static void cleanupHSQLDBFiles() {
		new File("db").mkdir();
		new File("db/testdb2.data").delete();
		new File("db/testdb2.log").delete();
		new File("db/testdb2.properties").delete();
		new File("db/testdb2.script").delete();
	}

	private static void init(boolean hsql) throws Exception {
		long time = System.currentTimeMillis();
		if (hsql) {
			Class.forName(jdbcDriver.class.getCanonicalName());
			
			cleanupHSQLDBFiles();
			
			server = new Server();
			
			server.setSilent(true);
			server.setNoSystemExit(true);
			server.setPort(1234);
			server.setDatabasePath(0, "file:db/testdb2");
			server.setDatabaseName(0, "name");
			
			HsqlProperties props = new HsqlProperties();
			props.setProperty(HsqlDatabaseProperties.hsqldb_tx, "MVCC");
			props.setProperty(HsqlDatabaseProperties.hsqldb_tx_level, "READ_COMMITTED");
			props.setProperty(HsqlDatabaseProperties.hsqldb_tx_conflict_rollback, true);
			props.setProperty(HsqlDatabaseProperties.hsqldb_default_table_type, "CACHED");
			props.setProperty("user", user);
			props.setProperty("password", password);
			
			DatabaseManager.getDatabase(DatabaseURL.S_FILE, "db/testdb2", server, props);
			
			server.start();
		} else {
			Class.forName("com.mysql.jdbc.Driver");
		}
		time = System.currentTimeMillis() - time;
		System.out.println("Init: " + time);
	}
	
	private static Connection getConnection(boolean hsql) throws SQLException {
		long time = System.currentTimeMillis();
		Connection result;
		if (hsql) {
			result = DriverManager.getConnection("jdbc:hsqldb:hsql://127.0.0.1:1234/name", user, password);
		} else {
			result = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", user, password);
		}
		time = System.currentTimeMillis() - time;
		System.out.println("GetConnection: " + time);
		return result;
	}
	
	private static void drop() throws Exception {
		long time = System.currentTimeMillis();
		statement.execute("DROP TABLE IF EXISTS Test");
		time = System.currentTimeMillis() - time;
		System.out.println("Drop: " + time);
	}
	
	private static void create(boolean hsql) throws Exception {
		long time = System.currentTimeMillis();
		String create;
		if (hsql) {
			create = "CREATE TABLE Test(Id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, t1 VARCHAR(255), t2 VARCHAR(255), t3 VARCHAR(255), t4 VARCHAR(255))";
		} else {
			create = "CREATE TABLE Test(Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, t1 VARCHAR(255), t2 VARCHAR(255), t3 VARCHAR(255), t4 VARCHAR(255))";
		}
		statement.execute(create);
		time = System.currentTimeMillis() - time;
		System.out.println("Create: " + time);
	}
	
	private static void insert(int count) throws Exception {
		long time = System.currentTimeMillis();
		connection.setAutoCommit(false);
		PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO Test (t1,t2,t3,t4) VALUES (?, ?, ?, ?)");
		for (int i = 0; i < count; i++) {
			preparedStatement.setString(1, data[i]);
			preparedStatement.setString(2, data[i]);
			preparedStatement.setString(3, data[i]);
			preparedStatement.setString(4, data[i]);
			preparedStatement.addBatch();
		}
		preparedStatement.executeBatch();
		connection.commit();
		connection.setAutoCommit(true);
		time = System.currentTimeMillis() - time;
		System.out.println("Insert: " + time);
	}
	
	private static void select(int selects) throws Exception {
		for(int r = 0; r < selects; r++) {
			long time = System.currentTimeMillis();
			
			ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM Test");
			while (rs.next());
			rs.close();

			time = System.currentTimeMillis() - time;
			System.out.println("Select #" + (r+1) + ": " + time);
		}
	}
	
	private static void close(boolean hsql) throws Exception {
		long time = System.currentTimeMillis();
		if (!connection.getAutoCommit()) {
			connection.commit();
		}
		connection.close();
		
		if (hsql) {
			if (server != null && server.getState() == 1) {
				server.shutdownWithCatalogs(Database.CLOSEMODE_NORMAL);
				server.stop();
				server = null;
			}
		}
		time = System.currentTimeMillis() - time;
		System.out.println("Close: " + time);
	}

	private static void run(boolean hsql, int count, int selects) throws Exception {
		init(hsql);

		connection = getConnection(hsql);
		statement = connection.createStatement();

		drop();

		create(hsql);

		insert(count);

		select(selects);

		close(hsql);
	}
	
	public static void main(String[] args) throws Exception {
		int count = 20000;
		int selects = 10;
		
		prepareRandomData(count);
		
		System.out.println("--- HSQLDB ---");
		run(true, count, selects);
		System.out.println("--- MySQL  ---");
		run(false, count, selects);
	}

}