Re: Fwd: HSQL/MySQL query performance

---   HSQLDB (server)   ---
[Server@169e11]: [Thread[main,5,main]]: setSilent(true)
2013-12-19 09:26:43,310 [main] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - Checkpoint start
2013-12-19 09:26:43,310 [main] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - checkpointClose start
2013-12-19 09:26:43,323 [main] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - checkpointClose end
2013-12-19 09:26:43,325 [main] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - Checkpoint end - txts: 1
[Server@169e11]: Initiating startup sequence...
[Server@169e11]: Server socket opened successfully in 9 ms.
[Server@169e11]: Database [index=0, id=0, db=file:db/testdb2, alias=name] opened sucessfully in 0 ms.
[Server@169e11]: Startup sequence completed in 10 ms.
[Server@169e11]: 2013-12-19 09:26:43.347 HSQLDB server 2.3.0 is online on port 1234
[Server@169e11]: To close normally, connect and execute SHUTDOWN SQL
[Server@169e11]: From command line, use [Ctrl]+[C] to abort abruptly
Init: 738
GetConnection: 139
Drop: 1
Create: 1
2013-12-19 09:26:44,050 [HSQLDB Connection @199f91c] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - dataFileCache open start
2013-12-19 09:26:44,066 [HSQLDB Connection @199f91c] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - dataFileCache open end
Insert: 847
Select #1: 486
Select #2: 397
Select #3: 388
Select #4: 419
Select #5: 372
Select #6: 372
Select #7: 375
Select #8: 415
Select #9: 380
Select #10: 379
2013-12-19 09:26:48,420 [main] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - dataFileCache commit start
2013-12-19 09:26:48,602 [main] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - dataFileCache commit end
2013-12-19 09:26:48,617 [main] INFO  hsqldb.db.HSQLDB4309F6C2C4.ENGINE - Database closed
[Server@169e11]: Initiating shutdown sequence...
[Server@169e11]: Shutdown sequence completed in 0 ms.
[Server@169e11]: 2013-12-19 09:26:48.718 SHUTDOWN : System.exit() was not called
Close: 306
--- HSQLDB (in-process) ---
[Server@111a3a4]: [Thread[main,5,main]]: setSilent(true)
2013-12-19 09:26:48,751 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - Checkpoint start
2013-12-19 09:26:48,751 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - checkpointClose start
2013-12-19 09:26:48,758 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - checkpointClose end
2013-12-19 09:26:48,758 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - Checkpoint end - txts: 1
[Server@111a3a4]: Initiating startup sequence...
[Server@111a3a4]: Server socket opened successfully in 3 ms.
[Server@111a3a4]: Database [index=0, id=1, db=file:db/testdb2, alias=name] opened sucessfully in 0 ms.
[Server@111a3a4]: Startup sequence completed in 3 ms.
[Server@111a3a4]: 2013-12-19 09:26:48.763 HSQLDB server 2.3.0 is online on port 1234
[Server@111a3a4]: To close normally, connect and execute SHUTDOWN SQL
[Server@111a3a4]: From command line, use [Ctrl]+[C] to abort abruptly
Init: 141
GetConnection: 0
Drop: 0
Create: 1
2013-12-19 09:26:48,924 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - dataFileCache open start
2013-12-19 09:26:48,926 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - dataFileCache open end
Insert: 301
Select #1: 7
Select #2: 6
Select #3: 6
Select #4: 6
Select #5: 6
Select #6: 6
Select #7: 6
Select #8: 6
Select #9: 6
Select #10: 6
2013-12-19 09:26:49,284 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - dataFileCache commit start
2013-12-19 09:26:49,506 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - dataFileCache commit end
2013-12-19 09:26:49,517 [main] INFO  hsqldb.db.HSQLDB4309F6D82C.ENGINE - Database closed
[Server@111a3a4]: Initiating shutdown sequence...
[Server@111a3a4]: Shutdown sequence completed in 0 ms.
[Server@111a3a4]: 2013-12-19 09:26:49.619 SHUTDOWN : System.exit() was not called
Close: 396
---        MySQL        ---
Init: 0
GetConnection: 200
Drop: 7
Create: 2
Insert: 4255
Select #1: 201
Select #2: 200
Select #3: 199
Select #4: 202
Select #5: 202
Select #6: 200
Select #7: 203
Select #8: 199
Select #9: 199
Select #10: 202
Close: 2

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(HsqlDatabaseProperties.hsqldb_cache_rows, 1*1024*1024);
			props.setProperty(HsqlDatabaseProperties.hsqldb_cache_size, 1*1024*1024);
			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, int mode) throws SQLException {
		long time = System.currentTimeMillis();
		Connection result;
		if (hsql) {
			if (mode == 0) {
				result = DriverManager.getConnection("jdbc:hsqldb:hsql://127.0.0.1:1234/name", user, password);
			} else {
				result = DriverManager.getConnection("jdbc:hsqldb:file:db/testdb2", 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 mode, int count, int selects) throws Exception {
		init(hsql);

		connection = getConnection(hsql, mode);
		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 (server)   ---");
		run(true, 0, count, selects);
		System.out.println("--- HSQLDB (in-process) ---");
		run(true, 1, count, selects);
		System.out.println("---        MySQL        ---");
		run(false, 0, count, selects);
	}

}