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.ArrayList;
013: import java.util.HashMap;
014: import java.util.Random;
015: import java.util.TreeSet;
016:
017: import org.h2.test.TestBase;
018:
019: /**
020: * Test various optimizations (query cache, optimization for MIN(..), and
021: * MAX(..)).
022: */
023: public class TestOptimizations extends TestBase {
024:
025: public void test() throws Exception {
026: if (config.networked) {
027: return;
028: }
029: testMultiColumnRangeQuery();
030: testDistinctOptimization();
031: testQueryCacheTimestamp();
032: testQueryCacheSpeed();
033: testQueryCache(true);
034: testQueryCache(false);
035: testIn();
036: testMinMaxCountOptimization(true);
037: testMinMaxCountOptimization(false);
038: }
039:
040: private void testMultiColumnRangeQuery() throws Exception {
041: deleteDb("optimizations");
042: Connection conn = getConnection("optimizations");
043: Statement stat = conn.createStatement();
044: stat.execute("CREATE TABLE Logs(id INT PRIMARY KEY, type INT)");
045: stat
046: .execute("CREATE unique INDEX type_index ON Logs(type, id)");
047: stat
048: .execute("INSERT INTO Logs SELECT X, MOD(X, 3) FROM SYSTEM_RANGE(1, 1000)");
049: stat.execute("ANALYZE SAMPLE_SIZE 0");
050: ResultSet rs;
051: rs = stat
052: .executeQuery("EXPLAIN SELECT id FROM Logs WHERE id < 100 and type=2 AND id<100");
053: rs.next();
054: String plan = rs.getString(1);
055: check(plan.indexOf("TYPE_INDEX") > 0);
056: conn.close();
057: }
058:
059: private void testDistinctOptimization() throws Exception {
060: deleteDb("optimizations");
061: Connection conn = getConnection("optimizations");
062: Statement stat = conn.createStatement();
063: stat
064: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR, TYPE INT)");
065: stat.execute("CREATE INDEX IDX_TEST_TYPE ON TEST(TYPE)");
066: Random random = new Random(1);
067: int len = getSize(10000, 100000);
068: int[] groupCount = new int[10];
069: PreparedStatement prep = conn
070: .prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
071: for (int i = 0; i < len; i++) {
072: prep.setInt(1, i);
073: prep.setString(2, "Hello World");
074: int type = random.nextInt(10);
075: groupCount[type]++;
076: prep.setInt(3, type);
077: prep.execute();
078: }
079: ResultSet rs;
080: rs = stat
081: .executeQuery("SELECT TYPE, COUNT(*) FROM TEST GROUP BY TYPE ORDER BY TYPE");
082: for (int i = 0; rs.next(); i++) {
083: check(i, rs.getInt(1));
084: check(groupCount[i], rs.getInt(2));
085: }
086: checkFalse(rs.next());
087: rs = stat
088: .executeQuery("SELECT DISTINCT TYPE FROM TEST ORDER BY TYPE");
089: for (int i = 0; rs.next(); i++) {
090: check(i, rs.getInt(1));
091: }
092: checkFalse(rs.next());
093: stat.execute("ANALYZE");
094: rs = stat
095: .executeQuery("SELECT DISTINCT TYPE FROM TEST ORDER BY TYPE");
096: for (int i = 0; i < 10; i++) {
097: check(rs.next());
098: check(i, rs.getInt(1));
099: }
100: checkFalse(rs.next());
101: rs = stat
102: .executeQuery("SELECT DISTINCT TYPE FROM TEST ORDER BY TYPE LIMIT 5 OFFSET 2");
103: for (int i = 2; i < 7; i++) {
104: check(rs.next());
105: check(i, rs.getInt(1));
106: }
107: checkFalse(rs.next());
108: rs = stat
109: .executeQuery("SELECT DISTINCT TYPE FROM TEST ORDER BY TYPE LIMIT 0 OFFSET 0 SAMPLE_SIZE 3");
110: // must have at least one row
111: check(rs.next());
112: for (int i = 0; i < 3; i++) {
113: rs.getInt(1);
114: if (i > 0 && !rs.next()) {
115: break;
116: }
117: }
118: checkFalse(rs.next());
119: conn.close();
120: }
121:
122: private void testQueryCacheTimestamp() throws Exception {
123: deleteDb("optimizations");
124: Connection conn = getConnection("optimizations");
125: PreparedStatement prep = conn
126: .prepareStatement("SELECT CURRENT_TIMESTAMP()");
127: ResultSet rs = prep.executeQuery();
128: rs.next();
129: String a = rs.getString(1);
130: Thread.sleep(50);
131: rs = prep.executeQuery();
132: rs.next();
133: String b = rs.getString(1);
134: checkFalse(a.equals(b));
135: conn.close();
136: }
137:
138: private void testQueryCacheSpeed() throws Exception {
139: deleteDb("optimizations");
140: Connection conn = getConnection("optimizations");
141: Statement stat = conn.createStatement();
142: testQuerySpeed(
143: stat,
144: "select sum(x) from system_range(1, 10000) a where a.x in (select b.x from system_range(1, 30) b)");
145: testQuerySpeed(
146: stat,
147: "select sum(a.n), sum(b.x) from system_range(1, 100) b, (select sum(x) n from system_range(1, 4000)) a");
148: conn.close();
149: }
150:
151: private void testQuerySpeed(Statement stat, String sql)
152: throws Exception {
153: stat.execute("set OPTIMIZE_REUSE_RESULTS 0");
154: stat.execute(sql);
155: long time = System.currentTimeMillis();
156: stat.execute(sql);
157: time = System.currentTimeMillis() - time;
158: stat.execute("set OPTIMIZE_REUSE_RESULTS 1");
159: stat.execute(sql);
160: long time2 = System.currentTimeMillis();
161: stat.execute(sql);
162: time2 = System.currentTimeMillis() - time2;
163: if (time2 > time * 2) {
164: error("not optimized: " + time + " optimized: " + time2
165: + " sql:" + sql);
166: }
167: }
168:
169: private void testQueryCache(boolean optimize) throws Exception {
170: deleteDb("optimizations");
171: Connection conn = getConnection("optimizations");
172: Statement stat = conn.createStatement();
173: if (optimize) {
174: stat.execute("set OPTIMIZE_REUSE_RESULTS 1");
175: } else {
176: stat.execute("set OPTIMIZE_REUSE_RESULTS 0");
177: }
178: stat.execute("create table test(id int)");
179: stat.execute("create table test2(id int)");
180: stat.execute("insert into test values(1), (1), (2)");
181: stat.execute("insert into test2 values(1)");
182: PreparedStatement prep = conn
183: .prepareStatement("select * from test where id = (select id from test2)");
184: ResultSet rs1 = prep.executeQuery();
185: rs1.next();
186: check(rs1.getInt(1), 1);
187: rs1.next();
188: check(rs1.getInt(1), 1);
189: checkFalse(rs1.next());
190:
191: stat.execute("update test2 set id = 2");
192: ResultSet rs2 = prep.executeQuery();
193: rs2.next();
194: check(rs2.getInt(1), 2);
195:
196: conn.close();
197: }
198:
199: private void testMinMaxCountOptimization(boolean memory)
200: throws Exception {
201: deleteDb("optimizations");
202: Connection conn = getConnection("optimizations");
203: Statement stat = conn.createStatement();
204: stat.execute("create " + (memory ? "memory" : "")
205: + " table test(id int primary key, value int)");
206: stat.execute("create index idx_value_id on test(value, id);");
207: int len = getSize(1000, 10000);
208: HashMap map = new HashMap();
209: TreeSet set = new TreeSet();
210: Random random = new Random(1);
211: for (int i = 0; i < len; i++) {
212: if (i == len / 2) {
213: if (!config.memory) {
214: conn.close();
215: conn = getConnection("optimizations");
216: stat = conn.createStatement();
217: }
218: }
219: switch (random.nextInt(10)) {
220: case 0:
221: case 1:
222: case 2:
223: case 3:
224: case 4:
225: case 5:
226: if (random.nextInt(1000) == 1) {
227: stat.execute("insert into test values(" + i
228: + ", null)");
229: map.put(new Integer(i), null);
230: } else {
231: int value = random.nextInt();
232: stat.execute("insert into test values(" + i + ", "
233: + value + ")");
234: map.put(new Integer(i), new Integer(value));
235: set.add(new Integer(value));
236: }
237: break;
238: case 6:
239: case 7:
240: case 8: {
241: if (map.size() > 0) {
242: for (int j = random.nextInt(i), k = 0; k < 10; k++, j++) {
243: if (map.containsKey(new Integer(j))) {
244: Integer x = (Integer) map
245: .remove(new Integer(j));
246: if (x != null) {
247: set.remove(x);
248: }
249: stat.execute("delete from test where id="
250: + j);
251: }
252: }
253: }
254: break;
255: }
256: case 9: {
257: ArrayList list = new ArrayList(map.values());
258: int count = list.size();
259: Integer min = null, max = null;
260: if (count > 0) {
261: min = (Integer) set.first();
262: max = (Integer) set.last();
263: }
264: ResultSet rs = stat
265: .executeQuery("select min(value), max(value), count(*) from test");
266: rs.next();
267: Integer minDb = (Integer) rs.getObject(1);
268: Integer maxDb = (Integer) rs.getObject(2);
269: int countDb = rs.getInt(3);
270: check(minDb, min);
271: check(maxDb, max);
272: check(countDb, count);
273: }
274: }
275: }
276: conn.close();
277: }
278:
279: private void testIn() throws Exception {
280: deleteDb("optimizations");
281: Connection conn = getConnection("optimizations");
282: Statement stat = conn.createStatement();
283: stat
284: .execute("create table test(id int primary key, name varchar)");
285: stat.execute("insert into test values(1, 'Hello')");
286: stat.execute("insert into test values(2, 'World')");
287: PreparedStatement prep;
288: ResultSet rs;
289:
290: prep = conn
291: .prepareStatement("select * from test t1 where t1.id in(?)");
292: prep.setInt(1, 1);
293: rs = prep.executeQuery();
294: rs.next();
295: check(rs.getInt(1), 1);
296: check(rs.getString(2), "Hello");
297: checkFalse(rs.next());
298:
299: prep = conn
300: .prepareStatement("select * from test t1 where t1.id in(?, ?) order by id");
301: prep.setInt(1, 1);
302: prep.setInt(2, 2);
303: rs = prep.executeQuery();
304: rs.next();
305: check(rs.getInt(1), 1);
306: check(rs.getString(2), "Hello");
307: rs.next();
308: check(rs.getInt(1), 2);
309: check(rs.getString(2), "World");
310: checkFalse(rs.next());
311:
312: prep = conn
313: .prepareStatement("select * from test t1 where t1.id in(select t2.id from test t2 where t2.id=?)");
314: prep.setInt(1, 2);
315: rs = prep.executeQuery();
316: rs.next();
317: check(rs.getInt(1), 2);
318: check(rs.getString(2), "World");
319: checkFalse(rs.next());
320:
321: prep = conn
322: .prepareStatement("select * from test t1 where t1.id in(select t2.id from test t2 where t2.id=? and t1.id<>t2.id)");
323: prep.setInt(1, 2);
324: rs = prep.executeQuery();
325: checkFalse(rs.next());
326:
327: prep = conn
328: .prepareStatement("select * from test t1 where t1.id in(select t2.id from test t2 where t2.id in(cast(?+10 as varchar)))");
329: prep.setInt(1, 2);
330: rs = prep.executeQuery();
331: checkFalse(rs.next());
332:
333: conn.close();
334: }
335:
336: }
|