http://hsqldb.org: HSQLDB - Lightweight 100% Java SQL Database Engine (The HSQL Development Group)
  • Blaine Simpson 
/* Copyright (c) 2001-2019, 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 org.hsqldb.test;

import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

import org.hsqldb.lib.FileUtil;
import org.hsqldb.lib.StopWatch;
import org.hsqldb.persist.HsqlProperties;


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:Fred Toussi (fredt@users dot sourceforge.net)
Version:1.8.0
Since:1.7.0
/**
 * 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 Fred Toussi (fredt@users dot sourceforge.net)
 * @version 1.8.0
 * @since 1.7.0
 */
public class TestCacheSize {

    // program can edit the *.properties file to set cache_size, old files are deleted
    protected boolean filedb = true;

    // shutdown performed mid operation - not for mem: or hsql: URL's
    protected boolean shutdown = true;

    // fixed
    protected String url = "jdbc:hsqldb:";

//    protected String  filepath = "hsql://localhost/mytest";
//    protected String filepath = "mem:test";
    protected String filepath = "/hsql/testcache/test";

    // frequent reporting of progress
    boolean reportProgress = true;

    // type of the big table {MEMORY | CACHED | TEXT | ""}
    String  tableType      = "CACHED";
    int     cacheScale     = 14;
    int     cacheSizeScale = 10;
    boolean nioMode        = true;
    int     writeDelay     = 60;
    boolean indexZip       = false;
    boolean indexLastName  = false;
    boolean addForeignKey  = false;
    boolean refIntegrity   = true;

    // may speed 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 = 4*256000;

    // number of ops
    int bigops    = 4*256000;
    int smallops  = 32000;
    int smallrows = 0xfff;

    // if the extra table needs to be created and filled up
    boolean multikeytable = false;

    //
    String     user;
    String     password;
    Statement  sStatement;
    Connection cConnection;
    FileWriter writer;

    //
    String filler = "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
                    + "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ";

    private void checkSelects() {

        countTestID();
        selectID();

//        selectZipTable();
    }

    private void checkUpdates() {

//        updateIDLinear();
//        updateID();
        updateTestString();
        countTestID();
        deleteTest();
        countTestID();
        countZip();
    }

    protected void setUp() {

        try {
            writer = new FileWriter("speedtests.html", true);

            writer.write("<table>\n");
            storeResult(new java.util.Date().toString(), 0, 0, 0);
            storeResult(filepath + " " + tableType + " " + nioMode,
                        cacheScale, 0, 0);
        } catch (Exception e) {}

        user     = "sa";
        password = "";

        try {
            sStatement  = null;
            cConnection = null;

            Class.forName("org.hsqldb.jdbc.JDBCDriver");

            if (filedb) {
                deleteDatabase(filepath);

                cConnection = DriverManager.getConnection(url + filepath,
                        user, password);
                sStatement = cConnection.createStatement();

//                sStatement.execute("SET FILES WRITE DELAY " + 2);
                sStatement.execute("SET FILES DEFRAG " + 0);
                sStatement.execute("SET FILES LOG SIZE " + 0);

//                sStatement.execute("SET FILES LOG FALSE");
                sStatement.execute("SET DATABASE EVENT LOG LEVEL 1");

                int cacheRows = (1 << cacheScale) * 3;
                int cacheSize = (1 << cacheSizeScale) * cacheRows / 1024;

                sStatement.execute("SET FILES CACHE ROWS " + cacheRows);
                sStatement.execute("SET FILES CACHE SIZE " + cacheSize);
                sStatement.execute("SET FILES NIO " + nioMode);
                sStatement.execute("SET FILES BACKUP INCREMENT " + true);
                sStatement.execute("SHUTDOWN");
                cConnection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("TestSql.setUp() error: " + e.getMessage());
        }
    }

    
Fill up the cache

/**
     * Fill up the cache
     *
     *
     */
    public void testFillUp() {

        StopWatch sw    = new StopWatch();
        String    ddl1  = "DROP TABLE test IF EXISTS";
        String    ddl11 = "DROP TABLE zip IF EXISTS";
        String    ddl2  = "CREATE TABLE zip( zip INT IDENTITY )";
        String ddl3 = "CREATE " + tableType + " TABLE test( id INT IDENTITY,"
                      + " firstname VARCHAR(20), " + " lastname VARCHAR(20), "
                      + " zip INTEGER, " + " filler VARCHAR(300))";
        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) ON DELETE CASCADE;";
        String ddl7 = "CREATE TEMP TABLE temptest( id INT,"
                      + " firstname VARCHAR, " + " lastname VARCHAR, "
                      + " zip INTEGER, " + " filler VARCHAR)";
        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 = DriverManager.getConnection(url + filepath, user,
                    password);

            System.out.println("connection time -- " + 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("created index on lastname");
            }

            if (indexZip) {
                sStatement.execute(ddl5);
                System.out.println("created index on zip");
            }

            if (addForeignKey) {
                sStatement.execute(ddl6);
                System.out.println("added foreign key");
            }

            if (createTempTable) {
                sStatement.execute(ddl7);
                System.out.println("created temp table");
            }

            if (multikeytable) {
                sStatement.execute(mddl1);
                sStatement.execute(mddl2);

                if (tableType.equals("TEXT")) {
                    sStatement.execute(mdd13);
                }

                System.out.println("created multi key table");
            }

//            sStatement.execute("CREATE INDEX idx3 ON tempTEST (zip);");
            System.out.println("complete setup time -- " + sw.elapsedTime()
                               + " ms");
            fillUpBigTable(filler, randomgen);

            if (multikeytable) {
                fillUpMultiTable(filler, randomgen);
            }

            sw.zero();

            if (shutdown) {
                sStatement.execute("SHUTDOWN");

                long time = sw.elapsedTime();

                storeResult("shutdown", 0, time, 0);
                System.out.println("shutdown time  -- " + time + " ms");
            }

            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;
        PreparedStatement ps =
            cConnection.prepareStatement("INSERT INTO zip VALUES(?)");

        for (i = 0; i <= smallrows; i++) {
            ps.setInt(1, i);
            ps.execute();
        }

        ps.close();
        sStatement.execute("SET DATABASE REFERENTIAL INTEGRITY "
                           + this.refIntegrity);

        ps = cConnection.prepareStatement(
            "INSERT INTO test (firstname,lastname,zip,filler) VALUES (?,?,?,?)");

        ps.setString(1, "Julia");
        ps.setString(2, "Clancy");

        for (i = 0; i < bigrows; i++) {
            ps.setInt(3, nextIntRandom(randomgen, smallrows));

            {

                // small rows
                long nextrandom   = randomgen.nextLong();
                int  randomlength = (int) nextrandom & 0x7f;

                if (randomlength > filler.length()) {
                    randomlength = filler.length();
                }

                String varfiller = filler.substring(0, randomlength);

                ps.setString(4, nextrandom + varfiller);
            }

/*
            {
                // big rows
                long nextrandom   = randomgen.nextLong();
                int  randomlength = (int) nextrandom & 0x7ff;

                if (randomlength > filler.length() * 20) {
                    randomlength = filler.length() * 20;
                }

                StringBuffer sb = new StringBuffer(0xff);

                for (int j = 0; j < 20; j++) {
                    sb.append(filler);
                }

                String varfiller = sb.substring(0, randomlength);

                ps.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();");

                ResultSet rs = sStatement.getResultSet();

                rs.next();

                int lastId = rs.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");
            }
        }

        ps.close();

//            sStatement.execute("INSERT INTO test SELECT * FROM temptest;");
//            sStatement.execute("DROP TABLE temptest;");
//            sStatement.execute(ddl7);
        long time = sw.elapsedTime();
        long rate = ((long) i * 1000) / (time + 1);

        storeResult("insert", i, time, rate);
        System.out.println("insert time for " + i + " rows -- " + time
                           + " ms -- " + rate + " tps");
    }

    private void fillUpMultiTable(String filler,
                                  Random randomgen) throws SQLException {

        StopWatch sw = new StopWatch();
        int       i;
        PreparedStatement ps = cConnection.prepareStatement(
            "INSERT INTO test2 (id1, id2, firstname,lastname,zip,filler) VALUES (?,?,?,?,?,?)");

        ps.setString(3, "Julia");
        ps.setString(4, "Clancy");

        int id1 = 0;

        for (i = 0; i < bigrows; i++) {
            int id2 = nextIntRandom(randomgen, Integer.MAX_VALUE);

            if (i % 1000 == 0) {
                id1 = nextIntRandom(randomgen, Integer.MAX_VALUE);
            }

            ps.setInt(1, id1);
            ps.setInt(2, id2);
            ps.setInt(5, nextIntRandom(randomgen, smallrows));

            long nextrandom   = randomgen.nextLong();
            int  randomlength = (int) nextrandom & 0x7f;

            if (randomlength > filler.length()) {
                randomlength = filler.length();
            }

            String varfiller = filler.substring(0, randomlength);

            ps.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());
            }
        }

        ps.close();
        System.out.println("total multi key rows inserted: " + i);
        System.out.println("insert time: " + sw.elapsedTime() + " rps: "
                           + (i * 1000 / (sw.elapsedTime() + 1)));
    }

    protected void tearDown() {

        try {
            writer.write("\n</table>\n");
            writer.close();
        } catch (Exception e) {}
    }

    protected void checkResults() {

        try {
            StopWatch sw = new StopWatch();
            ResultSet rs;

            cConnection = DriverManager.getConnection(url + filepath, user,
                    password);

            long time = sw.elapsedTime();

            storeResult("reopen", 0, time, 0);
            System.out.println("database reopen time -- " + time + " ms");
            sw.zero();

            sStatement = cConnection.createStatement();

//            sStatement.execute("SET WRITE_DELAY " + writeDelay);
            checkSelects();
            checkUpdates();
            sw.zero();

            if (shutdown) {
                sStatement.execute("SHUTDOWN");

                time = sw.elapsedTime();

                storeResult("shutdown", 0, time, 0);
                System.out.println("shutdown time  -- " + time + " ms");
            }

            cConnection.close();

//            System.out.println("database close time  -- " + sw.elapsedTime() + " ms");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    void selectZip() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "SELECT TOP 1 firstname,lastname,zip,filler FROM test WHERE zip = ?");

            for (; i < bigops; i++) {
                ps.setInt(1, nextIntRandom(randomgen, smallrows));
                ps.execute();

                if ((i + 1) == 100 && sw.elapsedTime() > 50000) {
                    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();
        }

        long time = sw.elapsedTime();
        long rate = ((long) i * 1000) / (time + 1);

        storeResult("select random zip", i, time, rate);
        System.out.println("select time for random zip " + i + " rows  -- "
                           + time + " ms -- " + rate + " tps");
    }

    void selectID() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "SELECT firstname,lastname,zip,filler FROM test WHERE id = ?");

            for (i = 0; i < smallops; i++) {
                ps.setInt(1, nextIntRandom(randomgen, bigrows - 1));
                ps.execute();

                if (reportProgress && (i + 1) % 10000 == 0
                        || (slow && (i + 1) % 100 == 0)) {
                    System.out.println("Select " + (i + 1) + " : "
                                       + (sw.elapsedTime() + 1));
                }
            }

            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = ((long) i * 1000) / (time + 1);

        storeResult("select random id", i, time, rate);
        System.out.println("select time for random id " + i + " rows  -- "
                           + time + " ms -- " + rate + " tps");
    }

    void selectZipTable() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "SELECT zip FROM zip WHERE zip = ?");

            for (i = 0; i < bigops; i++) {
                ps.setInt(1, nextIntRandom(randomgen, smallrows - 1));
                ps.execute();

                if (reportProgress && (i + 1) % 10000 == 0
                        || (slow && (i + 1) % 100 == 0)) {
                    System.out.println("Select " + (i + 1) + " : "
                                       + (sw.elapsedTime() + 1));
                }
            }

            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = ((long) i * 1000) / (time + 1);

        storeResult("select random zip (zip table)", i, time, rate);
        System.out.println("select time for random zip from zip table " + i
                           + " rows  -- " + time + " ms -- " + rate + " tps");
    }

    private void countTestID() {

        try {
            StopWatch sw = new StopWatch();

            // the tests use different indexes
            // use primary index
            sStatement.execute("SELECT count(*) from TEST where id > -1");

            ResultSet rs = sStatement.getResultSet();

            rs.next();

            long time = sw.elapsedTime();
            long rate = ((long) bigrows * 1000) / (time + 1);

            storeResult("count (index on id)", rs.getInt(1), time, rate);
            System.out.println("count time (index on id) " + rs.getInt(1)
                               + " rows  -- " + time + " ms -- " + rate
                               + " tps");

            sw.zero();

            sStatement.execute("SELECT count(*) from TEST");

            rs = sStatement.getResultSet();

            rs.next();

            time = sw.elapsedTime();
            rate = (1000L) / (time + 1);

            storeResult("count (index on id)", rs.getInt(1), time, rate);
            System.out.println("count time (full count) " + rs.getInt(1)
                               + " rows  -- " + time + " ms -- " + rate
                               + " tps");

        } catch (SQLException e) {}
    }

    private void countTestZip() {

        try {
            StopWatch sw = new StopWatch();

            sStatement.execute("SELECT count(*) from TEST where zip > -1");

            ResultSet rs = sStatement.getResultSet();

            rs.next();

            long time = (long) sw.elapsedTime();
            long rate = ((long) bigrows * 1000) / (time + 1);

            storeResult("count (index on zip)", rs.getInt(1), time, rate);
            System.out.println("count time (index on zip) " + rs.getInt(1)
                               + " rows  -- " + time + " ms -- " + rate
                               + " tps");
        } catch (SQLException e) {}
    }

    private void countZip() {

        try {
            StopWatch sw = new StopWatch();

            sStatement.execute("SELECT count(*) from zip where zip > -1");

            ResultSet rs = sStatement.getResultSet();

            rs.next();
            System.out.println("count time (zip table) " + rs.getInt(1)
                               + " rows  -- " + sw.elapsedTime() + " ms");
        } catch (SQLException e) {}
    }

    private void updateZip() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;
        int              count     = 0;
        int              random    = 0;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "UPDATE test SET filler = filler || zip WHERE zip = ?");

            for (; i < smallrows; i++) {
                random = nextIntRandom(randomgen, smallrows - 1);

                ps.setInt(1, random);

                count += ps.executeUpdate();

                if (reportProgress && count % 10000 < 20) {
                    System.out.println("Update " + count + " : "
                                       + (sw.elapsedTime() + 1));
                }
            }

            ps.close();
        } catch (SQLException e) {
            System.out.println("error : " + random);
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = (i * 1000) / (time + 1);

        storeResult("update with random zip", i, time, rate);
        System.out.println("update time with random zip " + i + " rows  -- "
                           + time + " ms -- " + rate + " tps");
    }

    void updateID() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;
        int              count     = 0;
        int              random    = 0;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "UPDATE test SET zip = zip + 1 WHERE id = ? and zip <> "
                + smallrows);

            for (i = 0; i < smallops; i++) {
                random = nextIntRandom(randomgen, bigrows - 1);

                ps.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)));
                }
            }

            ps.close();
        } catch (SQLException e) {
            System.out.println("error : " + random);
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = (i * 1000) / (time + 1);

        storeResult("update with random id", i, time, rate);
        System.out.println("update time with random id " + i + " rows  -- "
                           + time + " ms -- " + rate + " tps");
    }

    void updateTestString() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;
        int              count     = 0;
        int              random    = 0;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "UPDATE test SET filler = ? WHERE id = ? and zip <> "
                + smallrows);

            for (i = 0; i < smallops * 2; i++) {
                random = nextIntRandom(randomgen, bigrows - 1);

                int randomLength = nextIntRandom(randomgen, filler.length());
                String newFiller = filler.substring(randomLength);

                ps.setString(1, newFiller);
                ps.setInt(2, 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)));
                }
            }

            ps.close();
        } catch (SQLException e) {
            System.out.println("error : " + random);
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = (i * 1000) / (time + 1);

        storeResult("update with random id", i, time, rate);
        System.out.println("update time with random id " + i + " rows  -- "
                           + time + " ms -- " + rate + " tps");
    }

    void updateIDLinear() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;
        int              count     = 0;
        int              random    = 0;

        try {
            PreparedStatement ps = cConnection.prepareStatement(
                "UPDATE test SET zip = zip + 1 WHERE id = ? and zip <> "
                + smallrows);

            for (i = 0; i < bigops; i++) {
                random = i;

                ps.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)));
                }
            }

            ps.close();
        } catch (SQLException e) {
            System.out.println("error : " + random);
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = (i * 1000) / (time + 1);

        storeResult("update with sequential id", i, time, rate);
        System.out.println("update time with sequential id " + i
                           + " rows  -- " + time + " ms -- " + rate + " tps");
    }

    void deleteTest() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;
        int              count     = 0;
        int              random    = 0;

        try {
            PreparedStatement ps =
                cConnection.prepareStatement("DELETE FROM test WHERE id = ?");

            for (i = 0; count < smallops; i++) {
                random = nextIntRandom(randomgen, bigrows);

//                random = i;
                ps.setInt(1, random);

                count += ps.executeUpdate();

/*
                if ((i + 1) % 10000 == 0) {
                    Statement st = cConnection.createStatement();

                    st.execute("CHECKPOINT DEFRAG");
                    st.close();
                }
*/
                if (reportProgress && (i + 1) % 10000 == 0
                        || (slow && (i + 1) % 100 == 0)) {
                    System.out.println("delete " + (i + 1) + " : "
                                       + sw.elapsedTime() + " rps: "
                                       + (i * 1000 / (sw.elapsedTime() + 1)));
                }
            }

            ps.close();
        } catch (SQLException e) {
            System.out.println("error : " + random);
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = (count * 1000) / (time + 1);

        storeResult("delete with random id", count, time, rate);
        System.out.println("delete time for random id " + count + " rows  -- "
                           + time + " ms -- " + rate + " tps");
    }

    void deleteZipTable() {

        StopWatch        sw        = new StopWatch();
        java.util.Random randomgen = new java.util.Random();
        int              i         = 0;
        boolean          slow      = false;
        int              count     = 0;
        int              random    = 0;

        try {
            PreparedStatement ps =
                cConnection.prepareStatement("DELETE FROM zip WHERE zip = ?");

            for (i = 0; i <= smallrows; i++) {

//                random = randomgen.nextInt(smallrows - 1);
                random = i;

                ps.setInt(1, random);

                count += ps.executeUpdate();

                if (reportProgress && (i + 1) % 10000 == 0
                        || (slow && (i + 1) % 100 == 0)) {
                    System.out.println("delete " + (i + 1) + " : "
                                       + sw.elapsedTime() + " rps: "
                                       + (i * 1000 / (sw.elapsedTime() + 1)));
                }
            }

            ps.close();
        } catch (SQLException e) {
            System.out.println("error : " + random);
            e.printStackTrace();
        }

        long time = sw.elapsedTime();
        long rate = ((long) count * 1000) / (time + 1);

        storeResult("delete with random zip", count, time, rate);
        System.out.println("delete time for random zip " + count
                           + " rows  -- " + time + " ms -- " + rate + " tps");
    }

    void storeResult(String description, int count, long time, long rate) {

        try {
            writer.write("<tr><td>" + description + "</td><td>" + count
                         + "</td><td>" + time + "</td><td>" + rate
                         + "</td></tr>\n");
        } catch (Exception e) {}
    }

    static void deleteDatabase(String path) {

        FileUtil fileUtil = FileUtil.getFileUtil();

        fileUtil.delete(path + ".backup");
        fileUtil.delete(path + ".properties");
        fileUtil.delete(path + ".script");
        fileUtil.delete(path + ".data");
        fileUtil.delete(path + ".log");
        fileUtil.delete(path + ".lck");
        fileUtil.delete(path + ".csv");
    }

    int nextIntRandom(Random r, int range) {

        int b = r.nextInt();

        if (b == Integer.MIN_VALUE) {
            b = Integer.MAX_VALUE;
        }

        b = Math.abs(b);

        return b % range;
    }

    public static void main(String[] argv) {

        TestCacheSize  test  = new TestCacheSize();
        HsqlProperties props = HsqlProperties.argArrayToProps(argv, "test");

        test.bigops   = props.getIntegerProperty("test.bigops", test.bigops);
        test.bigrows  = test.bigops;
        test.smallops = test.bigops / 8;
        test.cacheScale = props.getIntegerProperty("test.scale",
                test.cacheScale);
        test.tableType = props.getProperty("test.tabletype", test.tableType);
        test.nioMode   = props.isPropertyTrue("test.nio", test.nioMode);

        if (props.getProperty("test.dbtype", "").equals("mem")) {
            test.filepath = "mem:test";
            test.filedb   = false;
            test.shutdown = false;
        }

        test.setUp();

        StopWatch sw = new StopWatch();

        test.testFillUp();
        test.checkResults();

        long time = sw.elapsedTime();

        test.storeResult("total test time", 0, (int) time, 0);
        System.out.println("total test time -- " + sw.elapsedTime() + " ms");
        test.tearDown();
    }
}