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.Statement;
011:
012: import org.h2.test.TestBase;
013:
014: /**
015: * Various small performance tests.
016: */
017: public class TestSpeed extends TestBase {
018:
019: // java -cp .;..\..\hsqldb\lib\hsqldb.jar -Xrunhprof:heap=sites,depth=6
020: // org.h2.test.TestAll
021: // java -Xrunhprof:heap=sites org.h2.test.TestAll
022:
023: // TODO test: here is more code, currently untested!
024:
025: public void test() throws Exception {
026:
027: deleteDb("speed");
028: Connection conn;
029:
030: conn = getConnection("speed");
031:
032: // conn =
033: // getConnection("speed;ASSERT=0;MAX_MEMORY_ROWS=1000000;MAX_LOG_SIZE=1000");
034:
035: // Class.forName("org.hsqldb.jdbcDriver");
036: // conn = DriverManager.getConnection("jdbc:hsqldb:speed");
037:
038: Statement stat = conn.createStatement();
039: stat.execute("DROP TABLE IF EXISTS TEST");
040: stat
041: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
042: int len = getSize(1, 10000);
043: for (int i = 0; i < len; i++) {
044: stat.execute("SELECT ID, NAME FROM TEST ORDER BY ID");
045: }
046:
047: // drop table if exists test;
048: // CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
049: // @LOOP 100000 INSERT INTO TEST VALUES(?, 'Hello');
050: // @LOOP 100000 SELECT * FROM TEST WHERE ID = ?;
051:
052: // stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME
053: // VARCHAR(255))");
054: // for(int i=0; i<1000; i++) {
055: // stat.execute("INSERT INTO TEST VALUES("+i+", 'Hello')");
056: // }
057: // stat.execute("CREATE TABLE TEST_A(ID INT PRIMARY KEY, NAME
058: // VARCHAR(255))");
059: // stat.execute("INSERT INTO TEST_A VALUES(0, 'Hello')");
060: long time = System.currentTimeMillis();
061: // for(int i=1; i<8000; i*=2) {
062: // stat.execute("INSERT INTO TEST_A SELECT ID+"+i+", NAME FROM TEST_A");
063: //
064: // // stat.execute("INSERT INTO TEST_A VALUES("+i+", 'Hello')");
065: // }
066: // for(int i=0; i<4; i++) {
067: // ResultSet rs = stat.executeQuery("SELECT * FROM TEST_A");
068: // while(rs.next()) {
069: // rs.getInt(1);
070: // rs.getString(2);
071: // }
072: // }
073: // System.out.println(System.currentTimeMillis()-time);
074:
075: //
076: // stat.execute("CREATE TABLE TEST_B(ID INT PRIMARY KEY, NAME
077: // VARCHAR(255))");
078: // for(int i=0; i<80000; i++) {
079: // stat.execute("INSERT INTO TEST_B VALUES("+i+", 'Hello')");
080: // }
081:
082: // conn.close();
083: // System.exit(0);
084: // int testParser;
085: // java -Xrunhprof:cpu=samples,depth=8 -cp . org.h2.test.TestAll
086: //
087: // stat.execute("CREATE TABLE TEST(ID INT)");
088: // stat.execute("INSERT INTO TEST VALUES(1)");
089: // ResultSet rs = stat.executeQuery("SELECT ID OTHER_ID FROM TEST");
090: // rs.next();
091: // rs.getString("ID");
092: // stat.execute("DROP TABLE TEST");
093:
094: // long time = System.currentTimeMillis();
095:
096: stat.execute("DROP TABLE IF EXISTS TEST");
097: stat
098: .execute("CREATE CACHED TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
099: PreparedStatement prep = conn
100: .prepareStatement("INSERT INTO TEST VALUES(?, ?)");
101:
102: int max = getSize(1, 10000);
103: for (int i = 0; i < max; i++) {
104: prep.setInt(1, i);
105: prep
106: .setString(
107: 2,
108: "abchelloasdfaldsjflajdflajdslfoajlskdfkjasdfadsfasdfadsfadfsalksdjflasjflajsdlkfjaksdjflkskd"
109: + i);
110: prep.execute();
111: }
112:
113: // System.exit(0);
114: // System.out.println("END "+Value.cacheHit+" "+Value.cacheMiss);
115:
116: time = System.currentTimeMillis() - time;
117: trace(time + " insert");
118:
119: // if(true) return;
120:
121: // if(config.log) {
122: // System.gc();
123: // System.gc();
124: // log("mem="+(Runtime.getRuntime().totalMemory() -
125: // Runtime.getRuntime().freeMemory())/1024);
126: // }
127:
128: // conn.close();
129:
130: time = System.currentTimeMillis();
131:
132: prep = conn
133: .prepareStatement("UPDATE TEST SET NAME='Another data row which is long' WHERE ID=?");
134: for (int i = 0; i < max; i++) {
135: prep.setInt(1, i);
136: prep.execute();
137:
138: // System.out.println("updated "+i);
139: // stat.execute("UPDATE TEST SET NAME='Another data row which is
140: // long' WHERE ID="+i);
141: // ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE
142: // ID="+i);
143: // if(!rs.next()) {
144: // throw new Error("hey! i="+i);
145: // }
146: // if(rs.next()) {
147: // throw new Error("hey! i="+i);
148: // }
149: }
150: // for(int i=0; i<max; i++) {
151: // stat.execute("DELETE FROM TEST WHERE ID="+i);
152: // ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE ID="+i);
153: // if(rs.next()) {
154: // throw new Error("hey!");
155: // }
156: // }
157:
158: time = System.currentTimeMillis() - time;
159: trace(time + " update");
160:
161: conn.close();
162: time = System.currentTimeMillis() - time;
163: trace(time + " close");
164: }
165:
166: // private void testOuterJoin() throws Exception {
167: // Class.forName("org.h2.jdbc.jdbcDriver");
168: // Connection conn = DriverManager.getConnection("jdbc:h2:test");
169:
170: // Class.forName("org.hsqldb.jdbcDriver");
171: // Connection conn = DriverManager.getConnection("jdbc:hsqldb:test");
172: // Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:.");
173:
174: // Statement stat = conn.createStatement();
175: //
176: // int len = getSize(1, 10000);
177:
178: // create table test(id int primary key, name varchar(255))
179: // insert into test values(1, 'b')
180: // insert into test values(2, 'c')
181: // insert into test values(3, 'a')
182: // select * from test order by name desc
183: // select min(id)+max(id) from test
184: // select abs(-1), id from test order by name desc
185:
186: // select id from test group by id
187:
188: // long start = System.currentTimeMillis();
189: //
190: // stat.executeUpdate("DROP TABLE IF EXISTS TEST");
191: // stat.executeUpdate("CREATE TABLE Test(" + "Id INTEGER PRIMARY KEY, "
192: // + "FirstName VARCHAR(20), " + "Name VARCHAR(50), "
193: // + "ZIP INTEGER)");
194: //
195: //
196: // stat.execute("create table a(a1 varchar(1), a2 int)");
197: // stat.execute("create table b(b1 varchar(1), b2 int)");
198: // stat.execute("insert into a values(null, 12)");
199: // stat.execute("insert into a values('a', 22)");
200: // stat.execute("insert into a values('b', 32)");
201: // stat.execute("insert into b values(null, 14)");
202: // stat.execute("insert into b values('a', 14)");
203: // stat.execute("insert into b values('c', 15)");
204:
205: // create table a(a1 varchar(1), a2 int);
206: // create table b(b1 varchar(1), b2 int);
207: // insert into a values(null, 12);
208: // insert into a values('a', 22);
209: // insert into a values('b', 32);
210: // insert into b values(null, 14);
211: // insert into b values('a', 14);
212: // insert into b values('c', 15);
213:
214: // query(stat, "select * from a left outer join b on a.a1=b.b1");
215:
216: // should be 3 rows
217: // query(stat, "select * from a left outer join b on ((a.a1=b.b1) or (a.a1
218: // is null and b.b1 is null))");
219: // A1 A2 B1 B2
220: // null 12 null 14
221: // a 22 a 14
222: // b 32 null null
223:
224: // should be 3 rows
225: // query(stat, "select * from a left outer join b on ((a.a1=b.b1) or (a.a1
226: // is null and b.b1 is null))");
227: // A1 A2 B1 B2
228: // 12 14
229: // a 22 a 14
230: // b 32
231:
232: // should be 2 rows
233: // query(stat, "select * from a left outer join b on (1=1) where
234: // ((a.a1=b.b1) or (a.a1 is null and b.b1 is null))");
235: // A1 A2 B1 B2
236: // 12 14
237: // a 22 a 14
238:
239: // should be 1 row
240: // query(stat, "select * from a left outer join b on (1=1) where
241: // a.a1=b.b1");
242:
243: // should be 3 rows
244: // query(stat, "select * from a left outer join b on a.a1=b.b1 where
245: // (1=1)");
246:
247: // if(true) return;
248:
249: // query(stat, "SELECT T1.ID, T2.ID FROM TEST T1, TEST T2 WHERE T1.ID >
250: // T2.ID");
251:
252: // PreparedStatement prep;
253: //
254: // prep = conn
255: // .prepareStatement("INSERT INTO Test
256: // VALUES(?,'Julia','Peterson-Clancy',?)");
257:
258: // query(stat, "SELECT * FROM TEST WHERE NAME LIKE 'Ju%'");
259:
260: // long time = System.currentTimeMillis();
261: //
262: // for (int i = 0; i < len; i++) {
263: // prep.setInt(1, i);
264: // prep.setInt(2, i);
265: // prep.execute();
266: // query(stat, "SELECT * FROM TEST");
267: // if(i % 2 == 0) {
268: // stat.executeUpdate("INSERT INTO Test
269: // VALUES("+i+",'Julia','Peterson-Clancy',"+i+")");
270: // } else {
271: // stat.executeUpdate("INSERT INTO TEST
272: // VALUES("+i+",'Julia','Peterson-Clancy',"+i+")");
273: // }
274: // }
275:
276: // query(stat, "SELECT ABS(-1) FROM TEST");
277:
278: // conn.close();
279: // if(true) return;
280:
281: // stat.executeUpdate("UPDATE Test SET Name='Hans' WHERE Id=1");
282: // query(stat, "SELECT * FROM Test WHERE Id=1");
283: // stat.executeUpdate("DELETE FROM Test WHERE Id=1");
284:
285: // query(stat, "SELECT * FROM TEST");
286:
287: // conn.close();
288: //
289: // if(true) {
290: // return;
291: // }
292:
293: // query(stat, "SELECT * FROM TEST WHERE ID = 182");
294: /*
295: * for(int i=0; i<len; i++) { query(stat, "SELECT * FROM TEST WHERE ID =
296: * "+i); }
297: */
298:
299: // System.out.println("insert=" + (System.currentTimeMillis() - time));
300: // conn.setAutoCommit(false);
301: // prep = conn.prepareStatement("UPDATE Test SET FirstName='Hans' WHERE
302: // Id=?");
303: //
304: // time = System.currentTimeMillis();
305: //
306: // for (int i = 0; i < len; i++) {
307: // prep.setInt(1, i);
308: // if(i%10 == 0) {
309: // System.out.println(i+" ");
310: // }
311: // prep.execute();
312: // stat.executeUpdate("UPDATE Test SET FirstName='Hans' WHERE Id="+i);
313: // if(i==5) conn.close();
314: // query(stat, "SELECT * FROM TEST");
315: // }
316: // conn.rollback();
317: // System.out.println("update=" + (System.currentTimeMillis() - time));
318: //
319: // prep = conn.prepareStatement("SELECT * FROM Test WHERE Id=?");
320: //
321: // time = System.currentTimeMillis();
322: //
323: // for (int i = 0; i < len; i++) {
324: // prep.setInt(1, i);
325: // prep.execute();
326: // // stat.executeQuery("SELECT * FROM Test WHERE Id="+i);
327: // }
328: // System.out.println("select=" + (System.currentTimeMillis() - time));
329: // query(stat, "SELECT * FROM TEST");
330: // prep = conn.prepareStatement("DELETE FROM Test WHERE Id=?");
331: //
332: // time = System.currentTimeMillis();
333: //
334: // for (int i = 0; i < len; i++) {
335: // // stat.executeUpdate("DELETE FROM Test WHERE Id="+i);
336: // prep.setInt(1, i);
337: // //System.out.println("delete "+i);
338: // prep.execute();
339: // // query(stat, "SELECT * FROM TEST");
340: // }
341: // System.out.println("delete=" + (System.currentTimeMillis() - time));
342: // System.out.println("total=" + (System.currentTimeMillis() - start));
343: // stat.executeUpdate("DROP TABLE Test");
344: //
345: // conn.close();
346: /*
347: * stat.executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE DATE)");
348: * stat.executeUpdate("INSERT INTO TEST VALUES(1, DATE '2004-12-19')");
349: * stat.executeUpdate("INSERT INTO TEST VALUES(2, DATE '2004-12-20')");
350: * query(stat, "SELECT * FROM TEST WHERE VALUE > DATE '2004-12-19'");
351: */
352: /*
353: * stat.executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE
354: * BINARY(10))"); stat.executeUpdate("INSERT INTO TEST VALUES(1, X'0011')");
355: * stat.executeUpdate("INSERT INTO TEST VALUES(2, X'01FFAA')"); query(stat,
356: * "SELECT * FROM TEST WHERE VALUE > X'0011'");
357: */
358: /*
359: * stat.executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME
360: * VARCHAR(255))"); stat.executeUpdate("INSERT INTO TEST VALUES(1,
361: * 'Hallo')"); stat.executeUpdate("INSERT INTO TEST VALUES(2, 'World')");
362: */
363: /*
364: * stat.executeUpdate("CREATE UNIQUE INDEX TEST_NAME ON TEST(NAME)");
365: * stat.executeUpdate("DROP INDEX TEST_NAME"); stat.executeUpdate("INSERT
366: * INTO TEST VALUES(2, 'Hallo')"); stat.executeUpdate("DELETE FROM TEST");
367: * for(int i=0; i <100; i++) { stat.executeUpdate("INSERT INTO TEST
368: * VALUES("+i+", 'Test"+i+"')"); }
369: */
370: /*
371: * query(stat, "SELECT T1.ID, T1.NAME FROM TEST T1"); query(stat, "SELECT
372: * T1.ID, T1.NAME, T2.ID, T2.NAME FROM TEST T1, TEST T2"); query(stat,
373: * "SELECT T1.ID, T1.NAME, T2.ID, T2.NAME FROM TEST T1, TEST T2 WHERE T1.ID =
374: * T2.ID");
375: */
376: /*
377: * query(stat, "SELECT * FROM TEST WHERE ID = 1");
378: * stat.executeUpdate("DELETE FROM TEST WHERE ID = 2"); query(stat, "SELECT *
379: * FROM TEST WHERE ID < 10"); query(stat, "SELECT * FROM TEST WHERE ID =
380: * 2"); stat.executeUpdate("UPDATE TEST SET NAME = 'World' WHERE ID = 5");
381: * query(stat, "SELECT * FROM TEST WHERE ID = 5"); query(stat, "SELECT *
382: * FROM TEST WHERE ID < 10");
383: */
384: // }
385: // private static void query(Statement stat, String sql) throws SQLException
386: // {
387: // System.out.println("--------- " + sql);
388: // ResultSet rs = stat.executeQuery(sql);
389: // ResultSetMetaData meta = rs.getMetaData();
390: // while (rs.next()) {
391: // for (int i = 0; i < meta.getColumnCount(); i++) {
392: // System.out.print("[" + meta.getColumnLabel(i + 1) + "]="
393: // + rs.getString(i + 1) + " ");
394: // }
395: // System.out.println();
396: // }
397: // }
398: }
|