001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.db;
007:
008: import java.sql.Connection;
009: import java.sql.PreparedStatement;
010: import java.sql.ResultSet;
011: import java.sql.Statement;
012: import java.util.Random;
013:
014: import org.h2.test.TestBase;
015: import org.h2.tools.MultiDimension;
016:
017: /**
018: * Tests the multi-dimension index tool.
019: */
020: public class TestMultiDimension extends TestBase {
021:
022: public void test() throws Exception {
023: Random rand = new Random(10);
024: for (int i = 0; i < 1000; i++) {
025: int x = rand.nextInt(1000), y = rand.nextInt(1000), z = rand
026: .nextInt(1000);
027: MultiDimension tool = MultiDimension.getInstance();
028: long xyz = tool.interleave(new int[] { x, y, z });
029: check(x, tool.deinterleave(xyz, 3, 0));
030: check(y, tool.deinterleave(xyz, 3, 1));
031: check(z, tool.deinterleave(xyz, 3, 2));
032: }
033:
034: deleteDb("multiDimension");
035: Connection conn;
036: conn = getConnection("multiDimension");
037: Statement stat = conn.createStatement();
038: stat.execute("CREATE ALIAS MAP FOR \"" + getClass().getName()
039: + ".interleave\"");
040: stat
041: .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)");
042: stat.execute("CREATE INDEX IDX_X ON TEST(X, Y, Z)");
043: stat.execute("CREATE INDEX IDX_XYZ ON TEST(XYZ)");
044: PreparedStatement prep = conn
045: .prepareStatement("INSERT INTO TEST(X, Y, Z, DATA) VALUES(?, ?, ?, ?)");
046: // a reasonable max value to see the performance difference is 60; the
047: // higher the bigger the difference
048: int max = getSize(10, 20);
049: long time = System.currentTimeMillis();
050: for (int x = 0; x < max; x++) {
051: for (int y = 0; y < max; y++) {
052: for (int z = 0; z < max; z++) {
053: long t2 = System.currentTimeMillis();
054: if (t2 - time > 1000) {
055: int percent = (int) (100.0 * ((double) x * x * x) / ((double) max
056: * max * max));
057: trace(percent + "%");
058: time = t2;
059: try {
060: Thread.sleep(10);
061: } catch (Exception e) {
062: }
063: }
064: prep.setInt(1, x);
065: prep.setInt(2, y);
066: prep.setInt(3, z);
067: prep.setString(4, "Test data");
068: prep.execute();
069: }
070: }
071: }
072: stat.execute("ANALYZE SAMPLE_SIZE 10000");
073: PreparedStatement prepRegular = conn
074: .prepareStatement("SELECT * FROM TEST WHERE X BETWEEN ? AND ? "
075: + "AND Y BETWEEN ? AND ? AND Z BETWEEN ? AND ? ORDER BY X, Y, Z");
076: MultiDimension multi = MultiDimension.getInstance();
077: String sql = multi.generatePreparedQuery("TEST", "XYZ",
078: new String[] { "X", "Y", "Z" });
079: sql += " ORDER BY X, Y, Z";
080: PreparedStatement prepMulti = conn.prepareStatement(sql);
081: long timeMulti = 0, timeRegular = 0;
082: int timeMax = getSize(100, 2000);
083: for (int i = 0; timeMulti < timeMax; i++) {
084: int size = rand.nextInt(max / 10);
085: int minX = rand.nextInt(max - size);
086: int minY = rand.nextInt(max - size);
087: int minZ = rand.nextInt(max - size);
088: int maxX = minX + size, maxY = minY + size, maxZ = minZ
089: + size;
090: time = System.currentTimeMillis();
091: ResultSet rs1 = multi.getResult(prepMulti, new int[] {
092: minX, minY, minZ }, new int[] { maxX, maxY, maxZ });
093: timeMulti += System.currentTimeMillis() - time;
094: time = System.currentTimeMillis();
095: prepRegular.setInt(1, minX);
096: prepRegular.setInt(2, maxX);
097: prepRegular.setInt(3, minY);
098: prepRegular.setInt(4, maxY);
099: prepRegular.setInt(5, minZ);
100: prepRegular.setInt(6, maxZ);
101: ResultSet rs2 = prepRegular.executeQuery();
102: timeRegular += System.currentTimeMillis() - time;
103: while (rs1.next()) {
104: check(rs2.next());
105: check(rs1.getInt(1), rs2.getInt(1));
106: check(rs1.getInt(2), rs2.getInt(2));
107: }
108: checkFalse(rs2.next());
109: }
110: trace("multi: " + timeMulti + " regular: " + timeRegular);
111: for (int i = 0; i < 50; i++) {
112: int size = rand.nextInt(max / 10);
113: int minX = rand.nextInt(max - size);
114: int minY = rand.nextInt(max - size);
115: int minZ = rand.nextInt(max - size);
116: int maxX = minX + size, maxY = minY + size, maxZ = minZ
117: + size;
118: long time1 = System.currentTimeMillis();
119: String query1 = MultiDimension.getInstance()
120: .generateQuery(
121: "TEST",
122: "XYZ",
123: new String[] { "X", "Y", "Z" },
124: new int[] { minX, minY, minZ },
125: new int[] { minX + size, minY + size,
126: minZ + size });
127: ResultSet rs1 = conn.createStatement().executeQuery(
128: query1 + " ORDER BY X, Y, Z");
129: time1 = System.currentTimeMillis() - time1;
130: long time2 = System.currentTimeMillis();
131: String query2 = "SELECT * FROM TEST WHERE " + "X BETWEEN "
132: + minX + " AND " + maxX + " AND " + "Y BETWEEN "
133: + minY + " AND " + maxY + " AND " + "Z BETWEEN "
134: + minZ + " AND " + maxZ;
135: PreparedStatement prep2 = conn.prepareStatement(query2
136: + " ORDER BY X, Y, Z");
137: ResultSet rs2 = prep2.executeQuery();
138: time2 = System.currentTimeMillis() - time2;
139: while (rs1.next()) {
140: check(rs2.next());
141: check(rs1.getInt(1), rs2.getInt(1));
142: check(rs1.getInt(2), rs2.getInt(2));
143: }
144: checkFalse(rs2.next());
145: // it just has to work, no need to compare the performance
146: // trace("t1="+time1+" t2="+time2+" size="+size);
147: }
148: conn.close();
149: }
150:
151: public static long interleave(int x, int y, int z) {
152: return MultiDimension.getInstance().interleave(
153: new int[] { x, y, z });
154: }
155: }
|