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