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