connectivity/qa/drivers/hsqldb/TestCacheSize.java

   1 /* Copyright (c) 2001-2004, 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 qa.drivers.hsqldb;
  33
  34 import java.io.*;
  35
  36
  37 import org.hsqldb.lib.StopWatch;
  38 import org.hsqldb.lib.FileAccess;
  39
  40 import java.util.Random;
  41 import com.sun.star.lang.*;
  42 import com.sun.star.uno.UnoRuntime;
  43 import com.sun.star.beans.PropertyValue;
  44 import com.sun.star.beans.PropertyState;
  45 import com.sun.star.container.XNameAccess;
  46 import com.sun.star.sdbc.*;
  47 import com.sun.star.document.XDocumentSubStorageSupplier;
  48 import com.sun.star.embed.XStorage;
  49 import com.sun.star.frame.XStorable;
  50
  51 /**
  52  * Test large cached tables by setting up a cached table of 100000 records
  53  * or more and a much smaller memory table with about 1/100th rows used.
  54  * Populate both tables so that an indexed column of the cached table has a
  55  * foreign key reference to the main table.
  56  *
  57  * This database can be used to demonstrate efficient queries to retrieve
  58  * the data from the cached table.
  59  *
  60  * 1.7.1 insert timings for 100000 rows, cache scale 12:
  61  * simple table, no extra index: 52 s
  62  * with index on lastname only: 56 s
  63  * with index on zip only: 211 s
  64  * foreign key, referential_integrity true: 216 s
  65  *
  66  * The above have improved a lot in 1.7.2
  67  *
  68  * This test now incorporates the defunct TestTextTables
  69  *
  70  * @author fredt@users
  71  * @version 1.7.2
  72  * @since 1.7.0
  73  */
  74 public class TestCacheSize {
  75
  76     // program can edit the *.properties file to set cache_size
  77     protected boolean filedb = true;
  78
  79     // shutdown performed mid operation - not for mem: or hsql: URL's
  80     protected boolean shutdown = true;
  81
  82     // fixed
  83     protected String url = "sdbc:embedded:hsqldb";
  84
  85     // frequent reporting of progress
  86     boolean reportProgress = false;
  87
  88     XMultiServiceFactory servicefactory = null;
  89
  90     // type of the big table {MEMORY | CACHED | TEXT}
  91     String tableType      = "CACHED";
  92     int    cacheScale     = 17;
  93     int    cacheSizeScale = 8;
  94
  95     // script format {TEXT, BINARY, COMPRESSED}
  96     String logType       = "TEXT";
  97     int     writeDelay    = 60;
  98     boolean indexZip      = true;
  99     boolean indexLastName = false;
 100     boolean addForeignKey = false;
 101     boolean refIntegrity  = true;
 102
 103     // speeds up inserts when tableType=="CACHED"
 104     boolean createTempTable = false;
 105
 106     // introduces fragmentation to the .data file during insert
 107     boolean deleteWhileInsert         = false;
 108     int     deleteWhileInsertInterval = 10000;
 109
 110     // size of the tables used in test
 111     int bigrows   = 10000;
 112     int smallrows = 0xfff;
 113
 114     // if the extra table needs to be created and filled up
 115     boolean multikeytable = false;
 116
 117     //
 118     String     user;
 119     String     password;
 120     XStatement  sStatement;
 121     XConnection cConnection;
 122     XDataSource ds;
 123     XDriver drv;
 124     com.sun.star.beans.PropertyValue[] info;
 125
 126     TestCacheSize(XMultiServiceFactory _xmulti,com.sun.star.beans.PropertyValue[] _info,XDriver _drv){
 127         servicefactory = _xmulti;
 128         drv = _drv;
 129         info = _info;
 130     }
 131
 132     void setURL(String _url){
 133         url = _url;
 134     }
 135
 136     protected void setUp() {
 137
 138         user     = "sa";
 139         password = "";
 140
 141         try {
 142             sStatement  = null;
 143             cConnection = null;
 144
 145             //Class.forName("org.hsqldb.jdbcDriver");
 146
 147             if (filedb) {
 148
 149                 cConnection = drv.connect(url,info);
 150                 sStatement = cConnection.createStatement();
 151
 152                 sStatement.execute("SET SCRIPTFORMAT " + logType);
 153                 sStatement.execute("SET LOGSIZE " + 0);
 154                 sStatement.execute("SHUTDOWN");
 155                 cConnection.close();
 156 //                props.setProperty("hsqldb.cache_scale", "" + cacheScale);
 157 //                props.setProperty("hsqldb.cache_size_scale",
 158                                   //"" + cacheSizeScale);
 159             }
 160         } catch (Exception e) {
 161             e.printStackTrace();
 162             System.out.println("TestSql.setUp() error: " + e.getMessage());
 163         }
 164     }
 165
 166     /**
 167      * Fill up the cache
 168      *
 169      *
 170      */
 171     public void testFillUp() {
 172
 173         StopWatch sw = new StopWatch();
 174         String ddl1 = "DROP TABLE test IF EXISTS;"
 175                       + "DROP TABLE zip IF EXISTS;";
 176         String ddl2 = "CREATE CACHED TABLE zip( zip INT IDENTITY );";
 177         String ddl3 = "CREATE " + tableType + " TABLE test( id INT IDENTITY,"
 178                       + " firstname VARCHAR, " + " lastname VARCHAR, "
 179                       + " zip INTEGER, " + " filler VARCHAR); ";
 180         String ddl31 = "SET TABLE test SOURCE \"test.csv;cache_scale="
 181                        + cacheScale + "\";";
 182
 183         // adding extra index will slow down inserts a bit
 184         String ddl4 = "CREATE INDEX idx1 ON TEST (lastname);";
 185
 186         // adding this index will slow down  inserts a lot
 187         String ddl5 = "CREATE INDEX idx2 ON TEST (zip);";
 188
 189         // referential integrity checks will slow down inserts a bit
 190         String ddl6 =
 191             "ALTER TABLE test add constraint c1 FOREIGN KEY (zip) REFERENCES zip(zip);";
 192         String ddl7 = "CREATE TEMP TABLE temptest( id INT,"
 193                       + " firstname VARCHAR, " + " lastname VARCHAR, "
 194                       + " zip INTEGER, " + " filler VARCHAR); ";
 195         String filler =
 196             "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
 197             + "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ";
 198         String mddl1 = "DROP TABLE test2 IF EXISTS;";
 199         String mddl2 = "CREATE " + tableType
 200                        + " TABLE test2( id1 INT, id2 INT,"
 201                        + " firstname VARCHAR, " + " lastname VARCHAR, "
 202                        + " zip INTEGER, " + " filler VARCHAR, "
 203                        + " PRIMARY KEY (id1,id2) ); ";
 204         String mdd13 = "SET TABLE test2 SOURCE \"test2.csv;cache_scale="
 205                        + cacheScale + "\";";
 206
 207         try {
 208             System.out.println("Connecting");
 209             sw.zero();
 210
 211             cConnection = null;
 212             sStatement  = null;
 213             cConnection = drv.connect(url,info);
 214
 215             System.out.println("connected: " + sw.elapsedTime());
 216             sw.zero();
 217
 218             sStatement = cConnection.createStatement();
 219
 220             java.util.Random randomgen = new java.util.Random();
 221
 222             sStatement.execute("SET WRITE_DELAY " + writeDelay);
 223             sStatement.execute(ddl1);
 224             sStatement.execute(ddl2);
 225             sStatement.execute(ddl3);
 226
 227             if (tableType.equals("TEXT")) {
 228                 sStatement.execute(ddl31);
 229             }
 230
 231             System.out.println("test table with no index");
 232
 233             if (indexLastName) {
 234                 sStatement.execute(ddl4);
 235                 System.out.println("create index on lastname");
 236             }
 237
 238             if (indexZip) {
 239                 sStatement.execute(ddl5);
 240                 System.out.println("create index on zip");
 241             }
 242
 243             if (addForeignKey) {
 244                 sStatement.execute(ddl6);
 245                 System.out.println("add foreign key");
 246             }
 247
 248             if (createTempTable) {
 249                 sStatement.execute(ddl7);
 250                 System.out.println("temp table");
 251             }
 252
 253             if (multikeytable) {
 254                 sStatement.execute(mddl1);
 255                 sStatement.execute(mddl2);
 256
 257                 if (tableType.equals("TEXT")) {
 258                     sStatement.execute(mdd13);
 259                 }
 260
 261                 System.out.println("multi key table");
 262             }
 263
 264 //            sStatement.execute("CREATE INDEX idx3 ON tempTEST (zip);");
 265             System.out.println("Setup time: " + sw.elapsedTime());
 266             fillUpBigTable(filler, randomgen);
 267
 268             if (multikeytable) {
 269                 fillUpMultiTable(filler, randomgen);
 270             }
 271
 272             sw.zero();
 273
 274             if (shutdown) {
 275                 sStatement.execute("SHUTDOWN");
 276                 System.out.println("Shutdown Time: " + sw.elapsedTime());
 277             }
 278
 279             cConnection.close();
 280         } catch (SQLException e) {
 281             System.out.println(e.getMessage());
 282         }
 283     }
 284
 285     private void fillUpBigTable(String filler,
 286                                 Random randomgen) throws SQLException {
 287
 288         StopWatch sw = new StopWatch();
 289         int       i;
 290
 291         for (i = 0; i <= smallrows; i++) {
 292             sStatement.execute("INSERT INTO zip VALUES(null);");
 293         }
 294
 295         sStatement.execute("SET REFERENTIAL_INTEGRITY " + this.refIntegrity
 296                            + ";");
 297
 298         XPreparedStatement ps = cConnection.prepareStatement(
 299             "INSERT INTO test (firstname,lastname,zip,filler) VALUES (?,?,?,?)");
 300
 301         XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
 302         para.setString(1, "Julia");
 303         para.setString(2, "Clancy");
 304
 305         for (i = 0; i < bigrows; i++) {
 306             para.setInt(3, randomgen.nextInt(smallrows));
 307
 308             long nextrandom   = randomgen.nextLong();
 309             int  randomlength = (int) nextrandom & 0x7f;
 310
 311             if (randomlength > filler.length()) {
 312                 randomlength = filler.length();
 313             }
 314
 315             String varfiller = filler.substring(0, randomlength);
 316
 317             para.setString(4, nextrandom + varfiller);
 318             ps.execute();
 319
 320             if (reportProgress && (i + 1) % 10000 == 0) {
 321                 System.out.println("Insert " + (i + 1) + " : "
 322                                    + sw.elapsedTime());
 323             }
 324
 325             // delete and add 4000 rows to introduce fragmentation
 326             if (deleteWhileInsert && i != 0
 327                     && i % deleteWhileInsertInterval == 0) {
 328                 sStatement.execute("CALL IDENTITY();");
 329
 330                 XMultipleResults mrs = (XMultipleResults)UnoRuntime.queryInterface(XMultipleResults.class,sStatement);
 331                 XResultSet rs = mrs.getResultSet();
 332
 333                 rs.next();
 334
 335                 XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs);
 336                 int lastId = row.getInt(1);
 337
 338                 sStatement.execute(
 339                     "SELECT * INTO TEMP tempt FROM test WHERE id > "
 340                     + (lastId - 4000) + " ;");
 341                 sStatement.execute("DELETE FROM test WHERE id > "
 342                                    + (lastId - 4000) + " ;");
 343                 sStatement.execute("INSERT INTO test SELECT * FROM tempt;");
 344                 sStatement.execute("DROP TABLE tempt;");
 345             }
 346         }
 347
 348 //            sStatement.execute("INSERT INTO test SELECT * FROM temptest;");
 349 //            sStatement.execute("DROP TABLE temptest;");
 350 //            sStatement.execute(ddl7);
 351         System.out.println("Total insert: " + i);
 352         System.out.println("Insert time: " + sw.elapsedTime() + " rps: "
 353                            + (i * 1000 / (sw.elapsedTime() + 1)));
 354     }
 355
 356     private void fillUpMultiTable(String filler,
 357                                   Random randomgen) throws SQLException {
 358
 359         StopWatch sw = new StopWatch();
 360         int       i;
 361         XPreparedStatement ps = cConnection.prepareStatement(
 362             "INSERT INTO test2 (id1, id2, firstname,lastname,zip,filler) VALUES (?,?,?,?,?,?)");
 363
 364         XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
 365         para.setString(3, "Julia");
 366         para.setString(4, "Clancy");
 367
 368         int id1 = 0;
 369
 370         for (i = 0; i < bigrows; i++) {
 371             int id2 = randomgen.nextInt(Integer.MAX_VALUE);
 372
 373             if (i % 1000 == 0) {
 374                 id1 = randomgen.nextInt(Integer.MAX_VALUE);
 375             }
 376
 377             para.setInt(1, id1);
 378             para.setInt(2, id2);
 379             para.setInt(5, randomgen.nextInt(smallrows));
 380
 381             long nextrandom   = randomgen.nextLong();
 382             int  randomlength = (int) nextrandom & 0x7f;
 383
 384             if (randomlength > filler.length()) {
 385                 randomlength = filler.length();
 386             }
 387
 388             String varfiller = filler.substring(0, randomlength);
 389
 390             para.setString(6, nextrandom + varfiller);
 391
 392             try {
 393                 ps.execute();
 394             } catch (SQLException e) {
 395                 e.printStackTrace();
 396             }
 397
 398             if (reportProgress && (i + 1) % 10000 == 0) {
 399                 System.out.println("Insert " + (i + 1) + " : "
 400                                    + sw.elapsedTime());
 401             }
 402         }
 403
 404         System.out.println("Multi Key Total insert: " + i);
 405         System.out.println("Insert time: " + sw.elapsedTime() + " rps: "
 406                            + (i * 1000 / (sw.elapsedTime() + 1)));
 407     }
 408
 409     protected void tearDown() {}
 410
 411     protected void checkResults() {
 412
 413         try {
 414             StopWatch sw = new StopWatch();
 415             XResultSet rs;
 416
 417             cConnection = drv.connect(url,info);
 418
 419             System.out.println("Reopened database: " + sw.elapsedTime());
 420             sw.zero();
 421
 422             sStatement = cConnection.createStatement();
 423
 424             sStatement.execute("SET WRITE_DELAY " + writeDelay);
 425
 426             // the tests use different indexes
 427             // use primary index
 428             sStatement.execute("SELECT count(*) from TEST");
 429
 430             XMultipleResults mrs = (XMultipleResults)UnoRuntime.queryInterface(XMultipleResults.class,sStatement);
 431             rs = mrs.getResultSet();
 432             XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs);
 433
 434             rs.next();
 435             System.out.println("Row Count: " + row.getInt(1));
 436             System.out.println("Time to count: " + sw.elapsedTime());
 437
 438             // use index on zip
 439             sw.zero();
 440             sStatement.execute("SELECT count(*) from TEST where zip > -1");
 441
 442             rs = mrs.getResultSet();
 443
 444             rs.next();
 445             System.out.println("Row Count: " + row.getInt(1));
 446             System.out.println("Time to count: " + sw.elapsedTime());
 447             checkSelects();
 448             checkUpdates();
 449             checkSelects();
 450             sw.zero();
 451             sStatement.execute("SELECT count(*) from TEST where zip > -1");
 452
 453             rs = mrs.getResultSet();
 454
 455             rs.next();
 456             System.out.println("Row Count: " + row.getInt(1));
 457             System.out.println("Time to count: " + sw.elapsedTime());
 458             sw.zero();
 459
 460             if (shutdown) {
 461                 sStatement.execute("SHUTDOWN");
 462                 System.out.println("Shutdown Time: " + sw.elapsedTime());
 463             }
 464
 465             cConnection.close();
 466             System.out.println("Closed database: " + sw.elapsedTime());
 467         } catch (SQLException e) {
 468             e.printStackTrace();
 469         }
 470     }
 471
 472     private void checkSelects() {
 473
 474         StopWatch        sw        = new StopWatch();
 475         java.util.Random randomgen = new java.util.Random();
 476         int              i         = 0;
 477         boolean          slow      = false;
 478
 479         try {
 480             XPreparedStatement ps = cConnection.prepareStatement(
 481                 "SELECT TOP 1 firstname,lastname,zip,filler FROM test WHERE zip = ?");
 482             XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
 483
 484             for (; i < bigrows; i++) {
 485                 para.setInt(1, randomgen.nextInt(smallrows));
 486                 ps.execute();
 487
 488                 if ((i + 1) == 100 && sw.elapsedTime() > 5000) {
 489                     slow = true;
 490                 }
 491
 492                 if (reportProgress && (i + 1) % 10000 == 0
 493                         || (slow && (i + 1) % 100 == 0)) {
 494                     System.out.println("Select " + (i + 1) + " : "
 495                                        + sw.elapsedTime() + " rps: "
 496                                        + (i * 1000 / (sw.elapsedTime() + 1)));
 497                 }
 498             }
 499         } catch (SQLException e) {
 500             e.printStackTrace();
 501         }
 502
 503         System.out.println("Select random zip " + i + " rows : "
 504                            + sw.elapsedTime() + " rps: "
 505                            + (i * 1000 / (sw.elapsedTime() + 1)));
 506         sw.zero();
 507
 508         try {
 509             XPreparedStatement ps = cConnection.prepareStatement(
 510                 "SELECT firstname,lastname,zip,filler FROM test WHERE id = ?");
 511             XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
 512
 513             for (i = 0; i < bigrows; i++) {
 514                 para.setInt(1, randomgen.nextInt(bigrows - 1));
 515                 ps.execute();
 516
 517                 if (reportProgress && (i + 1) % 10000 == 0
 518                         || (slow && (i + 1) % 100 == 0)) {
 519                     System.out.println("Select " + (i + 1) + " : "
 520                                        + (sw.elapsedTime() + 1));
 521                 }
 522             }
 523         } catch (SQLException e) {
 524             e.printStackTrace();
 525         }
 526
 527         System.out.println("Select random id " + i + " rows : "
 528                            + sw.elapsedTime() + " rps: "
 529                            + (i * 1000 / (sw.elapsedTime() + 1)));
 530         sw.zero();
 531
 532         try {
 533             XPreparedStatement ps = cConnection.prepareStatement(
 534                 "SELECT zip FROM zip WHERE zip = ?");
 535             XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
 536
 537             for (i = 0; i < bigrows; i++) {
 538                 para.setInt(1, randomgen.nextInt(smallrows - 1));
 539                 ps.execute();
 540
 541                 if (reportProgress && (i + 1) % 10000 == 0
 542                         || (slow && (i + 1) % 100 == 0)) {
 543                     System.out.println("Select " + (i + 1) + " : "
 544                                        + (sw.elapsedTime() + 1));
 545                 }
 546             }
 547         } catch (SQLException e) {
 548             e.printStackTrace();
 549         }
 550
 551         System.out.println("Select random zip from zip table " + i
 552                            + " rows : " + sw.elapsedTime() + " rps: "
 553                            + (i * 1000 / (sw.elapsedTime() + 1)));
 554     }
 555
 556     private void checkUpdates() {
 557
 558         StopWatch        sw        = new StopWatch();
 559         java.util.Random randomgen = new java.util.Random();
 560         int              i         = 0;
 561         boolean          slow      = false;
 562         int              count     = 0;
 563
 564         try {
 565             XPreparedStatement ps = cConnection.prepareStatement(
 566                 "UPDATE test SET filler = filler || zip WHERE zip = ?");
 567             XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
 568
 569             for (; i < smallrows; i++) {
 570                 int random = randomgen.nextInt(smallrows - 1);
 571
 572                 para.setInt(1, random);
 573
 574                 count += ps.executeUpdate();
 575
 576                 if (reportProgress && count % 10000 < 20) {
 577                     System.out.println("Update " + count + " : "
 578                                        + (sw.elapsedTime() + 1));
 579                 }
 580             }
 581         } catch (SQLException e) {
 582             e.printStackTrace();
 583         }
 584
 585         System.out.println("Update with random zip " + i
 586                            + " UPDATE commands, " + count + " rows : "
 587                            + sw.elapsedTime() + " rps: "
 588                            + (count * 1000 / (sw.elapsedTime() + 1)));
 589         sw.zero();
 590
 591         try {
 592             XPreparedStatement ps = cConnection.prepareStatement(
 593                 "UPDATE test SET zip = zip + 1 WHERE id = ?");
 594             XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
 595
 596             for (i = 0; i < bigrows; i++) {
 597                 int random = randomgen.nextInt(bigrows - 1);
 598
 599                 para.setInt(1, random);
 600                 ps.execute();
 601
 602                 if (reportProgress && (i + 1) % 10000 == 0
 603                         || (slow && (i + 1) % 100 == 0)) {
 604                     System.out.println("Update " + (i + 1) + " : "
 605                                        + sw.elapsedTime() + " rps: "
 606                                        + (i * 1000 / (sw.elapsedTime() + 1)));
 607                 }
 608             }
 609         } catch (SQLException e) {
 610             e.printStackTrace();
 611         }
 612
 613         System.out.println("Update with random id " + i + " rows : "
 614                            + sw.elapsedTime() + " rps: "
 615                            + (i * 1000 / (sw.elapsedTime() + 1)));
 616     }
 617 }