KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > h2 > test > db > TestSpeed


1 /*
2  * Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html).
3  * Initial Developer: H2 Group
4  */
5 package org.h2.test.db;
6
7 import java.sql.Connection JavaDoc;
8 import java.sql.PreparedStatement JavaDoc;
9 import java.sql.Statement JavaDoc;
10
11 import org.h2.test.TestBase;
12
13 /**
14  * @author Thomas
15  */
16
17 public class TestSpeed extends TestBase {
18
19     // java -cp .;..\..\hsqldb\lib\hsqldb.jar -Xrunhprof:heap=sites,depth=6 org.h2.test.TestAll
20 // java -Xrunhprof:heap=sites org.h2.test.TestAll
21 // java -Xprof org.h2.test.TestAll
22
23     // TODO test: here is more code, currently untested!
24
25     public void test() throws Exception JavaDoc {
26         
27         deleteDb("speed");
28         Connection JavaDoc conn;
29
30         conn = getConnection("speed");
31
32 // conn = getConnection("speed;ASSERT=0;MAX_MEMORY_ROWS=1000000;MAX_LOG_SIZE=1000");
33
34 // Class.forName("org.hsqldb.jdbcDriver");
35 // conn = DriverManager.getConnection("jdbc:hsqldb:speed");
36
37         Statement JavaDoc stat = conn.createStatement();
38         stat.execute("DROP TABLE IF EXISTS TEST");
39         stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
40         int len = getSize(1, 10000);
41         for(int i=0; i<len; i++) {
42             stat.execute("SELECT ID, NAME FROM TEST ORDER BY ID");
43         }
44         
45 //drop table if exists test;
46 //CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
47 //@LOOP 100000 INSERT INTO TEST VALUES(?, 'Hello');
48 //@LOOP 100000 SELECT * FROM TEST WHERE ID = ?;
49
50         
51 // stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
52 // for(int i=0; i<1000; i++) {
53 // stat.execute("INSERT INTO TEST VALUES("+i+", 'Hello')");
54 // }
55 // stat.execute("CREATE TABLE TESTA(ID INT PRIMARY KEY, NAME VARCHAR(255))");
56 // stat.execute("INSERT INTO TESTA VALUES(0, 'Hello')");
57 long time = System.currentTimeMillis();
58 // for(int i=1; i<8000; i*=2) {
59 // stat.execute("INSERT INTO TESTA SELECT ID+"+i+", NAME FROM TESTA");
60 //
61 //// stat.execute("INSERT INTO TESTA VALUES("+i+", 'Hello')");
62 // }
63 // for(int i=0; i<4; i++) {
64 // ResultSet rs = stat.executeQuery("SELECT * FROM TESTA");
65 // while(rs.next()) {
66 // rs.getInt(1);
67 // rs.getString(2);
68 // }
69 // }
70 // System.out.println(System.currentTimeMillis()-time);
71
72 //
73 // stat.execute("CREATE TABLE TESTB(ID INT PRIMARY KEY, NAME VARCHAR(255))");
74 // for(int i=0; i<80000; i++) {
75 // stat.execute("INSERT INTO TESTB VALUES("+i+", 'Hello')");
76 // }
77
78         // conn.close();
79 // System.exit(0);
80 // int testParser;
81 // java -Xrunhprof:cpu=samples,depth=8 -cp . org.h2.test.TestAll
82 //
83 // stat.execute("CREATE TABLE TEST(ID INT)");
84 // stat.execute("INSERT INTO TEST VALUES(1)");
85 // ResultSet rs = stat.executeQuery("SELECT ID OTHERID FROM TEST");
86 // rs.next();
87 // rs.getString("ID");
88 // stat.execute("DROP TABLE TEST");
89
90 // long time = System.currentTimeMillis();
91
92         stat.execute("DROP TABLE IF EXISTS TEST");
93         stat.execute("CREATE CACHED TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
94         PreparedStatement JavaDoc prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
95
96         int max = getSize(1, 10000);
97         for(int i=0; i<max; i++) {
98             prep.setInt(1, i);
99             prep.setString(2, "Helloasdfaldsjflajdflajdslfoajlskdfkjasdfadsfasdfadsfadfsalksdjflasjflajsdlkfjaksdjflkskd" + i);
100             //prep.setString(2, "Helloasdfaldsjflajdflajdslfoajlskdfkjasdfadsfasdfadsfadfsalksdjflasjflajsdlkfjaksdjflkskd");
101 prep.execute();
102 // System.out.println("i="+i);
103 // stat.execute("INSERT INTO TEST VALUES("+i+", 'Helloasdfaldsjflajdflajdslfaajlskdfkjasdfadsfasdfadsfadfsalksdjflasjflajsdlkfjaksdjflkskd"+i+"')");
104 // ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE ID="+i);
105 // if(!rs.next()) {
106 // throw new Error("hey! i="+i);
107 // }
108 // if(rs.next()) {
109 // throw new Error("hey! i="+i);
110 // }
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()-Runtime.getRuntime().freeMemory())/1024);
125 // }
126
127 // conn.close();
128
129         time = System.currentTimeMillis();
130
131         prep = conn.prepareStatement("UPDATE TEST SET NAME='Another data row which is long' WHERE ID=?");
132         for(int i=0; i<max; i++) {
133             prep.setInt(1, i);
134             prep.execute();
135
136 // System.out.println("updated "+i);
137 // stat.execute("UPDATE TEST SET NAME='Another data row which is long' WHERE ID="+i);
138 // ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE ID="+i);
139 // if(!rs.next()) {
140 // throw new Error("hey! i="+i);
141 // }
142 // if(rs.next()) {
143 // throw new Error("hey! i="+i);
144 // }
145 }
146 // for(int i=0; i<max; i++) {
147 // stat.execute("DELETE FROM TEST WHERE ID="+i);
148 // ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE ID="+i);
149 // if(rs.next()) {
150 // throw new Error("hey!");
151 // }
152 // }
153
154         time = System.currentTimeMillis() - time;
155         trace(time+" update");
156
157         conn.close();
158         time = System.currentTimeMillis() - time;
159         trace(time+" close");
160     }
161
162 // private void testOuterJoin() throws Exception {
163 // Class.forName("org.h2.jdbc.jdbcDriver");
164 // Connection conn = DriverManager.getConnection("jdbc:h2:test");
165
166 // Class.forName("org.hsqldb.jdbcDriver");
167 // Connection conn = DriverManager.getConnection("jdbc:hsqldb:test");
168 // Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:.");
169
170 // Statement stat = conn.createStatement();
171 //
172 // int len = getSize(1, 10000);
173
174         // create table test(id int primary key, name varchar(255))
175 // insert into test values(1, 'b')
176 // insert into test values(2, 'c')
177 // insert into test values(3, 'a')
178 // select * from test order by name desc
179 // select min(id)+max(id) from test
180 // select abs(-1), id from test order by name desc
181
182         // select id from test group by id
183
184 // long start = System.currentTimeMillis();
185 //
186 // stat.executeUpdate("DROP TABLE IF EXISTS TEST");
187 // stat.executeUpdate("CREATE TABLE Test(" + "Id INTEGER PRIMARY KEY, "
188 // + "FirstName VARCHAR(20), " + "Name VARCHAR(50), "
189 // + "ZIP INTEGER)");
190 //
191 //
192 // stat.execute("create table a(a1 varchar(1), a2 int)");
193 // stat.execute("create table b(b1 varchar(1), b2 int)");
194 // stat.execute("insert into a values(null, 12)");
195 // stat.execute("insert into a values('a', 22)");
196 // stat.execute("insert into a values('b', 32)");
197 // stat.execute("insert into b values(null, 14)");
198 // stat.execute("insert into b values('a', 14)");
199 // stat.execute("insert into b values('c', 15)");
200
201 // create table a(a1 varchar(1), a2 int);
202 // create table b(b1 varchar(1), b2 int);
203 // insert into a values(null, 12);
204 // insert into a values('a', 22);
205 // insert into a values('b', 32);
206 // insert into b values(null, 14);
207 // insert into b values('a', 14);
208 // insert into b values('c', 15);
209
210
211         //query(stat, "select * from a left outer join b on a.a1=b.b1");
212
213         // should be 3 rows
214 // query(stat, "select * from a left outer join b on ((a.a1=b.b1) or (a.a1 is null and b.b1 is null))");
215 // A1 A2 B1 B2
216 // null 12 null 14
217 // a 22 a 14
218 // b 32 null null
219
220         // should be 3 rows
221 // query(stat, "select * from a left outer join b on ((a.a1=b.b1) or (a.a1 is null and b.b1 is null))");
222 // A1 A2 B1 B2
223 // 12 14
224 // a 22 a 14
225 // b 32
226
227         // should be 2 rows
228 // query(stat, "select * from a left outer join b on (1=1) where ((a.a1=b.b1) or (a.a1 is null and b.b1 is null))");
229 // A1 A2 B1 B2
230 // 12 14
231 // a 22 a 14
232
233         // should be 1 row
234 // query(stat, "select * from a left outer join b on (1=1) where a.a1=b.b1");
235
236         // should be 3 rows
237 // query(stat, "select * from a left outer join b on a.a1=b.b1 where (1=1)");
238
239 // if(true) return;
240
241
242
243
244         //query(stat, "SELECT T1.ID, T2.ID FROM TEST T1, TEST T2 WHERE T1.ID > T2.ID");
245
246 // PreparedStatement prep;
247 //
248 // prep = conn
249 // .prepareStatement("INSERT INTO Test VALUES(?,'Julia','Peterson-Clancy',?)");
250
251         //query(stat, "SELECT * FROM TEST WHERE NAME LIKE 'Ju%'");
252
253
254
255 // long time = System.currentTimeMillis();
256 //
257 // for (int i = 0; i < len; i++) {
258 // prep.setInt(1, i);
259 // prep.setInt(2, i);
260 // prep.execute();
261 //query(stat, "SELECT * FROM TEST");
262 //if(i % 2 == 0) {
263 // stat.executeUpdate("INSERT INTO Test VALUES("+i+",'Julia','Peterson-Clancy',"+i+")");
264 //} else {
265 // stat.executeUpdate("INSERT INTO TEST VALUES("+i+",'Julia','Peterson-Clancy',"+i+")");
266 //}
267 // }
268
269 // query(stat, "SELECT ABS(-1) FROM TEST");
270
271 // conn.close();
272 // if(true) return;
273
274 // stat.executeUpdate("UPDATE Test SET Name='Hans' WHERE Id=1");
275 //query(stat, "SELECT * FROM Test WHERE Id=1");
276 //stat.executeUpdate("DELETE FROM Test WHERE Id=1");
277
278         //query(stat, "SELECT * FROM TEST");
279
280 // conn.close();
281 //
282 // if(true) {
283 // return;
284 // }
285
286         // query(stat, "SELECT * FROM TEST WHERE ID = 182");
287 /*
288         for(int i=0; i<len; i++) {
289             query(stat, "SELECT * FROM TEST WHERE ID = "+i);
290         }
291         */
292
293
294 // System.out.println("insert=" + (System.currentTimeMillis() - time));
295
296         // conn.setAutoCommit(false);
297 // prep = conn.prepareStatement("UPDATE Test SET FirstName='Hans' WHERE Id=?");
298 //
299 // time = System.currentTimeMillis();
300 //
301 // for (int i = 0; i < len; i++) {
302 // prep.setInt(1, i);
303 // if(i%10 == 0) {
304 // System.out.println(i+" ");
305 // }
306 // prep.execute();
307
308 // stat.executeUpdate("UPDATE Test SET FirstName='Hans' WHERE Id="+i);
309
310             // if(i==5) conn.close();
311 //query(stat, "SELECT * FROM TEST");
312 // }
313 // conn.rollback();
314
315 // System.out.println("update=" + (System.currentTimeMillis() - time));
316 //
317 // prep = conn.prepareStatement("SELECT * FROM Test WHERE Id=?");
318 //
319 // time = System.currentTimeMillis();
320 //
321 // for (int i = 0; i < len; i++) {
322 // prep.setInt(1, i);
323 // prep.execute();
324 //// stat.executeQuery("SELECT * FROM Test WHERE Id="+i);
325 // }
326
327 // System.out.println("select=" + (System.currentTimeMillis() - time));
328
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
342 // System.out.println("delete=" + (System.currentTimeMillis() - time));
343
344 // System.out.println("total=" + (System.currentTimeMillis() - start));
345 // stat.executeUpdate("DROP TABLE Test");
346 //
347 // conn.close();
348 /*
349          * stat.executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE
350          * DATE)"); stat.executeUpdate("INSERT INTO TEST VALUES(1, DATE
351          * '2004-12-19')"); stat.executeUpdate("INSERT INTO TEST VALUES(2, DATE
352          * '2004-12-20')"); query(stat, "SELECT * FROM TEST WHERE VALUE > DATE
353          * '2004-12-19'");
354          */
355         /*
356          * stat.executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE
357          * BINARY(10))"); stat.executeUpdate("INSERT INTO TEST VALUES(1,
358          * X'0011')"); stat.executeUpdate("INSERT INTO TEST VALUES(2,
359          * X'01FFAA')"); query(stat, "SELECT * FROM TEST WHERE VALUE >
360          * X'0011'");
361          */
362         /*
363          * stat.executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME
364          * VARCHAR(255))"); stat.executeUpdate("INSERT INTO TEST VALUES(1,
365          * 'Hallo')"); stat.executeUpdate("INSERT INTO TEST VALUES(2,
366          * 'World')");
367          */
368         /*
369          * stat.executeUpdate("CREATE UNIQUE INDEX TEST_NAME ON TEST(NAME)");
370          * stat.executeUpdate("DROP INDEX TEST_NAME");
371          * stat.executeUpdate("INSERT INTO TEST VALUES(2, 'Hallo')");
372          * stat.executeUpdate("DELETE FROM TEST"); for(int i=0; i <100; i++) {
373          * stat.executeUpdate("INSERT INTO TEST VALUES("+i+", 'Test"+i+"')"); }
374          */
375         /*
376          * query(stat, "SELECT T1.ID, T1.NAME FROM TEST T1"); query(stat,
377          * "SELECT T1.ID, T1.NAME, T2.ID, T2.NAME FROM TEST T1, TEST T2");
378          * query(stat, "SELECT T1.ID, T1.NAME, T2.ID, T2.NAME FROM TEST T1, TEST
379          * T2 WHERE T1.ID = T2.ID");
380          */
381         /*
382          * query(stat, "SELECT * FROM TEST WHERE ID = 1");
383          * stat.executeUpdate("DELETE FROM TEST WHERE ID = 2"); query(stat,
384          * "SELECT * FROM TEST WHERE ID < 10"); query(stat, "SELECT * FROM TEST
385          * WHERE ID = 2"); stat.executeUpdate("UPDATE TEST SET NAME = 'World'
386          * WHERE ID = 5"); query(stat, "SELECT * FROM TEST WHERE ID = 5");
387          * query(stat, "SELECT * FROM TEST WHERE ID < 10");
388          */
389 // }
390
391 // private static void query(Statement stat, String sql) throws SQLException {
392 // System.out.println("--------- " + sql);
393 // ResultSet rs = stat.executeQuery(sql);
394 // ResultSetMetaData meta = rs.getMetaData();
395 // while (rs.next()) {
396 // for (int i = 0; i < meta.getColumnCount(); i++) {
397 // System.out.print("[" + meta.getColumnLabel(i + 1) + "]="
398 // + rs.getString(i + 1) + " ");
399 // }
400 // System.out.println();
401 // }
402 // }
403
404 }
405
Popular Tags