summaryrefslogtreecommitdiff
path: root/connectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java
diff options
context:
space:
mode:
Diffstat (limited to 'connectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java')
-rwxr-xr-xconnectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java609
1 files changed, 609 insertions, 0 deletions
diff --git a/connectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java b/connectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java
new file mode 100755
index 0000000000..54abbeb3b4
--- /dev/null
+++ b/connectivity/qa/complex/connectivity/hsqldb/TestCacheSize.java
@@ -0,0 +1,609 @@
+/* Copyright (c) 2001-2004, The HSQL Development Group
+ * All rights reserved.
+ *
+ * Redistribution and use in source and binary forms, with or without
+ * modification, are permitted provided that the following conditions are met:
+ *
+ * Redistributions of source code must retain the above copyright notice, this
+ * list of conditions and the following disclaimer.
+ *
+ * Redistributions in binary form must reproduce the above copyright notice,
+ * this list of conditions and the following disclaimer in the documentation
+ * and/or other materials provided with the distribution.
+ *
+ * Neither the name of the HSQL Development Group nor the names of its
+ * contributors may be used to endorse or promote products derived from this
+ * software without specific prior written permission.
+ *
+ * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+ * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+ * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+ * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
+ * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
+ * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
+ * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+ * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+ * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+ */
+
+
+package complex.connectivity.hsqldb;
+
+
+
+import org.hsqldb.lib.StopWatch;
+
+import java.util.Random;
+import com.sun.star.lang.*;
+import com.sun.star.uno.UnoRuntime;
+import com.sun.star.sdbc.*;
+
+/**
+ * Test large cached tables by setting up a cached table of 100000 records
+ * or more and a much smaller memory table with about 1/100th rows used.
+ * Populate both tables so that an indexed column of the cached table has a
+ * foreign key reference to the main table.
+ *
+ * This database can be used to demonstrate efficient queries to retrieve
+ * the data from the cached table.
+ *
+ * 1.7.1 insert timings for 100000 rows, cache scale 12:
+ * simple table, no extra index: 52 s
+ * with index on lastname only: 56 s
+ * with index on zip only: 211 s
+ * foreign key, referential_integrity true: 216 s
+ *
+ * The above have improved a lot in 1.7.2
+ *
+ * This test now incorporates the defunct TestTextTables
+ *
+ * @author fredt@users
+ * @version 1.7.2
+ * @since 1.7.0
+ */
+public class TestCacheSize {
+
+ // program can edit the *.properties file to set cache_size
+ protected boolean filedb = true;
+
+ // shutdown performed mid operation - not for mem: or hsql: URL's
+ protected boolean shutdown = true;
+
+ // fixed
+ protected String url = "sdbc:embedded:hsqldb";
+
+ // frequent reporting of progress
+ boolean reportProgress = false;
+
+ XMultiServiceFactory servicefactory = null;
+
+ // type of the big table {MEMORY | CACHED | TEXT}
+ String tableType = "CACHED";
+ int cacheScale = 17;
+ int cacheSizeScale = 8;
+
+ // script format {TEXT, BINARY, COMPRESSED}
+ String logType = "TEXT";
+ int writeDelay = 60;
+ boolean indexZip = true;
+ boolean indexLastName = false;
+ boolean addForeignKey = false;
+ boolean refIntegrity = true;
+
+ // speeds up inserts when tableType=="CACHED"
+ boolean createTempTable = false;
+
+ // introduces fragmentation to the .data file during insert
+ boolean deleteWhileInsert = false;
+ int deleteWhileInsertInterval = 10000;
+
+ // size of the tables used in test
+ int bigrows = 10000;
+ int smallrows = 0xfff;
+
+ // if the extra table needs to be created and filled up
+ boolean multikeytable = false;
+
+ //
+ String user;
+ String password;
+ XStatement sStatement;
+ XConnection cConnection;
+ XDataSource ds;
+ XDriver drv;
+ com.sun.star.beans.PropertyValue[] info;
+
+ public TestCacheSize(XMultiServiceFactory _xmulti,com.sun.star.beans.PropertyValue[] _info,XDriver _drv){
+ servicefactory = _xmulti;
+ drv = _drv;
+ info = _info;
+ }
+
+ public void setURL(String _url){
+ url = _url;
+ }
+
+ public void setUp() {
+
+ user = "sa";
+ password = "";
+
+ try {
+ sStatement = null;
+ cConnection = null;
+
+ //Class.forName("org.hsqldb.jdbcDriver");
+
+ if (filedb) {
+
+ cConnection = drv.connect(url,info);
+ sStatement = cConnection.createStatement();
+
+ sStatement.execute("SET SCRIPTFORMAT " + logType);
+ sStatement.execute("SET LOGSIZE " + 0);
+ sStatement.execute("SHUTDOWN");
+ cConnection.close();
+// props.setProperty("hsqldb.cache_scale", "" + cacheScale);
+// props.setProperty("hsqldb.cache_size_scale",
+ //"" + cacheSizeScale);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ System.out.println("TestSql.setUp() error: " + e.getMessage());
+ }
+ }
+
+ /**
+ * Fill up the cache
+ *
+ *
+ */
+ public void testFillUp() {
+
+ StopWatch sw = new StopWatch();
+ String ddl1 = "DROP TABLE test IF EXISTS;"
+ + "DROP TABLE zip IF EXISTS;";
+ String ddl2 = "CREATE CACHED TABLE zip( zip INT IDENTITY );";
+ String ddl3 = "CREATE " + tableType + " TABLE test( id INT IDENTITY,"
+ + " firstname VARCHAR, " + " lastname VARCHAR, "
+ + " zip INTEGER, " + " filler VARCHAR); ";
+ String ddl31 = "SET TABLE test SOURCE \"test.csv;cache_scale="
+ + cacheScale + "\";";
+
+ // adding extra index will slow down inserts a bit
+ String ddl4 = "CREATE INDEX idx1 ON TEST (lastname);";
+
+ // adding this index will slow down inserts a lot
+ String ddl5 = "CREATE INDEX idx2 ON TEST (zip);";
+
+ // referential integrity checks will slow down inserts a bit
+ String ddl6 =
+ "ALTER TABLE test add constraint c1 FOREIGN KEY (zip) REFERENCES zip(zip);";
+ String ddl7 = "CREATE TEMP TABLE temptest( id INT,"
+ + " firstname VARCHAR, " + " lastname VARCHAR, "
+ + " zip INTEGER, " + " filler VARCHAR); ";
+ String filler =
+ "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
+ + "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ";
+ String mddl1 = "DROP TABLE test2 IF EXISTS;";
+ String mddl2 = "CREATE " + tableType
+ + " TABLE test2( id1 INT, id2 INT,"
+ + " firstname VARCHAR, " + " lastname VARCHAR, "
+ + " zip INTEGER, " + " filler VARCHAR, "
+ + " PRIMARY KEY (id1,id2) ); ";
+ String mdd13 = "SET TABLE test2 SOURCE \"test2.csv;cache_scale="
+ + cacheScale + "\";";
+
+ try {
+ System.out.println("Connecting");
+ sw.zero();
+
+ cConnection = null;
+ sStatement = null;
+ cConnection = drv.connect(url,info);
+
+ System.out.println("connected: " + sw.elapsedTime());
+ sw.zero();
+
+ sStatement = cConnection.createStatement();
+
+ java.util.Random randomgen = new java.util.Random();
+
+ sStatement.execute("SET WRITE_DELAY " + writeDelay);
+ sStatement.execute(ddl1);
+ sStatement.execute(ddl2);
+ sStatement.execute(ddl3);
+
+ if (tableType.equals("TEXT")) {
+ sStatement.execute(ddl31);
+ }
+
+ System.out.println("test table with no index");
+
+ if (indexLastName) {
+ sStatement.execute(ddl4);
+ System.out.println("create index on lastname");
+ }
+
+ if (indexZip) {
+ sStatement.execute(ddl5);
+ System.out.println("create index on zip");
+ }
+
+ if (addForeignKey) {
+ sStatement.execute(ddl6);
+ System.out.println("add foreign key");
+ }
+
+ if (createTempTable) {
+ sStatement.execute(ddl7);
+ System.out.println("temp table");
+ }
+
+ if (multikeytable) {
+ sStatement.execute(mddl1);
+ sStatement.execute(mddl2);
+
+ if (tableType.equals("TEXT")) {
+ sStatement.execute(mdd13);
+ }
+
+ System.out.println("multi key table");
+ }
+
+// sStatement.execute("CREATE INDEX idx3 ON tempTEST (zip);");
+ System.out.println("Setup time: " + sw.elapsedTime());
+ fillUpBigTable(filler, randomgen);
+
+ if (multikeytable) {
+ fillUpMultiTable(filler, randomgen);
+ }
+
+ sw.zero();
+
+ if (shutdown) {
+ sStatement.execute("SHUTDOWN");
+ System.out.println("Shutdown Time: " + sw.elapsedTime());
+ }
+
+ cConnection.close();
+ } catch (SQLException e) {
+ System.out.println(e.getMessage());
+ }
+ }
+
+ private void fillUpBigTable(String filler,
+ Random randomgen) throws SQLException {
+
+ StopWatch sw = new StopWatch();
+ int i;
+
+ for (i = 0; i <= smallrows; i++) {
+ sStatement.execute("INSERT INTO zip VALUES(null);");
+ }
+
+ sStatement.execute("SET REFERENTIAL_INTEGRITY " + this.refIntegrity
+ + ";");
+
+ XPreparedStatement ps = cConnection.prepareStatement(
+ "INSERT INTO test (firstname,lastname,zip,filler) VALUES (?,?,?,?)");
+
+ XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
+ para.setString(1, "Julia");
+ para.setString(2, "Clancy");
+
+ for (i = 0; i < bigrows; i++) {
+ para.setInt(3, randomgen.nextInt(smallrows));
+
+ long nextrandom = randomgen.nextLong();
+ int randomlength = (int) nextrandom & 0x7f;
+
+ if (randomlength > filler.length()) {
+ randomlength = filler.length();
+ }
+
+ String varfiller = filler.substring(0, randomlength);
+
+ para.setString(4, nextrandom + varfiller);
+ ps.execute();
+
+ if (reportProgress && (i + 1) % 10000 == 0) {
+ System.out.println("Insert " + (i + 1) + " : "
+ + sw.elapsedTime());
+ }
+
+ // delete and add 4000 rows to introduce fragmentation
+ if (deleteWhileInsert && i != 0
+ && i % deleteWhileInsertInterval == 0) {
+ sStatement.execute("CALL IDENTITY();");
+
+ XMultipleResults mrs = (XMultipleResults)UnoRuntime.queryInterface(XMultipleResults.class,sStatement);
+ XResultSet rs = mrs.getResultSet();
+
+ rs.next();
+
+ XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs);
+ int lastId = row.getInt(1);
+
+ sStatement.execute(
+ "SELECT * INTO TEMP tempt FROM test WHERE id > "
+ + (lastId - 4000) + " ;");
+ sStatement.execute("DELETE FROM test WHERE id > "
+ + (lastId - 4000) + " ;");
+ sStatement.execute("INSERT INTO test SELECT * FROM tempt;");
+ sStatement.execute("DROP TABLE tempt;");
+ }
+ }
+
+// sStatement.execute("INSERT INTO test SELECT * FROM temptest;");
+// sStatement.execute("DROP TABLE temptest;");
+// sStatement.execute(ddl7);
+ System.out.println("Total insert: " + i);
+ System.out.println("Insert time: " + sw.elapsedTime() + " rps: "
+ + (i * 1000 / (sw.elapsedTime() + 1)));
+ }
+
+ private void fillUpMultiTable(String filler,
+ Random randomgen) throws SQLException {
+
+ StopWatch sw = new StopWatch();
+ int i;
+ XPreparedStatement ps = cConnection.prepareStatement(
+ "INSERT INTO test2 (id1, id2, firstname,lastname,zip,filler) VALUES (?,?,?,?,?,?)");
+
+ XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
+ para.setString(3, "Julia");
+ para.setString(4, "Clancy");
+
+ int id1 = 0;
+
+ for (i = 0; i < bigrows; i++) {
+ int id2 = randomgen.nextInt(Integer.MAX_VALUE);
+
+ if (i % 1000 == 0) {
+ id1 = randomgen.nextInt(Integer.MAX_VALUE);
+ }
+
+ para.setInt(1, id1);
+ para.setInt(2, id2);
+ para.setInt(5, randomgen.nextInt(smallrows));
+
+ long nextrandom = randomgen.nextLong();
+ int randomlength = (int) nextrandom & 0x7f;
+
+ if (randomlength > filler.length()) {
+ randomlength = filler.length();
+ }
+
+ String varfiller = filler.substring(0, randomlength);
+
+ para.setString(6, nextrandom + varfiller);
+
+ try {
+ ps.execute();
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ if (reportProgress && (i + 1) % 10000 == 0) {
+ System.out.println("Insert " + (i + 1) + " : "
+ + sw.elapsedTime());
+ }
+ }
+
+ System.out.println("Multi Key Total insert: " + i);
+ System.out.println("Insert time: " + sw.elapsedTime() + " rps: "
+ + (i * 1000 / (sw.elapsedTime() + 1)));
+ }
+
+ public void tearDown() {}
+
+ public void checkResults() {
+
+ try {
+ StopWatch sw = new StopWatch();
+ XResultSet rs;
+
+ cConnection = drv.connect(url,info);
+
+ System.out.println("Reopened database: " + sw.elapsedTime());
+ sw.zero();
+
+ sStatement = cConnection.createStatement();
+
+ sStatement.execute("SET WRITE_DELAY " + writeDelay);
+
+ // the tests use different indexes
+ // use primary index
+ sStatement.execute("SELECT count(*) from TEST");
+
+ XMultipleResults mrs = (XMultipleResults)UnoRuntime.queryInterface(XMultipleResults.class,sStatement);
+ rs = mrs.getResultSet();
+ XRow row = (XRow)UnoRuntime.queryInterface(XRow.class,rs);
+
+ rs.next();
+ System.out.println("Row Count: " + row.getInt(1));
+ System.out.println("Time to count: " + sw.elapsedTime());
+
+ // use index on zip
+ sw.zero();
+ sStatement.execute("SELECT count(*) from TEST where zip > -1");
+
+ rs = mrs.getResultSet();
+
+ rs.next();
+ System.out.println("Row Count: " + row.getInt(1));
+ System.out.println("Time to count: " + sw.elapsedTime());
+ checkSelects();
+ checkUpdates();
+ checkSelects();
+ sw.zero();
+ sStatement.execute("SELECT count(*) from TEST where zip > -1");
+
+ rs = mrs.getResultSet();
+
+ rs.next();
+ System.out.println("Row Count: " + row.getInt(1));
+ System.out.println("Time to count: " + sw.elapsedTime());
+ sw.zero();
+
+ if (shutdown) {
+ sStatement.execute("SHUTDOWN");
+ System.out.println("Shutdown Time: " + sw.elapsedTime());
+ }
+
+ cConnection.close();
+ System.out.println("Closed database: " + sw.elapsedTime());
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+
+ private void checkSelects() {
+
+ StopWatch sw = new StopWatch();
+ java.util.Random randomgen = new java.util.Random();
+ int i = 0;
+ boolean slow = false;
+
+ try {
+ XPreparedStatement ps = cConnection.prepareStatement(
+ "SELECT TOP 1 firstname,lastname,zip,filler FROM test WHERE zip = ?");
+ XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
+
+ for (; i < bigrows; i++) {
+ para.setInt(1, randomgen.nextInt(smallrows));
+ ps.execute();
+
+ if ((i + 1) == 100 && sw.elapsedTime() > 5000) {
+ slow = true;
+ }
+
+ if (reportProgress && (i + 1) % 10000 == 0
+ || (slow && (i + 1) % 100 == 0)) {
+ System.out.println("Select " + (i + 1) + " : "
+ + sw.elapsedTime() + " rps: "
+ + (i * 1000 / (sw.elapsedTime() + 1)));
+ }
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ System.out.println("Select random zip " + i + " rows : "
+ + sw.elapsedTime() + " rps: "
+ + (i * 1000 / (sw.elapsedTime() + 1)));
+ sw.zero();
+
+ try {
+ XPreparedStatement ps = cConnection.prepareStatement(
+ "SELECT firstname,lastname,zip,filler FROM test WHERE id = ?");
+ XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
+
+ for (i = 0; i < bigrows; i++) {
+ para.setInt(1, randomgen.nextInt(bigrows - 1));
+ ps.execute();
+
+ if (reportProgress && (i + 1) % 10000 == 0
+ || (slow && (i + 1) % 100 == 0)) {
+ System.out.println("Select " + (i + 1) + " : "
+ + (sw.elapsedTime() + 1));
+ }
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ System.out.println("Select random id " + i + " rows : "
+ + sw.elapsedTime() + " rps: "
+ + (i * 1000 / (sw.elapsedTime() + 1)));
+ sw.zero();
+
+ try {
+ XPreparedStatement ps = cConnection.prepareStatement(
+ "SELECT zip FROM zip WHERE zip = ?");
+ XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
+
+ for (i = 0; i < bigrows; i++) {
+ para.setInt(1, randomgen.nextInt(smallrows - 1));
+ ps.execute();
+
+ if (reportProgress && (i + 1) % 10000 == 0
+ || (slow && (i + 1) % 100 == 0)) {
+ System.out.println("Select " + (i + 1) + " : "
+ + (sw.elapsedTime() + 1));
+ }
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ System.out.println("Select random zip from zip table " + i
+ + " rows : " + sw.elapsedTime() + " rps: "
+ + (i * 1000 / (sw.elapsedTime() + 1)));
+ }
+
+ private void checkUpdates() {
+
+ StopWatch sw = new StopWatch();
+ java.util.Random randomgen = new java.util.Random();
+ int i = 0;
+ boolean slow = false;
+ int count = 0;
+
+ try {
+ XPreparedStatement ps = cConnection.prepareStatement(
+ "UPDATE test SET filler = filler || zip WHERE zip = ?");
+ XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
+
+ for (; i < smallrows; i++) {
+ int random = randomgen.nextInt(smallrows - 1);
+
+ para.setInt(1, random);
+
+ count += ps.executeUpdate();
+
+ if (reportProgress && count % 10000 < 20) {
+ System.out.println("Update " + count + " : "
+ + (sw.elapsedTime() + 1));
+ }
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ System.out.println("Update with random zip " + i
+ + " UPDATE commands, " + count + " rows : "
+ + sw.elapsedTime() + " rps: "
+ + (count * 1000 / (sw.elapsedTime() + 1)));
+ sw.zero();
+
+ try {
+ XPreparedStatement ps = cConnection.prepareStatement(
+ "UPDATE test SET zip = zip + 1 WHERE id = ?");
+ XParameters para = (XParameters)UnoRuntime.queryInterface(XParameters.class,ps);
+
+ for (i = 0; i < bigrows; i++) {
+ int random = randomgen.nextInt(bigrows - 1);
+
+ para.setInt(1, random);
+ ps.execute();
+
+ if (reportProgress && (i + 1) % 10000 == 0
+ || (slow && (i + 1) % 100 == 0)) {
+ System.out.println("Update " + (i + 1) + " : "
+ + sw.elapsedTime() + " rps: "
+ + (i * 1000 / (sw.elapsedTime() + 1)));
+ }
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ System.out.println("Update with random id " + i + " rows : "
+ + sw.elapsedTime() + " rps: "
+ + (i * 1000 / (sw.elapsedTime() + 1)));
+ }
+}
generated by cgit v1.2.3 (git 2.39.1) at 2024-04-27 00:02:24 +0000