private void testPerformance3d() throws SQLException {
deleteDb("multiDimension");
Connection conn;
conn = getConnection("multiDimension");
Statement stat = conn.createStatement();
stat.execute("CREATE ALIAS MAP FOR \"" + getClass().getName() + ".interleave\"");
stat.execute("CREATE TABLE TEST(X INT NOT NULL, Y INT NOT NULL, Z INT NOT NULL, " +
"XYZ BIGINT AS MAP(X, Y, Z), DATA VARCHAR)");
stat.execute("CREATE INDEX IDX_X ON TEST(X, Y, Z)");
stat.execute("CREATE INDEX IDX_XYZ ON TEST(XYZ)");
PreparedStatement prep = conn.prepareStatement(
"INSERT INTO TEST(X, Y, Z, DATA) VALUES(?, ?, ?, ?)");
// the MultiDimension tool is faster for 8000 (20^3) points
// the more the bigger the difference
int max = getSize(10, 20);
long time = System.currentTimeMillis();
for (int x = 0; x < max; x++) {
for (int y = 0; y < max; y++) {
for (int z = 0; z < max; z++) {
long t2 = System.currentTimeMillis();
if (t2 - time > 1000) {
int percent = (int) (100.0 * ((double) x * max + y) / ((double) max * max));
trace(percent + "%");
time = t2;
}
prep.setInt(1, x);
prep.setInt(2, y);
prep.setInt(3, z);
prep.setString(4, "Test data");
prep.execute();
}
}
}
stat.execute("ANALYZE SAMPLE_SIZE 10000");
PreparedStatement prepRegular = conn.prepareStatement(
"SELECT * FROM TEST WHERE X BETWEEN ? AND ? " +
"AND Y BETWEEN ? AND ? AND Z BETWEEN ? AND ? ORDER BY X, Y, Z");
MultiDimension multi = MultiDimension.getInstance();
String sql = multi.generatePreparedQuery("TEST", "XYZ", new String[] { "X", "Y", "Z" });