Java > Open Source Codes > org > hsqldb > test > TestCacheSize


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags