001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.io.FileWriter;
034: import java.sql.Connection;
035: import java.sql.DriverManager;
036: import java.sql.PreparedStatement;
037: import java.sql.ResultSet;
038: import java.sql.SQLException;
039: import java.sql.Statement;
040: import java.util.Random;
041:
042: import org.hsqldb.lib.FileUtil;
043: import org.hsqldb.lib.StopWatch;
044: import org.hsqldb.persist.HsqlProperties;
045:
046: /**
047: * Test large cached tables by setting up a cached table of 100000 records
048: * or more and a much smaller memory table with about 1/100th rows used.
049: * Populate both tables so that an indexed column of the cached table has a
050: * foreign key reference to the main table.
051: *
052: * This database can be used to demonstrate efficient queries to retrieve
053: * the data from the cached table.
054: *
055: * 1.7.1 insert timings for 100000 rows, cache scale 12:
056: * simple table, no extra index: 52 s
057: * with index on lastname only: 56 s
058: * with index on zip only: 211 s
059: * foreign key, referential_integrity true: 216 s
060: *
061: * The above have improved a lot in 1.7.2
062: *
063: * This test now incorporates the defunct TestTextTables
064: *
065: * @author fredt@users
066: * @version 1.8.0
067: * @since 1.7.0
068: */
069: public class TestCacheSize {
070:
071: // program can edit the *.properties file to set cache_size, old files are deleted
072: protected boolean filedb = true;
073:
074: // shutdown performed mid operation - not for mem: or hsql: URL's
075: protected boolean shutdown = true;
076:
077: // fixed
078: protected String url = "jdbc:hsqldb:";
079:
080: // protected String filepath = "hsql://localhost/mytest";
081: // protected String filepath = "mem:test";
082: protected String filepath = "/hsql/testcache/test";
083:
084: // frequent reporting of progress
085: boolean reportProgress = false;
086:
087: // type of the big table {MEMORY | CACHED | TEXT | ""}
088: String tableType = "CACHED";
089: int cacheScale = 8;
090: int cacheSizeScale = 8;
091: boolean nioMode = false;
092:
093: // script format {TEXT | BINARY | COMPRESSED}
094: String logType = "TEXT";
095: int writeDelay = 60;
096: boolean indexZip = false;
097: boolean indexLastName = false;
098: boolean addForeignKey = false;
099: boolean refIntegrity = true;
100:
101: // may speed up inserts when tableType=="CACHED"
102: boolean createTempTable = false;
103:
104: // introduces fragmentation to the .data file during insert
105: boolean deleteWhileInsert = false;
106: int deleteWhileInsertInterval = 10000;
107:
108: // size of the tables used in test
109: int bigrows = 256000;
110:
111: // number of ops
112: int bigops = 256000;
113: int smallops = 8000;
114: int smallrows = 0xfff;
115:
116: // if the extra table needs to be created and filled up
117: boolean multikeytable = false;
118:
119: //
120: String user;
121: String password;
122: Statement sStatement;
123: Connection cConnection;
124: FileWriter writer;
125:
126: private void checkSelects() {
127:
128: countTestID();
129:
130: // selectID();
131: selectZipTable();
132: }
133:
134: private void checkUpdates() {
135:
136: updateIDLinear();
137:
138: // updateID();
139: countTestID();
140: deleteTest();
141: countTestID();
142: countZip();
143: }
144:
145: protected void setUp() {
146:
147: try {
148: writer = new FileWriter("speedtests.html", true);
149:
150: writer.write("<table>\n");
151: storeResult(new java.util.Date().toString(), 0, 0, 0);
152: storeResult(filepath + " " + tableType + " " + nioMode,
153: cacheScale, 0, 0);
154: } catch (Exception e) {
155: }
156:
157: user = "sa";
158: password = "";
159:
160: try {
161: sStatement = null;
162: cConnection = null;
163:
164: Class.forName("org.hsqldb.jdbcDriver");
165:
166: if (filedb) {
167: deleteDatabase(filepath);
168:
169: cConnection = DriverManager.getConnection(url
170: + filepath, user, password);
171: sStatement = cConnection.createStatement();
172:
173: sStatement
174: .execute("SET WRITE_DELAY " + 100 + " MILLIS");
175: sStatement.execute("SET CHECKPOINT DEFRAG " + 0);
176: sStatement.execute("SET SCRIPTFORMAT " + logType);
177: sStatement.execute("SET LOGSIZE " + 0);
178: sStatement
179: .execute("SET PROPERTY \"hsqldb.applog\" " + 1);
180: sStatement
181: .execute("SET PROPERTY \"hsqldb.cache_scale\" "
182: + cacheScale);
183: sStatement
184: .execute("SET PROPERTY \"hsqldb.cache_size_scale\" "
185: + cacheSizeScale);
186: sStatement
187: .execute("SET PROPERTY \"hsqldb.nio_data_file\" "
188: + nioMode);
189: sStatement.execute("SHUTDOWN");
190: cConnection.close();
191: }
192: } catch (Exception e) {
193: e.printStackTrace();
194: System.out.println("TestSql.setUp() error: "
195: + e.getMessage());
196: }
197: }
198:
199: /**
200: * Fill up the cache
201: *
202: *
203: */
204: public void testFillUp() {
205:
206: StopWatch sw = new StopWatch();
207: String ddl1 = "DROP TABLE test IF EXISTS";
208: String ddl11 = "DROP TABLE zip IF EXISTS";
209: String ddl2 = "CREATE TABLE zip( zip INT IDENTITY )";
210: String ddl3 = "CREATE " + tableType
211: + " TABLE test( id INT IDENTITY,"
212: + " firstname VARCHAR(20), "
213: + " lastname VARCHAR(20), " + " zip INTEGER, "
214: + " filler VARCHAR(300))";
215: String ddl31 = "SET TABLE test SOURCE \"test.csv;cache_scale="
216: + cacheScale + "\"";
217:
218: // adding extra index will slow down inserts a bit
219: String ddl4 = "CREATE INDEX idx1 ON TEST (lastname)";
220:
221: // adding this index will slow down inserts a lot
222: String ddl5 = "CREATE INDEX idx2 ON TEST (zip)";
223:
224: // referential integrity checks will slow down inserts a bit
225: String ddl6 = "ALTER TABLE test add constraint c1 FOREIGN KEY (zip) REFERENCES zip(zip) ON DELETE CASCADE;";
226: String ddl7 = "CREATE TEMP TABLE temptest( id INT,"
227: + " firstname VARCHAR, " + " lastname VARCHAR, "
228: + " zip INTEGER, " + " filler VARCHAR)";
229: String filler = "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
230: + "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ";
231: String mddl1 = "DROP TABLE test2 IF EXISTS";
232: String mddl2 = "CREATE " + tableType
233: + " TABLE test2( id1 INT, id2 INT,"
234: + " firstname VARCHAR, " + " lastname VARCHAR, "
235: + " zip INTEGER, " + " filler VARCHAR, "
236: + " PRIMARY KEY (id1,id2) )";
237: String mdd13 = "SET TABLE test2 SOURCE \"test2.csv;cache_scale="
238: + cacheScale + "\"";
239:
240: try {
241:
242: // System.out.println("Connecting");
243: sw.zero();
244:
245: cConnection = null;
246: sStatement = null;
247: cConnection = DriverManager.getConnection(url + filepath,
248: user, password);
249:
250: System.out
251: .println("connection time -- " + sw.elapsedTime());
252: sw.zero();
253:
254: sStatement = cConnection.createStatement();
255:
256: java.util.Random randomgen = new java.util.Random();
257:
258: // sStatement.execute("SET WRITE_DELAY " + writeDelay);
259: sStatement.execute(ddl1);
260: sStatement.execute(ddl2);
261: sStatement.execute(ddl3);
262:
263: if (tableType.equals("TEXT")) {
264: sStatement.execute(ddl31);
265: }
266:
267: // System.out.println("test table with no index");
268: if (indexLastName) {
269: sStatement.execute(ddl4);
270: System.out.println("created index on lastname");
271: }
272:
273: if (indexZip) {
274: sStatement.execute(ddl5);
275: System.out.println("created index on zip");
276: }
277:
278: if (addForeignKey) {
279: sStatement.execute(ddl6);
280: System.out.println("added foreign key");
281: }
282:
283: if (createTempTable) {
284: sStatement.execute(ddl7);
285: System.out.println("created temp table");
286: }
287:
288: if (multikeytable) {
289: sStatement.execute(mddl1);
290: sStatement.execute(mddl2);
291:
292: if (tableType.equals("TEXT")) {
293: sStatement.execute(mdd13);
294: }
295:
296: System.out.println("created multi key table");
297: }
298:
299: // sStatement.execute("CREATE INDEX idx3 ON tempTEST (zip);");
300: System.out.println("complete setup time -- "
301: + sw.elapsedTime() + " ms");
302: fillUpBigTable(filler, randomgen);
303:
304: if (multikeytable) {
305: fillUpMultiTable(filler, randomgen);
306: }
307:
308: sw.zero();
309:
310: if (shutdown) {
311: sStatement.execute("SHUTDOWN");
312:
313: long time = sw.elapsedTime();
314:
315: storeResult("shutdown", 0, time, 0);
316: System.out.println("shutdown time -- " + time + " ms");
317: }
318:
319: cConnection.close();
320: } catch (SQLException e) {
321: System.out.println(e.getMessage());
322: }
323: }
324:
325: private void fillUpBigTable(String filler, Random randomgen)
326: throws SQLException {
327:
328: StopWatch sw = new StopWatch();
329: int i;
330: PreparedStatement ps = cConnection
331: .prepareStatement("INSERT INTO zip VALUES(?)");
332:
333: for (i = 0; i <= smallrows; i++) {
334: ps.setInt(1, i);
335: ps.execute();
336: }
337:
338: ps.close();
339: sStatement.execute("SET REFERENTIAL_INTEGRITY "
340: + this .refIntegrity);
341:
342: ps = cConnection
343: .prepareStatement("INSERT INTO test (firstname,lastname,zip,filler) VALUES (?,?,?,?)");
344:
345: ps.setString(1, "Julia");
346: ps.setString(2, "Clancy");
347:
348: for (i = 0; i < bigrows; i++) {
349: ps.setInt(3, nextIntRandom(randomgen, smallrows));
350:
351: {
352:
353: // small rows
354: long nextrandom = randomgen.nextLong();
355: int randomlength = (int) nextrandom & 0x7f;
356:
357: if (randomlength > filler.length()) {
358: randomlength = filler.length();
359: }
360:
361: String varfiller = filler.substring(0, randomlength);
362:
363: ps.setString(4, nextrandom + varfiller);
364: }
365:
366: /*
367: {
368: // big rows
369: long nextrandom = randomgen.nextLong();
370: int randomlength = (int) nextrandom & 0x7ff;
371:
372: if (randomlength > filler.length() * 20) {
373: randomlength = filler.length() * 20;
374: }
375:
376: StringBuffer sb = new StringBuffer(0xff);
377:
378: for (int j = 0; j < 20; j++) {
379: sb.append(filler);
380: }
381:
382: String varfiller = sb.substring(0, randomlength);
383:
384: ps.setString(4, nextrandom + varfiller);
385: }
386: */
387: ps.execute();
388:
389: if (reportProgress && (i + 1) % 10000 == 0) {
390: System.out.println("insert " + (i + 1) + " : "
391: + sw.elapsedTime());
392: }
393:
394: // delete and add 4000 rows to introduce fragmentation
395: if (deleteWhileInsert && i != 0
396: && i % deleteWhileInsertInterval == 0) {
397: sStatement.execute("CALL IDENTITY();");
398:
399: ResultSet rs = sStatement.getResultSet();
400:
401: rs.next();
402:
403: int lastId = rs.getInt(1);
404:
405: sStatement
406: .execute("SELECT * INTO TEMP tempt FROM test WHERE id > "
407: + (lastId - 4000));
408: sStatement.execute("DELETE FROM test WHERE id > "
409: + (lastId - 4000));
410: sStatement
411: .execute("INSERT INTO test SELECT * FROM tempt");
412: sStatement.execute("DROP TABLE tempt");
413: }
414: }
415:
416: ps.close();
417:
418: // sStatement.execute("INSERT INTO test SELECT * FROM temptest;");
419: // sStatement.execute("DROP TABLE temptest;");
420: // sStatement.execute(ddl7);
421: long time = sw.elapsedTime();
422: long rate = ((long) i * 1000) / (time + 1);
423:
424: storeResult("insert", i, time, rate);
425: System.out.println("insert time for " + i + " rows -- " + time
426: + " ms -- " + rate + " tps");
427: }
428:
429: private void fillUpMultiTable(String filler, Random randomgen)
430: throws SQLException {
431:
432: StopWatch sw = new StopWatch();
433: int i;
434: PreparedStatement ps = cConnection
435: .prepareStatement("INSERT INTO test2 (id1, id2, firstname,lastname,zip,filler) VALUES (?,?,?,?,?,?)");
436:
437: ps.setString(3, "Julia");
438: ps.setString(4, "Clancy");
439:
440: int id1 = 0;
441:
442: for (i = 0; i < bigrows; i++) {
443: int id2 = nextIntRandom(randomgen, Integer.MAX_VALUE);
444:
445: if (i % 1000 == 0) {
446: id1 = nextIntRandom(randomgen, Integer.MAX_VALUE);
447: }
448:
449: ps.setInt(1, id1);
450: ps.setInt(2, id2);
451: ps.setInt(5, nextIntRandom(randomgen, smallrows));
452:
453: long nextrandom = randomgen.nextLong();
454: int randomlength = (int) nextrandom & 0x7f;
455:
456: if (randomlength > filler.length()) {
457: randomlength = filler.length();
458: }
459:
460: String varfiller = filler.substring(0, randomlength);
461:
462: ps.setString(6, nextrandom + varfiller);
463:
464: try {
465: ps.execute();
466: } catch (SQLException e) {
467: e.printStackTrace();
468: }
469:
470: if (reportProgress && (i + 1) % 10000 == 0) {
471: System.out.println("insert " + (i + 1) + " : "
472: + sw.elapsedTime());
473: }
474: }
475:
476: ps.close();
477: System.out.println("total multi key rows inserted: " + i);
478: System.out.println("insert time: " + sw.elapsedTime()
479: + " rps: " + (i * 1000 / (sw.elapsedTime() + 1)));
480: }
481:
482: protected void tearDown() {
483:
484: try {
485: writer.write("\n</table>\n");
486: writer.close();
487: } catch (Exception e) {
488: }
489: }
490:
491: protected void checkResults() {
492:
493: try {
494: StopWatch sw = new StopWatch();
495: ResultSet rs;
496:
497: cConnection = DriverManager.getConnection(url + filepath,
498: user, password);
499:
500: long time = sw.elapsedTime();
501:
502: storeResult("reopen", 0, time, 0);
503: System.out.println("database reopen time -- " + time
504: + " ms");
505: sw.zero();
506:
507: sStatement = cConnection.createStatement();
508:
509: // sStatement.execute("SET WRITE_DELAY " + writeDelay);
510: checkSelects();
511: checkUpdates();
512: sw.zero();
513:
514: if (shutdown) {
515: sStatement.execute("SHUTDOWN");
516:
517: time = sw.elapsedTime();
518:
519: storeResult("shutdown", 0, time, 0);
520: System.out.println("shutdown time -- " + time + " ms");
521: }
522:
523: cConnection.close();
524:
525: // System.out.println("database close time -- " + sw.elapsedTime() + " ms");
526: } catch (SQLException e) {
527: e.printStackTrace();
528: }
529: }
530:
531: void selectZip() {
532:
533: StopWatch sw = new StopWatch();
534: java.util.Random randomgen = new java.util.Random();
535: int i = 0;
536: boolean slow = false;
537:
538: try {
539: PreparedStatement ps = cConnection
540: .prepareStatement("SELECT TOP 1 firstname,lastname,zip,filler FROM test WHERE zip = ?");
541:
542: for (; i < bigops; i++) {
543: ps.setInt(1, nextIntRandom(randomgen, smallrows));
544: ps.execute();
545:
546: if ((i + 1) == 100 && sw.elapsedTime() > 50000) {
547: slow = true;
548: }
549:
550: if (reportProgress && (i + 1) % 10000 == 0
551: || (slow && (i + 1) % 100 == 0)) {
552: System.out.println("Select " + (i + 1) + " : "
553: + sw.elapsedTime() + " rps: "
554: + (i * 1000 / (sw.elapsedTime() + 1)));
555: }
556: }
557: } catch (SQLException e) {
558: e.printStackTrace();
559: }
560:
561: long time = sw.elapsedTime();
562: long rate = ((long) i * 1000) / (time + 1);
563:
564: storeResult("select random zip", i, time, rate);
565: System.out.println("select time for random zip " + i
566: + " rows -- " + time + " ms -- " + rate + " tps");
567: }
568:
569: void selectID() {
570:
571: StopWatch sw = new StopWatch();
572: java.util.Random randomgen = new java.util.Random();
573: int i = 0;
574: boolean slow = false;
575:
576: try {
577: PreparedStatement ps = cConnection
578: .prepareStatement("SELECT firstname,lastname,zip,filler FROM test WHERE id = ?");
579:
580: for (i = 0; i < bigops; i++) {
581: ps.setInt(1, nextIntRandom(randomgen, bigrows - 1));
582: ps.execute();
583:
584: if (reportProgress && (i + 1) % 10000 == 0
585: || (slow && (i + 1) % 100 == 0)) {
586: System.out.println("Select " + (i + 1) + " : "
587: + (sw.elapsedTime() + 1));
588: }
589: }
590:
591: ps.close();
592: } catch (SQLException e) {
593: e.printStackTrace();
594: }
595:
596: long time = sw.elapsedTime();
597: long rate = ((long) i * 1000) / (time + 1);
598:
599: storeResult("select random id", i, time, rate);
600: System.out.println("select time for random id " + i
601: + " rows -- " + time + " ms -- " + rate + " tps");
602: }
603:
604: void selectZipTable() {
605:
606: StopWatch sw = new StopWatch();
607: java.util.Random randomgen = new java.util.Random();
608: int i = 0;
609: boolean slow = false;
610:
611: try {
612: PreparedStatement ps = cConnection
613: .prepareStatement("SELECT zip FROM zip WHERE zip = ?");
614:
615: for (i = 0; i < bigops; i++) {
616: ps.setInt(1, nextIntRandom(randomgen, smallrows - 1));
617: ps.execute();
618:
619: if (reportProgress && (i + 1) % 10000 == 0
620: || (slow && (i + 1) % 100 == 0)) {
621: System.out.println("Select " + (i + 1) + " : "
622: + (sw.elapsedTime() + 1));
623: }
624: }
625:
626: ps.close();
627: } catch (SQLException e) {
628: e.printStackTrace();
629: }
630:
631: long time = sw.elapsedTime();
632: long rate = ((long) i * 1000) / (time + 1);
633:
634: storeResult("select random zip (zip table)", i, time, rate);
635: System.out.println("select time for random zip from zip table "
636: + i + " rows -- " + time + " ms -- " + rate + " tps");
637: }
638:
639: private void countTestID() {
640:
641: try {
642: StopWatch sw = new StopWatch();
643:
644: // the tests use different indexes
645: // use primary index
646: sStatement
647: .execute("SELECT count(*) from TEST where id > -1");
648:
649: ResultSet rs = sStatement.getResultSet();
650:
651: rs.next();
652:
653: long time = sw.elapsedTime();
654: long rate = ((long) bigrows * 1000) / (time + 1);
655:
656: storeResult("count (index on id)", rs.getInt(1), time, rate);
657: System.out.println("count time (index on id) "
658: + rs.getInt(1) + " rows -- " + time + " ms -- "
659: + rate + " tps");
660: } catch (SQLException e) {
661: }
662: }
663:
664: private void countTestZip() {
665:
666: try {
667: StopWatch sw = new StopWatch();
668:
669: sStatement
670: .execute("SELECT count(*) from TEST where zip > -1");
671:
672: ResultSet rs = sStatement.getResultSet();
673:
674: rs.next();
675:
676: long time = (long) sw.elapsedTime();
677: long rate = ((long) bigrows * 1000) / (time + 1);
678:
679: storeResult("count (index on zip)", rs.getInt(1), time,
680: rate);
681: System.out.println("count time (index on zip) "
682: + rs.getInt(1) + " rows -- " + time + " ms -- "
683: + rate + " tps");
684: } catch (SQLException e) {
685: }
686: }
687:
688: private void countZip() {
689:
690: try {
691: StopWatch sw = new StopWatch();
692:
693: sStatement
694: .execute("SELECT count(*) from zip where zip > -1");
695:
696: ResultSet rs = sStatement.getResultSet();
697:
698: rs.next();
699: System.out.println("count time (zip table) " + rs.getInt(1)
700: + " rows -- " + sw.elapsedTime() + " ms");
701: } catch (SQLException e) {
702: }
703: }
704:
705: private void updateZip() {
706:
707: StopWatch sw = new StopWatch();
708: java.util.Random randomgen = new java.util.Random();
709: int i = 0;
710: boolean slow = false;
711: int count = 0;
712: int random = 0;
713:
714: try {
715: PreparedStatement ps = cConnection
716: .prepareStatement("UPDATE test SET filler = filler || zip WHERE zip = ?");
717:
718: for (; i < smallrows; i++) {
719: random = nextIntRandom(randomgen, smallrows - 1);
720:
721: ps.setInt(1, random);
722:
723: count += ps.executeUpdate();
724:
725: if (reportProgress && count % 10000 < 20) {
726: System.out.println("Update " + count + " : "
727: + (sw.elapsedTime() + 1));
728: }
729: }
730:
731: ps.close();
732: } catch (SQLException e) {
733: System.out.println("error : " + random);
734: e.printStackTrace();
735: }
736:
737: long time = sw.elapsedTime();
738: long rate = (i * 1000) / (time + 1);
739:
740: storeResult("update with random zip", i, time, rate);
741: System.out.println("update time with random zip " + i
742: + " rows -- " + time + " ms -- " + rate + " tps");
743: }
744:
745: void updateID() {
746:
747: StopWatch sw = new StopWatch();
748: java.util.Random randomgen = new java.util.Random();
749: int i = 0;
750: boolean slow = false;
751: int count = 0;
752: int random = 0;
753:
754: try {
755: PreparedStatement ps = cConnection
756: .prepareStatement("UPDATE test SET zip = zip + 1 WHERE id = ? and zip <> "
757: + smallrows);
758:
759: for (i = 0; i < bigops; i++) {
760: random = nextIntRandom(randomgen, bigrows - 1);
761:
762: ps.setInt(1, random);
763: ps.execute();
764:
765: if (reportProgress && (i + 1) % 10000 == 0
766: || (slow && (i + 1) % 100 == 0)) {
767: System.out.println("Update " + (i + 1) + " : "
768: + sw.elapsedTime() + " rps: "
769: + (i * 1000 / (sw.elapsedTime() + 1)));
770: }
771: }
772:
773: ps.close();
774: } catch (SQLException e) {
775: System.out.println("error : " + random);
776: e.printStackTrace();
777: }
778:
779: long time = sw.elapsedTime();
780: long rate = (i * 1000) / (time + 1);
781:
782: storeResult("update with random id", i, time, rate);
783: System.out.println("update time with random id " + i
784: + " rows -- " + time + " ms -- " + rate + " tps");
785: }
786:
787: void updateIDLinear() {
788:
789: StopWatch sw = new StopWatch();
790: java.util.Random randomgen = new java.util.Random();
791: int i = 0;
792: boolean slow = false;
793: int count = 0;
794: int random = 0;
795:
796: try {
797: PreparedStatement ps = cConnection
798: .prepareStatement("UPDATE test SET zip = zip + 1 WHERE id = ? and zip <> "
799: + smallrows);
800:
801: for (i = 0; i < bigops; i++) {
802: random = i;
803:
804: ps.setInt(1, random);
805: ps.execute();
806:
807: if (reportProgress && (i + 1) % 10000 == 0
808: || (slow && (i + 1) % 100 == 0)) {
809: System.out.println("Update " + (i + 1) + " : "
810: + sw.elapsedTime() + " rps: "
811: + (i * 1000 / (sw.elapsedTime() + 1)));
812: }
813: }
814:
815: ps.close();
816: } catch (SQLException e) {
817: System.out.println("error : " + random);
818: e.printStackTrace();
819: }
820:
821: long time = sw.elapsedTime();
822: long rate = (i * 1000) / (time + 1);
823:
824: storeResult("update with sequential id", i, time, rate);
825: System.out.println("update time with sequential id " + i
826: + " rows -- " + time + " ms -- " + rate + " tps");
827: }
828:
829: void deleteTest() {
830:
831: StopWatch sw = new StopWatch();
832: java.util.Random randomgen = new java.util.Random();
833: int i = 0;
834: boolean slow = false;
835: int count = 0;
836: int random = 0;
837:
838: try {
839: PreparedStatement ps = cConnection
840: .prepareStatement("DELETE FROM test WHERE id = ?");
841:
842: for (i = 0; count < smallops; i++) {
843: random = nextIntRandom(randomgen, bigrows);
844:
845: // random = i;
846: ps.setInt(1, random);
847:
848: count += ps.executeUpdate();
849:
850: /*
851: if ((i + 1) % 10000 == 0) {
852: Statement st = cConnection.createStatement();
853:
854: st.execute("CHECKPOINT DEFRAG");
855: st.close();
856: }
857: */
858: if (reportProgress && (i + 1) % 10000 == 0
859: || (slow && (i + 1) % 100 == 0)) {
860: System.out.println("delete " + (i + 1) + " : "
861: + sw.elapsedTime() + " rps: "
862: + (i * 1000 / (sw.elapsedTime() + 1)));
863: }
864: }
865:
866: ps.close();
867: } catch (SQLException e) {
868: System.out.println("error : " + random);
869: e.printStackTrace();
870: }
871:
872: long time = sw.elapsedTime();
873: long rate = (count * 1000) / (time + 1);
874:
875: storeResult("delete with random id", count, time, rate);
876: System.out.println("delete time for random id " + count
877: + " rows -- " + time + " ms -- " + rate + " tps");
878: }
879:
880: void deleteZipTable() {
881:
882: StopWatch sw = new StopWatch();
883: java.util.Random randomgen = new java.util.Random();
884: int i = 0;
885: boolean slow = false;
886: int count = 0;
887: int random = 0;
888:
889: try {
890: PreparedStatement ps = cConnection
891: .prepareStatement("DELETE FROM zip WHERE zip = ?");
892:
893: for (i = 0; i <= smallrows; i++) {
894:
895: // random = randomgen.nextInt(smallrows - 1);
896: random = i;
897:
898: ps.setInt(1, random);
899:
900: count += ps.executeUpdate();
901:
902: if (reportProgress && (i + 1) % 10000 == 0
903: || (slow && (i + 1) % 100 == 0)) {
904: System.out.println("delete " + (i + 1) + " : "
905: + sw.elapsedTime() + " rps: "
906: + (i * 1000 / (sw.elapsedTime() + 1)));
907: }
908: }
909:
910: ps.close();
911: } catch (SQLException e) {
912: System.out.println("error : " + random);
913: e.printStackTrace();
914: }
915:
916: long time = sw.elapsedTime();
917: long rate = ((long) count * 1000) / (time + 1);
918:
919: storeResult("delete with random zip", count, time, rate);
920: System.out.println("delete time for random zip " + count
921: + " rows -- " + time + " ms -- " + rate + " tps");
922: }
923:
924: void storeResult(String description, int count, long time, long rate) {
925:
926: try {
927: writer.write("<tr><td>" + description + "</td><td>" + count
928: + "</td><td>" + time + "</td><td>" + rate
929: + "</td></tr>\n");
930: } catch (Exception e) {
931: }
932: }
933:
934: static void deleteDatabase(String path) {
935:
936: FileUtil.delete(path + ".backup");
937: FileUtil.delete(path + ".properties");
938: FileUtil.delete(path + ".script");
939: FileUtil.delete(path + ".data");
940: FileUtil.delete(path + ".log");
941: FileUtil.delete(path + ".lck");
942: FileUtil.delete(path + ".csv");
943: }
944:
945: int nextIntRandom(Random r, int range) {
946:
947: int b = Math.abs(r.nextInt());
948:
949: return b % range;
950: }
951:
952: public static void main(String[] argv) {
953:
954: TestCacheSize test = new TestCacheSize();
955: HsqlProperties props = HsqlProperties.argArrayToProps(argv,
956: "test");
957:
958: test.bigops = props.getIntegerProperty("test.bigops",
959: test.bigops);
960: test.bigrows = test.bigops;
961: test.smallops = test.bigops / 8;
962: test.cacheScale = props.getIntegerProperty("test.scale",
963: test.cacheScale);
964: test.logType = props.getProperty("test.logtype", test.logType);
965: test.tableType = props.getProperty("test.tabletype",
966: test.tableType);
967: test.nioMode = props.isPropertyTrue("test.nio", test.nioMode);
968:
969: if (props.getProperty("test.dbtype", "").equals("mem")) {
970: test.filepath = "mem:test";
971: test.filedb = false;
972: test.shutdown = false;
973: }
974:
975: test.setUp();
976:
977: StopWatch sw = new StopWatch();
978:
979: test.testFillUp();
980: test.checkResults();
981:
982: long time = sw.elapsedTime();
983:
984: test.storeResult("total test time", 0, (int) time, 0);
985: System.out.println("total test time -- " + sw.elapsedTime()
986: + " ms");
987: test.tearDown();
988: }
989: }
|